Introduction
In the past, we have seen two ways to open PDF files with VBA: the first one involved the usage of the Adobe Object Model and it could be only used with Adobe Professional. The second one was more generic; it was taking advantage of the Windows API functions and it could be used by both Adobe Reader and Professional. Both ways worked and continue to work just fine. There is a problem, though: what will happen if the PDF file you want to open is password-protected? Is there a way to fill the password in the open dialog and continue opening the file?
The answer is, yes! The suggested solution relies on several Windows APIs since the Adobe Object Model does not provide an option/method for including the password when opening a PDF file. Note: the VBA code that you will see below is NOT a password cracking piece of code! The code implies that you know the password of the file. It just automates the opening procedure, especially if you have to open multiple PDF files.
The main idea
The main idea behind the code below can be divided into four steps:
- First of all, the code checks if the given path is valid (e.g. the file exists). Then, the ShellExecute API is used to open the file using the associated default program (either Adobe Reader or Adobe Professional).
- The FindWindow API function is used to find the pop-up window that prompts the user to fill the password.
- By using the FindWindowEx API function, the code searches the subsequent child windows until the text box is reached (using the class RICHEDIT50W).
- Finally, the SendMessage and PostMessage API functions are invoked in order to fill the known password and hit the Open button.
Similar to many previous posts, the Spy++ software was used to specify the windows hierarchy when opening the password-protected file. The above image shows the window tree that should be navigated in order to find the text box that will receive the password.
VBA code for opening password-protected files
Below you will find the OpenLockedPdf macro that does the main work, the FileExists function that is responsible for testing the file existence, as well as the SamplePdfTest macro that performs a sample test. Note that:
- The OpenLockedPdf macro can be used to open a PDF file that is NOT password-protected. In that case, the code that is right after the line of ShellExecute is ignored.
- The macro can be used in every Office application, as well as in AutoCAD. It works with both 32 and 64-bit applications.
Option Explicit
'Declaring the necessary API functions for both 64 and 32 bit applications.
#If VBA7 And Win64 Then
'For 64 bit applications.
'Performs an operation on a specified file.
Public Declare PtrSafe Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" _
(ByVal hwnd As LongPtr, _
ByVal lpOperation As String, _
ByVal lpFile As String, _
ByVal lpParameters As String, _
ByVal lpDirectory As String, _
ByVal nShowCmd As Long) As LongPtr
'Retrieves a handle to the top-level window whose class name and window name match the specified strings.
'This function does not search child windows. This function does not perform a case-sensitive search.
Public Declare PtrSafe Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, _
ByVal lpWindowName As String) As LongPtr
'Retrieves a handle to a window whose class name and window name match the specified strings.
'The function searches child windows, beginning with the one following the specified child window.
'This function does not perform a case-sensitive search.
Public Declare PtrSafe Function FindWindowEx Lib "user32" Alias "FindWindowExA" _
(ByVal hWnd1 As LongPtr, _
ByVal hWnd2 As LongPtr, _
ByVal lpClassName As String, _
ByVal lpWindowName As String) As LongPtr
'Sends the specified message to a window or windows. The SendMessage function calls the window procedure
'for the specified window and does not parentWindowurn until the window procedure has processed the message.
Public Declare PtrSafe Function SendMessage Lib "user32" Alias "SendMessageA" _
(ByVal hwnd As LongPtr, _
ByVal wMsg As Long, _
ByVal wParam As LongPtr, _
lParam As Any) As LongPtr
'Places (posts) a message in the message queue associated with the thread that created the specified
'window and parentWindowurns without waiting for the thread to process the message.
Public Declare PtrSafe Function PostMessage Lib "user32" Alias "PostMessageA" _
(ByVal hwnd As LongPtr, _
ByVal wMsg As Long, _
ByVal wParam As LongPtr, _
ByVal lParam As LongPtr) As Long
#Else
'For 32 bit applications.
Public Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" _
(ByVal hwnd As Long, _
ByVal lpOperation As String, _
ByVal lpFile As String, _
ByVal lpParameters As String, _
ByVal lpDirectory As String, _
ByVal nShowCmd As Long) As Long
Public Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long
Public Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" _
(ByVal hWnd1 As Long, _
ByVal hWnd2 As Long, _
ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long
Public Declare Function SendMessage Lib "user32" Alias "SendMessageA" _
(ByVal hwnd As Long, _
ByVal wMsg As Long, _
ByVal wParam As Long, _
lParam As Any) As Long
Public Declare Function PostMessage Lib "user32.dll" Alias "PostMessageA" _
(ByVal hwnd As Long, _
ByVal wMsg As Long, _
ByVal wParam As Long, _
ByVal lParam As Long) As Long
#End If
'Constants used in API functions.
Public Const SW_HIDE As Long = 0
Public Const SW_SHOWNORMAL As Long = 1
Public Const SW_SHOWMAXIMIZED As Long = 3
Public Const SW_SHOWMINIMIZED As Long = 2
Public Const WM_SETTEXT = &HC
Public Const VK_RETURN = &HD
Public Const WM_KEYDOWN = &H100
Public Sub OpenLockedPdf(pdfPath As String, password As String)
'------------------------------------------------------------------------
'Opens a password-protected PDF file, given its (known) password.
'API functions are used to find the pop-up window and fill the password.
'
'The subroutine can be used in every Office application, as well as
'in AutoCAD. It works for both 32 and 64 bit applications.
'The macro also works with PDF files that are NOT password-protected.
'In that case, the code after the line of ShellExecute is ignored.
'Written By: Christos Samaras
'Date: 30/04/2018
'E-mail: [email protected]
'Site: https://myengineeringworld.net
'------------------------------------------------------------------------
'Declaring the necessary variables (different for 32 or 64 bit applications).
#If VBA7 And Win64 Then
Dim parentWindow As LongPtr
Dim firstChildWindow As LongPtr
Dim secondChildFirstWindow As LongPtr
#Else
Dim parentWindow As Long
Dim firstChildWindow As Long
Dim secondChildFirstWindow As Long
#End If
Dim timeCount As Date
'Check if the PDF file exists.
If FileExists(pdfPath) = False Then
MsgBox "The PDF file doesn't exist!", vbCritical, "Error in PDF path"
Exit Sub
End If
'The ShellExecute API will try to open the PDF file using the default application that
'is associated with PDF files (either Adobe Reader or Professional).
ShellExecute Application.hwnd, "Open", pdfPath, vbNullString, "C:\", SW_SHOWNORMAL
'Note: The code below will be ignored if the PDF file has no protection.
'Find the handle of the pop-up window.
timeCount = Now()
Do Until Now() > timeCount + TimeValue("00:00:05")
parentWindow = 0
DoEvents
parentWindow = FindWindow("#32770", "Password")
If parentWindow <> 0 Then Exit Do
Loop
If parentWindow <> 0 Then
'Find the handle of the first child window (it is a group box).
timeCount = Now()
Do Until Now() > timeCount + TimeValue("00:00:05")
firstChildWindow = 0
DoEvents
firstChildWindow = FindWindowEx(parentWindow, ByVal 0&, "GroupBox", vbNullString)
If firstChildWindow <> 0 Then Exit Do
Loop
'Find the handle of the subsequent child window (it is the text box for filling the password).
If firstChildWindow <> 0 Then
timeCount = Now()
Do Until Now() > timeCount + TimeValue("00:00:05")
secondChildFirstWindow = 0
DoEvents
secondChildFirstWindow = FindWindowEx(firstChildWindow, ByVal 0&, "RICHEDIT50W", vbNullString)
If secondChildFirstWindow <> 0 Then Exit Do
Loop
'The handle was found, so...
If secondChildFirstWindow <> 0 Then
'Fill the password in the text box.
SendMessage secondChildFirstWindow, WM_SETTEXT, 0&, ByVal password
'Press the OK button (it is the default action, so no need to find the handle of the button).
PostMessage secondChildFirstWindow, WM_KEYDOWN, VK_RETURN, 0
End If
End If
End If
End Sub
Function FileExists(FilePath As String) As Boolean
'--------------------------------------------------
'Checks if a file exists (using the Dir function).
'--------------------------------------------------
On Error Resume Next
If Len(FilePath) > 0 Then
If Not Dir(FilePath, vbDirectory) = vbNullString Then FileExists = True
End If
On Error GoTo 0
End Function
Sub SamplePdfTest()
'Full path example that can be used in every Office application, as well as with AutoCAD:
'OpenLockedPdf "C:\Users\Christos\Desktop\Locked File.pdf", "Newsletter"
'Relative path example (in Excel):
'OpenLockedPdf ThisWorkbook.Path & "\" & "Locked File.pdf", "Newsletter"
'For this example only (with the button):
OpenLockedPdf ThisWorkbook.Sheets("Open Locked Sample File").Range("C3"), ThisWorkbook.Sheets("Open Locked Sample File").Range("C5")
End Sub
Demonstration video
The video that follows demonstrates the result of the “OpenLockedPdf” macro, as well as gives more information on the usage of Spy++.
Downloads
The zip file contains a sample PDF file that is locked with a password, as well as a sample workbook containing the code presented above that can be used to open that file (the password is included). The workbook can be opened with Excel 2007 or newer. Please enable macros before using it. Finally, there is also a VBA module with the code to attach it in any Office or AutoCAD application you might have.
Hi, Ank,
Each printer has its own peculiarities.
That is why this code works assuming that you will use Adobe Reader/Pro.
To suggest something, I will have to reproduce your problem and write specific code for your needs.
Unfortunately, I cannot propose any generic solution.
Best Regards,
Christos
hi Christos,
the code is perfect.
I can print to physical printer by change the code from ‘Open’ to ‘Print’
“ShellExecute Application.hwnd, “Open”, pdfPath, vbNullString, “C:”, SW_SHOWNORMAL”
But I try to set the default printer to “Microsoft Print to PDF”, and follow the guide of checking spy++ element, it is not easy to set the file name/full path and then send ‘save’ button.
The reason I do this is to remove the password and save the secure PDF file as normal PDF.
Any idea?
Thank you
Ank
Hi, Mayank,
The above sample provides a possible solution to the problem of opening a password-protected PDF file.
However, you have probably a different code to paste from the PDF file back to Excel.
Without seeing the code, I cannot suggest a solution.
If your PDF file is a form, you can check this article that shows how to get the data into Excel.
Best Regards,
Christos
Hii, thanks a lot for the solution to a problem that someone has hardly worked upon. However, I am facing a problem while pasting the password-protected PDF to excel. It is showing an error while pasting as it is not able to read the data from the pdf. Even if I remove the password I am not able to paste multiple pdf to excel and work upon it. Code is working with single pdf but not for multiple pdf(sometimes it does for 2 pdf max). Any help would be great, as this is a major hurdle I am facing in my project. Thanks in advance.!!!
Thanks for your reply.
I’m using a lot of files with the same password.
So I’m very interested in your macros.
My version of windows is 10, 64bit.
and Office is 2010, Acrobat is version 9.
Hi,
Thank you for your kind words regarding the code.
What version of Windows, Office and Acrobat do you use?
Best Regards,
Christos
Thank you for “Open A Password-Protected PDF File With VBA”
But I have one problem running your macro.
The password is not entered in the password input section.
I used the SPY program to check the code of the corresponding upper window and password input window.There is nothing wrong.
However, the macro is stopped at the password input window.
I would appreciate it if you could help me.
Hi, Ali,
Assuming that you have Windows and not Mac or Linux, then press F5 to refresh the Spy++’s list, and try again.
I have an older version of Adobe Pro than you, but I doubt that there was any major change that will make the Spy++ useless.
Best Regards,
Christos
Hello Christos, First of all Thank you for your video ! I would like to share you that i encounter a problem with Spy. Indeed when i try to do the same things than you with the same files it doesnt work. I started Spy and i tried to pass on the text box and after click “ok”. This message appeared “Cannot find windows which matches search criteria”. I use Adobe Acrobat Pro 2017 to read Pdf.
You’re welcome!
Thank you for your great job..
Hi, good day!
Thank you for the code you have provided. If I were to incorporate the above code on outlook and check if the attached PDF file/s were encrypted or not, what are the changes that needs to be done? Thank you!
Hi, Akira,
First of all, thank you for your kind words regarding the code.
However, I am afraid that I don’t understand your question.
It doesn’t matter how somebody will create the pdf file.
When you will try to open it, either with the free Adobe Reader or with any other paid Adobe software, the password window will pop-up.
Best Regards,
Christos
Thank you for “Open A Password-Protected PDF File With VBA”
It is very useful for PDF file created by ACROBAT.(that can not be open by another PDF viewer). Using ACROBAT SDK is too expensive.
Next Step, I would like to print using Microsoft print to pdf using excel vba at following step:
After Open A Password-Protected PDF File
How to code using excel vba ;
?Ctrl+P (@acrobat reader)
?print