Dear experts.
Please can you help me with this macro.
The below code converts the excel sheet to pdf. and then password protects the pdf. using passwords i already have in sheet1.
My problem is that, i already have hundreds of pdf. letters in a folder that i need to have it password protected. Is there anyway you can help with this?
it would be gratefully appreciated.
Please can you help me with this macro.
The below code converts the excel sheet to pdf. and then password protects the pdf. using passwords i already have in sheet1.
My problem is that, i already have hundreds of pdf. letters in a folder that i need to have it password protected. Is there anyway you can help with this?
it would be gratefully appreciated.
Code:
Dim fTemp As String
Dim oPdf As String
Dim Pwd As String
Dim fPath As String
Dim nFnd
Dim RowNum
Sub PwdProPDFs()
On Error GoTo ErrHandler
Application.StatusBar = "Generating Salary Slip, Please Wait..."
Application.ScreenUpdating = False
Application.DisplayAlerts = False
fTemp = ThisWorkbook.Path & "" & "Temp.Pdf"
fPath = "C:\Users\abel9\Desktop\Abel Naidoo\New folder"
fPath = IIf(Right(fPath, 1) = "", fPath, fPath & "")
oPdf = fPath & Range("D13") & ".pdf"
nFnd = "*" & Split(Range("D13"), " ")(0) & "*"
RowNum = Application.Match(nFnd, Sheets(3).Columns("B"), 0)
Pwd = Sheets(3).Range("C" & RowNum).Value
With ActiveSheet
.ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=fTemp, _
Quality:=xlQualityStandard
End With
fTemp = """" & fTemp & """"
oPdf = """" & oPdf & """"
Pwd = """" & Pwd & """"
cmdStr = "pdftk " & fTemp _
& " Output " & oPdf _
& " User_pw " & Pwd _
& " Allow AllFeatures"
Shell cmdStr, vbHide
Application.Wait DateAdd("s", 2, Now)
Kill Replace(fTemp, """", "") 'Deleting temporary files.
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.StatusBar = "Protected Salary Slip Generated."
DoEvents
ErrHandler: 'Error Handler
If Err.Number <> 0 Then MsgBox Err.Description, vbCritical
End Sub
Last edited by a moderator: