【Excel VBA】シートをパスワード保護付きのPDFで出力する

Sub PW付PDF出力()
    Dim userListArray As Variant
    Dim userNumber As String
    Dim userName As String
    Dim loginUrl As String
    Dim password As String
    
    Dim EXPORT_PATH As String: EXPORT_PATH = ThisWorkbook.Path & "\PDF出力先"
    Dim pdfPath As String
    Dim SECURE_EXPORT_PATH As String: SECURE_EXPORT_PATH = ThisWorkbook.Path & "\PW付PDF出力先"
    Dim securePdfPath As String
    
    Dim pdftkPath As String: pdftkPath = """C:\Users\hoge\PDFTKBuilderPortable\App\pdftkbuilder\pdftk.exe"""   'pdftk.exeの絶対パスを指定
    Dim inputFile As String
    Dim outputFile As String
    
    Dim objWSH As Object, objwExec As Object, Result As String
    Dim cmd As String
    
    With Worksheets("リスト")
        userListArray = .Range(.Cells(2, 1), .Cells(.Cells(Rows.Count, 1).End(xlUp).Row, .Cells(1, Columns.Count).End(xlToLeft).Column)).Value
    End With
    
    For i = 1 To UBound(userListArray, 1)
        With Worksheets("出力シート")
            .Cells(1, 1).ClearContents
            .Cells(5, 1).Clear
            
            userNumber = userListArray(i, 1)
            userName = userListArray(i, 2)
            password = userListArray(i, 3)
            loginUrl = userListArray(i, 4)
            
            .Cells(1, 1).Value = "番号: " & userNumber & "　" & userName & " 様"
            .Cells(5, 1).Hyperlinks.Add anchor:=.Cells(5, 1), Address:=loginUrl, TextToDisplay:=loginUrl
            
            pdfPath = EXPORT_PATH & "\" & userNumber & "_" & userName
            
            .ExportAsFixedFormat Type:=xlTypePDF, _
                Filename:=pdfPath, _
                Quality:=xlQualityStandard, _
                IncludeDocProperties:=True, _
                IgnorePrintAreas:=False, _
                OpenAfterPublish:=False
            
            'PDFtkでパスワード保護
            Set objWSH = CreateObject("WScript.Shell")
            
            inputFile = Chr(34) + pdfPath + ".pdf" + Chr(34)
            outputFile = Chr(34) + SECURE_EXPORT_PATH + "\" & userNumber & "_" & userName & ".pdf" + Chr(34)
            
            cmd = "%ComSpec% /c " + Chr(34) + pdftkPath + " " + inputFile + " output " + outputFile + " user_pw " + password + Chr(34)
            
            Set objwExec = objWSH.Exec(cmd)
                
            Do While objwExec.Status = 0
                DoEvents
            Loop
            
        End With
    Next i
    
End Sub