Page 1 of 1
[Solved] Macro to export to PDF [code added for] save as ods
Posted: Thu Sep 03, 2015 8:28 pm
by MATT.B
is there a way to modify this code to export to pdf instead of saving as ods I have found some examples but they use the dispatcher and are buggy(don't understand dispatcher coding)
I would like to continue use the mypath and my filename variables my goal is to have a macro that will save the ods copy and then export the pdf copy at the same time
here is my working code for save as ods in path c:\ods pdf with file name set by cell u2
Code: Select all
Sub SaveAs1
On Error GoTo UNKNOWNERROR
'if fnWhichComponent(thisComponent) <> "Spreadsheet" then
exit sub
end if
Dim check as string
dim document as object
dim dispatcher as object
Dim MyFilename as string
dim MyPath as string
Dim url as string
document = ThisComponent.CurrentController.Frame
ThisComponent.currentController.setActiveSheet(ThisComponent.Sheets.getByIndex(0))
oDoc = ThisComponent
oView = oDoc.getCurrentController()
oSheet= oView.getActiveSheet().getCellRangeByName("ZZ2")
oCell = oSheet.getCellByPosition(0,0)
CHECK = oCell.getstring
If check = 5615 then
oSheet= oView.getActiveSheet().getCellRangeByName("U2")
oCell = oSheet.getCellByPosition(0,0)
MyFilename = oCell.getstring
IF MyFilename = " " then
ret& = Msgbox ("Infromation is missing, you will be prompted to save!" , 10 + 1, "INFORMATION MISSING")
EXIT SUB
end if
oSheet= oView.getActiveSheet().getCellRangeByName("U4")
oCell = oSheet.getCellByPosition(0,0)
MyPath = "C:\ods PDF\"
ret& = Msgbox ("File will be saved to folder " & MyPath & " with a filename of " & MyFilename & ".ods" , 10 + 1, "Information")
url = ConvertToUrl(MyPath & MyFilename & ".ods")
oDoc.StoreAsUrl(url,Array())
end if
EXIT SUB
UNKNOWNERROR:
ret& = Msgbox ("Unknown error, Auto save macro is disabled, and you will be prompted to save", 10 + 1, "ERROR")
End Sub
Re: Macro to export to PDF
Posted: Fri Sep 04, 2015 3:50 am
by FJCC
Here is some code I happen to have that saves range on a particular sheet as a PDF. I hope you can use it to adjust your code. I think you can just skip using Arg() and dimension args2() to have only a length of 1.
Code: Select all
sub mypdf
dim args2(1) as new com.sun.star.beans.PropertyValue
dim Arg(0) as new com.sun.star.beans.PropertyValue
Doc = ThisComponent
Controller = Doc.CurrentController
Sheet = Doc.Sheets.getByName("Sheet1")
CellRange = Sheet.getCellRangeByName("$A$1:$G$56")
Controller.select(CellRange)
Cell = Sheet.getCellByPosition(6,10)
'Got this here http://wiki.services.openoffice.org/wiki/API/Tutorials/PDF_export
'See also http://user.services.openoffice.org/en/forum/viewtopic.php?f=44&t=1804
Arg(0).Name = "Selection"
Arg(0).Value = CellRange
args2(0).Name = "FilterName"
args2(0).Value = "calc_pdf_Export"
args2(1).Name = "FilterData"
args2(1).Value = Arg()
Doc.storeToURL("file:///c:/users/fjcc/Desktop/"& Cell.getString()&".pdf",args2())
end sub
Re: Macro to export to PDF
Posted: Fri Sep 04, 2015 2:13 pm
by MATT.B
thank you that worked great
I removed args2(1) so i could get all 6 sheets, is there a way to define what sheets i want exported some times i only want to export sheet 1 some times its sheet 1-3 and other times its all 6.
Re: Macro to export to PDF
Posted: Sat Sep 05, 2015 3:09 am
by MATT.B
some one marked this as solved and i still have questions how configurable options
Re: Macro to export to PDF
Posted: Sat Sep 05, 2015 3:19 am
by FJCC
You can export certain pages using code like
Code: Select all
Arg(0).Name = "PageRange"
Arg(0).Value = "1,3"
though I didn't test that.
Re: Macro to export to PDF
Posted: Sat Sep 05, 2015 11:05 am
by Villeroy
I know that someone on oooforum.org elaborated the configurable options from source code but oooforum.org is down right now.
Re: Macro to export to PDF
Posted: Sat Sep 05, 2015 11:12 am
by RoryOF
If you can find the oooforum entry using Google, it may be possible to access the thread from Google's cache by clicking on the down arrow beside the result from the search engine.
Re: Macro to export to PDF
Posted: Sat Sep 05, 2015 8:50 pm
by Villeroy
Lots of matches for "PDF filteroptions macro site:oooforum.org" but no cached pages.
Re: Macro to export to PDF
Posted: Sat Sep 05, 2015 11:28 pm
by MATT.B
yeah its been down for at least 3 weeks now
Re: Macro to export to PDF
Posted: Sat Sep 05, 2015 11:40 pm
by FJCC
Re: Macro to export to PDF
Posted: Sun Sep 06, 2015 12:38 pm
by Villeroy
WOW. It had been documented before the project came to its standstill.
Re: Macro to export to PDF
Posted: Tue Sep 08, 2015 2:02 pm
by MATT.B
no it doesn't that only gives instructions for api interface but gives nothing for java or basic exports read the page
I have searched for an answer to this question for about 6 months now with out any luck
Re: Macro to export to PDF
Posted: Tue Sep 08, 2015 2:10 pm
by RoryOF
MATT.B wrote:
no it doesn't that only gives instructions for api interface but gives nothing for java or basic exports read the page
I have searched for an answer to this question for about 6 months now with out any luck
It gives full examples in Java. The conversion to BASIC is trivial as the API lines are identical and you need only convert a few code lines.
Re: Macro to export to PDF and save as ods code
Posted: Tue Sep 08, 2015 2:42 pm
by MATT.B
I know there is a lot to my code each section does something for my needs
I found an error with the pdf label in this code and it has been corrected
Code: Select all
function fnWhichComponent(oDoc) as string
'rem function to tell oo what type of document is opened
if HasUnoInterfaces(oDoc, "com.sun.star.lang.XServiceInfo") then
if thisComponent.supportsService ("com.sun.star.text.GenericTextDocument") then
fnWhichComponent = "Text"
elseif thisComponent.supportsService("com.sun.star.sheet.SpreadsheetDocument") then
fnWhichComponent = "Spreadsheet"
elseif thisComponent.supportsService("com.sun.star.presentation.PresentationDocument") then
fnWhichComponent = "Presentation"
elseif thisComponent.supportsService("com.sun.star.drawing.GenericDrawingDocument") then
fnWhichComponent = "Drawing"
else
fnWhichComponent = "Oops current document something else"
end if
else
fnWhichComponent = "Not a document"
end if
End function
Sub SaveAs1
On Error GoTo UNKNOWNERROR
'--------checks doc type exits macro if not spredsheet seems pointless now that I added the exit on error
if fnWhichComponent(thisComponent) <> "Spreadsheet" then
exit sub
end if
'-----------sets up variables for code------
Dim check as string
dim document as object
dim dispatcher as object
Dim MyFilename as string
dim MyPath as string
Dim url as string
dim url1 as string
DIM PAGE AS STRING
document = ThisComponent.CurrentController.Frame
ThisComponent.currentController.setActiveSheet(ThisComponent.Sheets.getByIndex(0))
oDoc = ThisComponent
oView = oDoc.getCurrentController()
'------in cell zz1-3 I have a code listed that will changed the coding depending on the doc this is used for setting variables later-------
oSheet= oView.getActiveSheet().getCellRangeByName("ZZ2")
oCell = oSheet.getCellByPosition(0,0)
CHECK = oCell.getstring
'---- this section takes a file name from a cell that cell has a formula in it that takes 4 cells and makes the file name for me--------
If check = 5615 then
oSheet= oView.getActiveSheet().getCellRangeByName("U2")
oCell = oSheet.getCellByPosition(0,0)
MyFilename = oCell.getstring
'---- lets the user know auto save is disabled
IF MyFilename = " " then
ret& = Msgbox ("Infromation is missing, you will be prompted to save!" , 10 + 1, "INFORMATION MISSING")
EXIT SUB
end if
'----- sets the save as path based off a cell or you can set it manually
oSheet= oView.getActiveSheet().getCellRangeByName("U4")
oCell = oSheet.getCellByPosition(0,0)
MyPath = oCell.getstring
'------converts the file name and path to a format that basic can use
url = ConvertToUrl(MyPath & MyFilename & ".ods")
url1= ConvertToUrl(MyPath & MyFilename & ".pdf")
'dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
'ThisComponent.Sheets.getByIndex(0).unprotect("")
'-----message that informs user
ret& = Msgbox ("Files saved to folder " & MyPath & " with a filenames of " & MyFilename & " .ods and " & MyFilename & " .pdf", 10 + 1, "Information")
'---------saves the doc in ods format using the url format of filename and path
oDoc.StoreAsUrl(url,Array())
'--------sets variables for pdf export
dim args12(1) as new com.sun.star.beans.PropertyValue
dim Arg(0) as new com.sun.star.beans.PropertyValue
Doc = ThisComponent
'---- code to set pdf by range , I do by page and have set up print ranges on each sheet
'Controller = Doc.CurrentController
'Sheet = Doc.Sheets.getByName("estimate sheet1")
'CellRange = Sheet.getCellRangeByName("$A$1:$R$48")
'Controller.select(CellRange)
'Cell = Sheet.getCellByPosition(6,10)
'Arg(0).Name = "Selection"
'Arg(0).Value = CellRange
'------ I have a page indicator on my main sheet this checks for the number of pages
ThisComponent.currentController.setActiveSheet(ThisComponent.Sheets.getByIndex(0))
oSheet= oView.getActiveSheet().getCellRangeByName("W47")
oCell = oSheet.getCellByPosition(0,0)
page = oCell.getstring
'------sets the number of pages to export to pdf
IF PAGE = 1 THEN
'----sets pagerange for pdf export
Arg(0).Name = "PageRange"
'----page value for export
Arg(0).Value = "1"
args12(1).Name = "FilterData"
args12(1).Value = Arg()
goto pdf
END IF
IF PAGE = 2 THEN
Arg(0).Name = "PageRange"
Arg(0).Value = "1,2"
args12(1).Name = "FilterData"
args12(1).Value = Arg()
goto pdf
END IF
IF PAGE = 3 THEN
Arg(0).Name = "PageRange"
Arg(0).Value = "1,2,3"
args12(1).Name = "FilterData"
args12(1).Value = Arg()
goto pdf
END IF
IF PAGE = 4 THEN
Arg(0).Name = "PageRange"
Arg(0).Value = "1,2,3,4"
args12(1).Name = "FilterData"
args12(1).Value = Arg()
goto pdf
END IF
IF PAGE = 5 THEN
Arg(0).Name = "PageRange"
Arg(0).Value = "1,2,3,4,5"
args12(1).Name = "FilterData"
args12(1).Value = Arg()
goto pdf
END IF
IF PAGE = 6 THEN
Arg(0).Name = "PageRange"
Arg(0).Value = "1,2,3,4,5,6"
args12(1).Name = "FilterData"
args12(1).Value = Arg()
goto pdf
END IF
pdf:
'----sets up the pdf export
args12(0).Name = "FilterName"
args12(0).Value = "calc_pdf_Export"
'----adds the varables for pagerange or any other variables you have added
'args12(1).Name = "FilterData"
'args12(1).Value = Arg()
'-------------saves the pdf format of doc
Doc.storeToURL(url1,args12())
end if
EXIT SUB
'----- changes the standard error message to a custom one so that the code isn't exposed to the user on errors
UNKNOWNERROR:
ret& = Msgbox ("Unknown error, Auto save macro is disabled, and you will be prompted to save", 10 + 1, "ERROR")
End Sub
Re: [solved]Macro to export to PDF [code added for] save as
Posted: Wed Sep 09, 2015 2:09 pm
by MATT.B
redirecting to this topic it answers all of my questions in one place and gives an example of final code
viewtopic.php?f=20&t=79013