I have recently decided to use linux only. This means I had to abandon my meticulously crafted Excel macros and find a solution that works equally well.
In one of my macros, I had a button to export my Excel worksheet to a PDF file. This seemed to be a very tricky thing to do in a LibreOffice BASIC macro. There were plenty of tutorials explaining how to export the COMPLETE document (all worksheets) to PDF, but that was not what I was looking for.
In this tutorial, I’ll show you how to Export the first worksheet to PDF with a macro.
The code to export to PDF via macro – one page only
The code listed below takes care of the exporting. However I want to point out that I also included a bonus feature. The filename will be generated with a timestamp. This way, each time you press the print button assigned to the myExportToPDF function, a new file will be generated and you won’t overwrite/lose a perfectly fine exported PDF file:
Dim pdfName As String
Dim time As String
time = Format(Now(), “yy-mm-dd-hh-mm-ss”)
pdfName = “TopconsultantPDF-” & time & “.pdf”
‘ change the path below as per your needs
path =”file:///home/yourusername/” & pdfName
dim document as object
dim dispatcher as object
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService(“com.sun.star.frame.DispatchHelper”)
‘ This part makes sure only the first page gets printed
dim argsF(1) as new com.sun.star.beans.PropertyValue
argsF(0).Name = “PageRange”
argsF(0).Value = “1”
‘ This part sets filename and references the properties above
dim args1(3) as new com.sun.star.beans.PropertyValue
args1(0).Name = “URL”
args1(0).Value = path
args1(1).Name = “FilterName”
args1(1).Value = “calc_pdf_Export”
args1(2).name = “FilterData”
args1(2).value = argsF()
dispatcher.executeDispatch(document, “.uno:ExportDirectToPDF”, “”, 0, args1())
This is how you do it. Let me know if that works for you in the comments below.
Export a part of the worksheet to PDF
What if you only want a certain part of the worksheet to be printed? That’s also possible, and it’s quite easy once we have this code setup. You simply have to select the cells you want to print with your mouse or keyboard, then click in your menu:
Insert > Names > Define
Then you get a popup dialog. You can give this selected range of cells a name (named range) but you also have to click the arrow at the bottom: range options. There, you have to select Print range or Print area. It’s the first item on my screen but my native language is not English so I can’t give you the exact name, I suppose I’m close enough.
That’s it guys. Quick and simple but you wouldn’t believe it: I spent the better part of the day to figure this out! The documentation of LibreOffice leaves much to be desired, however I’m not complaining: it’s a beautiful piece of software and a serious contender for Microsoft Office. I’m sure that with time, the docs will get better and better and these things will be easier to find in the future.
In the meantime, I hope this helps you right now. Let me know in the comments what you think. 😉