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

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Locked
MATT.B
Posts: 165
Joined: Tue Nov 18, 2014 2:16 am

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

Post 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
Last edited by MATT.B on Tue Sep 08, 2015 2:47 pm, edited 3 times in total.
OpenOffice 4.1.1 ON WINDOWS 7 64
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Macro to export to PDF

Post 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
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.
MATT.B
Posts: 165
Joined: Tue Nov 18, 2014 2:16 am

Re: Macro to export to PDF

Post 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.
OpenOffice 4.1.1 ON WINDOWS 7 64
MATT.B
Posts: 165
Joined: Tue Nov 18, 2014 2:16 am

Re: Macro to export to PDF

Post by MATT.B »

some one marked this as solved and i still have questions how configurable options
OpenOffice 4.1.1 ON WINDOWS 7 64
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Macro to export to PDF

Post 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.
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.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Macro to export to PDF

Post by Villeroy »

I know that someone on oooforum.org elaborated the configurable options from source code but oooforum.org is down right now.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Macro to export to PDF

Post 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.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Macro to export to PDF

Post by Villeroy »

Lots of matches for "PDF filteroptions macro site:oooforum.org" but no cached pages.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
MATT.B
Posts: 165
Joined: Tue Nov 18, 2014 2:16 am

Re: Macro to export to PDF

Post by MATT.B »

yeah its been down for at least 3 weeks now
OpenOffice 4.1.1 ON WINDOWS 7 64
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Macro to export to PDF

Post by FJCC »

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.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Macro to export to PDF

Post by Villeroy »

WOW. It had been documented before the project came to its standstill.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
MATT.B
Posts: 165
Joined: Tue Nov 18, 2014 2:16 am

Re: Macro to export to PDF

Post 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
OpenOffice 4.1.1 ON WINDOWS 7 64
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: Macro to export to PDF

Post 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.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
MATT.B
Posts: 165
Joined: Tue Nov 18, 2014 2:16 am

Re: Macro to export to PDF and save as ods code

Post 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
OpenOffice 4.1.1 ON WINDOWS 7 64
MATT.B
Posts: 165
Joined: Tue Nov 18, 2014 2:16 am

Re: [solved]Macro to export to PDF [code added for] save as

Post 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
OpenOffice 4.1.1 ON WINDOWS 7 64
Locked