Create a PDF for every sheet, named after the sheet

I am trying to create a individual pdf for every sheet in spreadsheet (except the first two) and name the pdf the same as the name of the sheet (sheet names can contain spaces).

So far I have come up with the following (based on a lot of googleing):

Sub ExportAsPDFs
	dim document, oFolderDialog as object
	dim pdfDirectory as string
	dim i, iExec as integer

	oFolderDialog = CreateUnoService("com.sun.star.ui.dialogs.OfficeFolderPicker")
	iExec = oFolderDialog.Execute
	if iExec = 1 then
		pdfDirectory = oFolderDialog.Directory + "/"
	
		document = ThisComponent.CurrentController.Frame
		dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
	    names() = ThisComponent.Sheets.getElementNames()
	    for i = 2 to ubound(names)
			dim args1(2) as new com.sun.star.beans.PropertyValue
			args1(0).Name = "URL"
			args1(0).Value = "file://" + pdfDirectory + join(split(names(i), " "), "") + ".pdf"
			args1(1).Name = "FilterName"
			args1(1).Value = "calc_pdf_Export"
		    args1(2).Name = "PageRange"
		    args1(2).Value = i + 1
			
			dispatcher.executeDispatch(document, ".uno:ExportDirectToPDF", "", 0, args1())
		next i
	endif
End Sub

This mostly seems to work, except for the last cucial line. I suspect I am doing something wrong somewhere, but am not sure what or where. The error I get is:

Error saving the document <document name>:
General Error.
General input/output error.

Any help would be greatly appreciated.

Found the reason for the error. pdfDirectory already has the “file://” prefix, so I did not need to prefix it. It now creates a PDF of the whole document, named after the third sheet, instead of a single page PDF for every sheet (except the first thwo).

Does anyone know how I can create a pdf for every individual sheet?

Move the line dim args1(2) as new com.sun.star.beans.PropertyValue above the for next loop.
There is still something wrong when a sheet name contains spaces. Ah, it needs a space in the double quotes. Change to
args1(0).Value = pdfDirectory + join(split(names(i), " "), " ") + “.pdf”

Moving the dim statement did work, now I don’t get the error anymore. The only problem is now that I get a pdf of all the sheets for every sheet name. For instance if I would have sheet 1, sheet 2, sheet 3, sheet 4 and sheet 5, then I get three PDFs called sheet3.pdf, sheet4.pdf and sheet5.pdf, they all will be the same and contain all five sheets. Where I would like three PDFs, sheet3.pdf containing sheet 3, sheet4.pdf containing sheet 4, and sheet5.pdf containing sheet 5.

The join, split statement does not need a space in the double quotes, it is there to strip spaces out of the sheet names, and adding a space in the double quotes would put it back in.

Sorry, I thought you wanted to retain spaces in filenames.
I found this page, http://www.oooforum.org/forum/viewtopic.phtml?t=87886, that describes exporting single pages to pdf by deleting all print ranges, then setting the print range for the selected sheet. It works when selecting sheets by index instead of by name. The print range definition should be “$A$1:$Z$315” instead of what’s given. It would be even better to select the range as a given sheet but I didn’t find how to do that.

This modification of your macro steps through the pages to be printed, deletes all print ranges, sets a print range only for the current sheet and exports to PDF using that sheet’s name (stripping blanks in the name)

Sub ExportAsPDFs
    dim document, oFolderDialog as object
    dim pdfDirectory as string
    dim i, iExec as integer
    oFolderDialog = CreateUnoService("com.sun.star.ui.dialogs.OfficeFolderPicker")
    iExec = oFolderDialog.Execute
    if iExec = 1 then
        pdfDirectory = oFolderDialog.Directory + "/"
        document = ThisComponent.CurrentController.Frame
        dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
        names() = ThisComponent.Sheets.getElementNames()
        dim args1(2) as new com.sun.star.beans.PropertyValue
        dim args2(0) as new com.sun.star.beans.PropertyValue
        for i = 2 to ubound(names)
        delranges  rem delete all print ranges 
        oSheet=ThisComponent.Sheets.GetByIndex(i)
        ThisComponent.CurrentController.setActiveSheet(oSheet)
        rem set print range for sheet
        args2(0).Name = "PrintArea"
        args2(0).Value = "A1:u200" rem adjust as necessary to include all cells
        dispatcher.executeDispatch(document,".uno:ChangePrintArea","",0,args2())
            args1(0).Name = "URL"
            args1(0).Value = pdfDirectory + join(split(names(i), " "), "") + ".pdf"
            args1(1).Name = "FilterName"
            args1(1).Value = "calc_pdf_Export"
            dispatcher.executeDispatch(document, ".uno:ExportDirectToPDF", "", 0, args1())
       next i
    endif
End Sub

sub delranges
rem from http://www.oooforum.org/forum/viewtopic.phtml?t=87886
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
rem loop to all my sheets and delete all printAreas
 For index = 0 to Ubound(thisComponent.getSheets.getElementNames)
   oSheet = ThisComponent.Sheets.getByIndex(index)
   ThisComponent.CurrentController.setActiveSheet(oSheet)
   document = ThisComponent.CurrentController.Frame
   dispatcher.executeDispatch(document, ".uno:DeletePrintArea", "", 0,Array())   
 Next index
 end sub

Thank you so much for doing this, it saved me including the stuff from that link you found and it works great. This will save me lots of time.