Hopefully this is going to be easy for someone.
I have recorded a macro (several attempts) which goes through the steps of copying some text from a cell (which will become a filename), then going through the steps of exporting a PDF file in a folder.
Going through the steps Manually works perfectly. Going through the steps works fine for the unique file name (i.e., NOT replacing a file with same name or any basic error). This is the only macro I've encountered that does not complete the recorded task. No error messages or popup windows appear asking for anything.
The question is, at the end of a macro whose action is to save a unique spreadsheet file, is there an added step necessary (in the macro) after recording clicking of the SAVE button? Has anyone else here created a macro to save a file and it it did not work? That is, is there a special checkbox to indicate you really do want to complete the task ? I am missing something really essential but can't find it after many tries.
Thanks for any help or comments.
[Solved] Macro to "Export as PDF" not completing that task
[Solved] Macro to "Export as PDF" not completing that task
Last edited by PhilLens on Wed Aug 09, 2017 8:51 am, edited 1 time in total.
OpenOffice 4.1.3 on OSX 10.11.6, iMac Desktop
Re: Macro to "Export as PDF" not completing that task.
Without seeing the macro you recorded, it is very hard to guess what the problem is. Please upload your code.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: Macro to "Export as PDF" not completing that task.
Okay, I have attempted to save it as a text file(attached). The cell I copy to become the name of the exported PDF file is E5, as in the attached screen crop image. That is my invoice number which helps me organize the PDF's I would send to the customers (It is a cryptic date and sequence plus address plus the customers initials...pretty long!).
I really do appreciate your time and your help, my friend. And thank you for all you have already offered in past two days.
I really do appreciate your time and your help, my friend. And thank you for all you have already offered in past two days.
- Attachments
-
- Screen Shot 2017-08-08 at 11.45.44 PM.jpg (20.52 KiB) Viewed 3224 times
-
- SaveAs a PDF.txt
- (12.66 KiB) Downloaded 138 times
OpenOffice 4.1.3 on OSX 10.11.6, iMac Desktop
Re: Macro to "Export as PDF" not completing that task.
Use the "Code" tag for attaching maco codes in this forrum.
And WRITE your macros instead of recording them...
Your macro code is not transparent, tangled. The 'arrays of the arrays' part is not readable.
You need study the API (Application Programming Interface) functions if you want to write effective your macros.
And WRITE your macros instead of recording them...
Your macro code is not transparent, tangled. The 'arrays of the arrays' part is not readable.
You need study the API (Application Programming Interface) functions if you want to write effective your macros.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Re: Macro to "Export as PDF" not completing that task.
Here is the code that is in the txt file above.
Code: Select all
REM ***** BASIC *****
sub NewRecord1
rem ----------------------------------------------------------------------
rem define variables
dim document as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = "invdata"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())
rem ----------------------------------------------------------------------
dim args3(0) as new com.sun.star.beans.PropertyValue
args3(0).Name = "Nr"
args3(0).Value = 2
dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args3())
rem ----------------------------------------------------------------------
dim args4(0) as new com.sun.star.beans.PropertyValue
args4(0).Name = "ToPoint"
args4(0).Value = "$A$1"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args4())
rem ----------------------------------------------------------------------
dim args5(1) as new com.sun.star.beans.PropertyValue
args5(0).Name = "By"
args5(0).Value = 1
args5(1).Name = "Sel"
args5(1).Value = false
dispatcher.executeDispatch(document, ".uno:GoDownToEndOfData", "", 0, args5())
rem ----------------------------------------------------------------------
dim args6(1) as new com.sun.star.beans.PropertyValue
args6(0).Name = "By"
args6(0).Value = 1
args6(1).Name = "Sel"
args6(1).Value = false
dispatcher.executeDispatch(document, ".uno:GoDown", "", 0, args6())
rem ----------------------------------------------------------------------
dim args7(5) as new com.sun.star.beans.PropertyValue
args7(0).Name = "Flags"
args7(0).Value = "SVD"
args7(1).Name = "FormulaCommand"
args7(1).Value = 0
args7(2).Name = "SkipEmptyCells"
args7(2).Value = false
args7(3).Name = "Transpose"
args7(3).Value = false
args7(4).Name = "AsLink"
args7(4).Value = false
args7(5).Name = "MoveMode"
args7(5).Value = 4
dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, args7())
rem ----------------------------------------------------------------------
dim args8(1) as new com.sun.star.beans.PropertyValue
args8(0).Name = "By"
args8(0).Value = 1
args8(1).Name = "Sel"
args8(1).Value = false
dispatcher.executeDispatch(document, ".uno:GoDown", "", 0, args8())
end sub
sub ExportToPDF
rem ----------------------------------------------------------------------
rem define variables
dim document as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = "$E$5"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
rem ----------------------------------------------------------------------
rem dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())
rem ----------------------------------------------------------------------
dim args3(0) as new com.sun.star.beans.PropertyValue
args3(0).Name = "ToPoint"
args3(0).Value = "$C$7"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args3())
rem ----------------------------------------------------------------------
dim args4(2) as new com.sun.star.beans.PropertyValue
args4(0).Name = "URL"
args4(0).Value = "file:///Users/gptuggle/Desktop/INVOICEStoGO/170808-02-12Goofy-VW.pdf"
args4(1).Name = "FilterName"
args4(1).Value = "calc_pdf_Export"
args4(2).Name = "FilterData"
args4(2).Value = Array(Array("UseLosslessCompression",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("Quality",0,90,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("ReduceImageResolution",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("MaxImageResolution",0,300,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("UseTaggedPDF",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("SelectPdfVersion",0,0,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("ExportNotes",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("ExportBookmarks",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("OpenBookmarkLevels",0,-1,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("UseTransitionEffects",0,true,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("IsSkipEmptyPages",0,true,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("IsAddStream",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("EmbedStandardFonts",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("FormsType",0,1,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("ExportFormFields",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("AllowDuplicateFieldNames",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("HideViewerToolbar",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("HideViewerMenubar",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("HideViewerWindowControls",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("ResizeWindowToInitialPage",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("CenterWindow",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("OpenInFullScreenMode",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("DisplayPDFDocumentTitle",0,true,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("InitialView",0,0,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("Magnification",0,0,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("Zoom",0,100,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("PageLayout",0,0,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("FirstPageOnLeft",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("InitialPage",0,1,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("Printing",0,2,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("Changes",0,4,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("EnableCopyingOfContent",0,true,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("EnableTextAccessForAccessibilityTools",0,true,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("ExportLinksRelativeFsys",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("PDFViewSelection",0,0,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("ConvertOOoTargetToPDFTarget",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("ExportBookmarksToPDFDestination",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("_OkButtonString",0,"",com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("EncryptFile",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("PreparedPasswords",0,,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("RestrictPermissions",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("PreparedPermissionPassword",0,Array(),com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("PageRange",0,"1",com.sun.star.beans.PropertyState.DIRECT_VALUE))
dispatcher.executeDispatch(document, ".uno:ExportToPDF", "", 0, args4())
rem ----------------------------------------------------------------------
dim args5(0) as new com.sun.star.beans.PropertyValue
args5(0).Name = "ToPoint"
args5(0).Value = "$C$6"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args5())
end sub
sub ExportToPDF1
rem ----------------------------------------------------------------------
rem define variables
dim document as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = "$E$7"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
rem ----------------------------------------------------------------------
dim args2(0) as new com.sun.star.beans.PropertyValue
args2(0).Name = "ToPoint"
args2(0).Value = "$E$5"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())
rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())
rem ----------------------------------------------------------------------
dim args4(0) as new com.sun.star.beans.PropertyValue
args4(0).Name = "ToPoint"
args4(0).Value = "$C$7"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args4())
rem ----------------------------------------------------------------------
dim args5(2) as new com.sun.star.beans.PropertyValue
args5(0).Name = "URL"
args5(0).Value = "file:///Users/gptuggle/Desktop/INVOICEStoGO/190808-02-12Goofy-VW%20.pdf"
args5(1).Name = "FilterName"
args5(1).Value = "calc_pdf_Export"
args5(2).Name = "FilterData"
args5(2).Value = Array(Array("UseLosslessCompression",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("Quality",0,90,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("ReduceImageResolution",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("MaxImageResolution",0,300,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("UseTaggedPDF",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("SelectPdfVersion",0,0,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("ExportNotes",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("ExportBookmarks",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("OpenBookmarkLevels",0,-1,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("UseTransitionEffects",0,true,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("IsSkipEmptyPages",0,true,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("IsAddStream",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("EmbedStandardFonts",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("FormsType",0,1,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("ExportFormFields",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("AllowDuplicateFieldNames",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("HideViewerToolbar",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("HideViewerMenubar",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("HideViewerWindowControls",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("ResizeWindowToInitialPage",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("CenterWindow",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("OpenInFullScreenMode",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("DisplayPDFDocumentTitle",0,true,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("InitialView",0,0,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("Magnification",0,0,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("Zoom",0,100,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("PageLayout",0,0,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("FirstPageOnLeft",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("InitialPage",0,1,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("Printing",0,2,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("Changes",0,4,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("EnableCopyingOfContent",0,true,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("EnableTextAccessForAccessibilityTools",0,true,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("ExportLinksRelativeFsys",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("PDFViewSelection",0,0,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("ConvertOOoTargetToPDFTarget",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("ExportBookmarksToPDFDestination",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("_OkButtonString",0,"",com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("EncryptFile",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("PreparedPasswords",0,,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("RestrictPermissions",0,false,com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("PreparedPermissionPassword",0,Array(),com.sun.star.beans.PropertyState.DIRECT_VALUE),Array("PageRange",0,"1",com.sun.star.beans.PropertyState.DIRECT_VALUE))
dispatcher.executeDispatch(document, ".uno:ExportToPDF", "", 0, args5())
end sub
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: Macro to "Export as PDF" not completing that task.
The macro code here seems to address your problem.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: Macro to "Export as PDF" not completing that task.
Again, thank you very much. This works now - much study ahead for me.FJCC wrote:The macro code here seems to address your problem.
OpenOffice 4.1.3 on OSX 10.11.6, iMac Desktop
Re: Macro to "Export as PDF" not completing that task.
Right you are, of course, but I just wanted to note two issues for anyone who may start out with the same problems (hopefully, to save them some time and frustration). These are,Zizi64 wrote:And WRITE your macros instead of recording them...
Your macro code is not transparent, tangled. The 'arrays of the arrays' part is not readable.
You need study ...
(1) Since the macro text I uploaded for comment was created entirely by the "record macro" function (i.e., I wrote absolutely none of the code), recording fairly straightforward macros can apparently create a mess.
(2) It appears that one simply cannot automatically record a macro whose action is to "Export to PDF" as in the manual menu choice; after correcting this manually as advised above, new trial attempts to record-only to duplicate the desired behavior failed every time.
Apparently "record macro" works for most actions but not all.
I will be taking the time to learn the macro language - as advised - rather than depend on automatic recording. Thanks again for the guidance.
Special thanks to @FJCC , @robleyd, and @Zizi64
Last edited by PhilLens on Tue Aug 15, 2017 6:29 am, edited 2 times in total.
OpenOffice 4.1.3 on OSX 10.11.6, iMac Desktop
Re: [Solved] Macro to "Export as PDF" not completing that ta
The offline help will help you with this - F1 and search for macros;recording and you will find:Apparently "record macro" works for most actions but not all.
Limitations of the macro recorder
The following actions are not recorded:
1- Opening of windows is not recorded.
2- Actions carried out in another window than where the recorder was started are not recorded.
3- Window switching is not recorded.
4- Actions that are not related to the document content are not recorded. For example, changes made in the Options dialogue box, macro organiser, customising.
5- Selections are recorded only if they are done by using the keyboard (cursor travelling), but not when the mouse is used.
6- The macro recorder works only in Calc and Writer.
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.2.2; SlackBuild for 24.2.2 by Eric Hameleers