Create & Secure PDFS with Excel

[Ottsel]

I currently have sheet names listed on my "Password" sheet within excel. Column A contains the sheet names I want to generate a PDF of and column B, in the same row, contains the password I want to place onto the newly generated PDF, but I've been encountering numerous problems. I did some searching and found a thread that recommended one way, but I cannot get it to add a password onto the newly generated PDFs for security reasons. I would just add them manually, but its just a large number of PDF's it would save a large amount time if I could get this to work.
Here's what I got so far. It generates the PDFS perfectly, but it does not secure them with the password I want.

Sub GenerateSecureDocuments()

    Dim ws As Worksheet
    Dim sheetName As String
    Dim savePath As String
    Dim lastRow As Long
    Dim cell As Range
    Dim password As String
    Dim currentSheet As Worksheet

    ' ...Set reference to the "Password" worksheet
    Set ws = ThisWorkbook.Sheets("Password")

    ' ...Determine the last row in column A (where sheet names are listed)
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' ...The guy wanted the PDF's to save where this document is located
    savePath = ThisWorkbook.Path & "\"

    ' ...Loop through each sheet name in column A starting from A2
    For Each cell In ws.Range("A2:A" & lastRow)

        ' ...Get the sheet name and password from columns A and B
        sheetName = cell.Value
        password = ws.Cells(cell.Row, "B").Value

        ' ...Check if the sheet exists in the workbook
        On Error Resume Next
        Set currentSheet = ThisWorkbook.Sheets(sheetName)
        On Error GoTo 0

        If Not currentSheet Is Nothing Then
            ' ...Export the sheet as a PDF
            currentSheet.ExportAsFixedFormat Type:=xlTypePDF, _
                                           Filename:=savePath & sheetName & ".pdf", _
                                           Quality:=xlQualityStandard, _
                                           IncludeDocProperties:=True, _
                                           IgnorePrintAreas:=False, _
                                           OpenAfterPublish:=False

            ' ...Add password to the PDF using the shortcut
            Call SecurePDFWithPassword(savePath & sheetName & ".pdf", password)
        End If

        ' ...Reset the currentSheet to Nothing for the next iteration
        Set currentSheet = Nothing
    Next cell

    ' ...Notify the user that the process is complete
    MsgBox "Document(s) created and secured!", vbInformation + vbOKOnly, "abc Companies"

End Sub

Private Sub SecurePDFWithPassword(pdfPath As String, pdfPassword As String)

  Dim AcroApp As Object
  Dim AcroDoc As Object

  On Error GoTo HandleError

  '...Get the full path to the shortcut (assuming it's named "Adobe Acrobat.lnk")
  Dim shortcutPath As String
  shortcutPath = ThisWorkbook.Path & "\Adobe Acrobat.lnk"

  '...Create the COM objects using the shortcut path
  Set AcroApp = CreateObject("WScript.Shell").Exec(shortcutPath).StdOut
  Set AcroDoc = AcroApp.CreateObject("AcroExch.PDDoc")

  If AcroDoc.Open(pdfPath) Then
    Call AcroDoc.SetSecurity(1, pdfPassword, "")
    AcroDoc.Save 1, pdfPath
    AcroDoc.Close
  End If

  AcroApp.Quit
  Set AcroDoc = Nothing
  Set AcroApp = Nothing

  Exit Sub

HandleError:
  MsgBox "Error setting password to PDF: " & Err.Description, vbCritical
End Sub

 

[John_w]

Call AcroDoc.SetSecurity(1, pdfPassword, "") fails with "Object doesn't support this property or method", which means SetSecurity is not a valid method of the Acrobat AcroPDDoc class.

Previously I've tried the code at Secure PDF's generated with Microsoft Access OutputTo, but it doesn't work for me. Note it uses the encryptUsingPolicy method, but nowhere is a password specified.

Perhaps that code, in combination with an Acrobat trusted function might work. That approach is described at How to code Adobe JavaScript, how to code PDF JavaScript - Adobe Acrobat, though I'm not sure where or if a password can be specified.