Macro to export Calc spreadsheet as PDF with password

[cianoz]

Hello,
I’m currently using a macro to export a Calc spreadsheet as PDF (and it works).
But my Calc file is password protected and I’d like to keep the password protection in the PDF as well.

I’ve read a few posts here and elsewhere about the code to add to the macro for this purpose but the most are very old, tried some but no one worked.
Does anyone know what code to add to password protect the pdf?
Here is my current macro:

sub ExportPDF_No_Confirm
rem define variables
dim document   as object
dim dispatcher as object
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

dim args1(2) as new com.sun.star.beans.PropertyValue
args1(0).Name = "URL"
args1(0).Value = "file://///PC1/user/mydocs/file1.pdf"
args1(1).Name = "FilterName"
args1(1).Value = "calc_pdf_Export"
dispatcher.executeDispatch(document, ".uno:ExportDirectToPDF", "", 0, args1())
end sub

[sokol92]

Try this way:

' Export to pdf (password protected).
Sub ExportPDF_No_Confirm2
  Dim args1(2) As New com.sun.star.beans.PropertyValue
  Dim args2(1) As New com.sun.star.beans.PropertyValue

  args1(0).Name = "EncryptFile"
  args1(0).Value = True
  args1(1).Name = "DocumentOpenPassword"
  args1(1).Value = "1"                    ' password for Open file
  args1(2).Name = "SelectPdfVersion"
  args1(2).Value = 0
  
  args2(0).Name = "FilterName"
  args2(0).Value = "calc_pdf_Export"
  args2(1).Name = "FilterData"
  args2(1).Value = args1

  ThisComponent.storeToURL(ConvertToUrl("C:\temp\temp.pdf"),  args2())
End Sub

[mikekaganski]

@cianoz

Calc file is password protected and I’d like to keep the password protection in the PDF

Note that, if your intention is to literally keep the same password as in the original document, silently (automatically) obtaining it from the active document, it will be impossible: the password that you used for opening is not kept in memory, and only some descriptors (specific to the file format) are kept, that allow to encrypt back to the same file format, but don’t allow to create new descriptors for another format (like PDF).

[cianoz]

Hi mike,
yes, I’m well aware of this
putting a password in the macro code is an acceptable solution

[cianoz]

Hi sokol92,
Great! Exactly what I meant and it works perfectly
Thanks