[Solved] Macro to export to PDF [code added for] save as ods

[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   Expand viewCollapse view

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

[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   Expand viewCollapse view

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


[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.

[MATT.B]

some one marked this as solved and i still have questions how configurable options

[FJCC]

You can export certain pages using code like

Code: Select all   Expand viewCollapse view

Arg(0).Name = "PageRange"
Arg(0).Value = "1,3"

though I didn't test that.

[Villeroy]

I know that someone on oooforum.org elaborated the configurable options from source code but oooforum.org is down right now.

[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.

[Villeroy]

Lots of matches for "PDF filteroptions macro site:oooforum.org" but no cached pages.

[MATT.B]

yeah its been down for at least 3 weeks now

[FJCC]

Does this page help?
https://wiki.openoffice.org/wiki/API/Tutorials/PDF_export

[Villeroy]

WOW. It had been documented before the project came to its standstill.

[MATT.B]

Does this page help?
https://wiki.openoffice.org/wiki/API/Tutorials/PDF_export

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

[RoryOF]

Does this page help?
https://wiki.openoffice.org/wiki/API/Tutorials/PDF_export

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.

[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   Expand viewCollapse view

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


[MATT.B]

redirecting to this topic it answers all of my questions in one place and gives an example of final code
https://forum.openoffice.org/en/forum/v ... 20&t=79013