Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

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