We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

how do I exports all sheets to single pdf files with sheet name?

asked 2021-03-31 08:51:34 +0200

abete gravatar image

updated 2021-06-01 22:03:28 +0200

Alex Kemp gravatar image

I have a calc document with 100 sheets. I want to generate 100 pdf with the name of each sheet, not one big pdf... I managed to do it in Excel but not in Libre Office....

edit retag flag offensive close merge delete


I managed to do it in Excel

How? Trying to export to PDF from Excel 2016, I only see an option to export to a single file (maybe limiting to active sheet or selection), not to a series of files...

Mike Kaganski gravatar imageMike Kaganski ( 2021-03-31 09:09:38 +0200 )edit

Also: A sheetname may not be qualified as a filename.
If the most simple case concerning the names and the target folder (same as origional .ods) as well as the pdf options is assured, a macro for the task shouldn't be too complicated. Study the famous texts by Andrew Pitonyak. The appropriate export filter should be calc_pdf_Export.
See also (e.g.): https://forum.openoffice.org/en/forum....
How did you "do it in Excel"?

Lupp gravatar imageLupp ( 2021-03-31 13:23:45 +0200 )edit

2 Answers

Sort by » oldest newest most voted

answered 2021-03-31 14:49:09 +0200

JohnSUN gravatar image

updated 2021-03-31 14:49:50 +0200

It is not very difficult

Sub ExportAllSheetsToPDF
Rem Export options. Now they are minimal. To adjust the PDF format accurately, 
Rem see https://wiki.openoffice.org/wiki/API/Tutorials/PDF_export
Dim ExportArgs(1) as new com.sun.star.beans.PropertyValue
Dim dataRange(0) as new com.sun.star.beans.PropertyValue

Dim oSheets As Variant, oSheet As Variant, oCursor As Variant
Dim sPath As String, sFileName As String
Dim i As Long
    sFileName = ThisComponent.URL
    If sFileName = "" Then Exit Sub ' This spreadsheet wasn't saved, so it hasn't path and name '
Rem Folder to store PDF - same as source file
    sPath = ConvertFromURL(DirectoryNameoutofPath(sFileName,"/"))
    If Right(sPath,1) <> GetPathSeparator() Then sPath = sPath + GetPathSeparator() 
    ExportArgs(0).Name = "FilterName"
    ExportArgs(0).Value = "calc_pdf_Export"
    ExportArgs(1).Name = "FilterData"
    dataRange(0).Name = "Selection"
    oSheets = ThisComponent.getSheets()
    For i = 0 To oSheets.getCount()-1
        oSheet = oSheets.getByIndex(i)
        If oSheet.isVisible Then ' Skip hidden sheets '
Rem Export range with data only (skip first empty columns and rows)
            oCursor = oSheet.createCursor()
Rem Set range with data as param of filter export
            dataRange(0).Value = oCursor
            ExportArgs(1).Value = dataRange()
Rem Create filename by sheet name
            sFileName = sPath + oSheet.getName()+".pdf"
Rem Export
    Next i
End Sub
edit flag offensive delete link more

answered 2021-03-31 12:57:05 +0200

Zizi64 gravatar image

how do I exports all sheets to single pdf files with sheet name?

You can do it by your own macro.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2021-03-31 08:51:34 +0200

Seen: 39 times

Last updated: Mar 31