Ask Your Question
2

Create a PDF for every sheet, named after the sheet [closed]

asked 2013-12-19 15:25:09 +0200

Kees gravatar image

updated 2013-12-19 16:42:30 +0200

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.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2015-11-16 18:01:51.017614

Comments

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?

Kees gravatar imageKees ( 2013-12-19 16:43:09 +0200 )edit

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"

w_whalley gravatar imagew_whalley ( 2013-12-20 00:21:55 +0200 )edit

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.

Kees gravatar imageKees ( 2013-12-20 11:01:21 +0200 )edit

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.

Kees gravatar imageKees ( 2013-12-20 11:02:28 +0200 )edit

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.

w_whalley gravatar imagew_whalley ( 2013-12-20 18:05:20 +0200 )edit

1 Answer

Sort by » oldest newest most voted
3

answered 2013-12-20 20:34:22 +0200

w_whalley gravatar image

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
edit flag offensive delete link more

Comments

1

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.

Kees gravatar imageKees ( 2013-12-28 04:13:02 +0200 )edit

Question Tools

1 follower

Stats

Asked: 2013-12-19 15:25:09 +0200

Seen: 1,436 times

Last updated: Dec 20 '13