, about opening PDF with VBA, and it seems to be calling for reuse in other purposes different from the PDF task.
It’s somewhat related to
, so I think I must try to dissasemble both and try to do a mashup of them.
The point is that they rely in a little Microsoft app called
For the example we will manage (print a web in PDF, and open the file), there are several API functions that are beeing called
- API functions (FindWindow, SetForegroundWindow andFindWindowEx) in order to “find” the print window of Internet Explorer and its “child” windows.
- API functions (SendMessage and keybd_event) for changing the PDF file path.
- API functions (FindWindow and PostMessage) for finding the opened PDF document and closing it.The above list describes more or less the sequence of actions that I followed in order to fulfil this task
Also there’s the need to do some WMI queries:
- A custom WMI (Windows Management Instrumentation) function in order to determine if the printer has finished printing.
For the printing task, you should copy the procedures from this
post.
And finally, you’ll need the following tools to accomplish this project:
- Spy++ is a utility that gives you a graphical view of the system’s processes, threads, windows, and window messages.
- API Viewer is a utility that helps you write the API declarations, by providing the correct syntax of each function.
VBA code
' By Christos Samaras
' http://www.myengineeringworld.net
'API functions (FindWindow, SetForegroundWindow andFindWindowEx) in order
'to “find” the print window of Internet Explorer and its “child” windows.
'API functions (SendMessage and keybd_event) for changing the PDF file path.
'A custom WMI (Windows Management Instrumentation) function in order to determine if the printer has finished printing.
'API functions (FindWindow and PostMessage) for finding the opened PDF document and closing it.The above list describes more or less the sequence of actions that I followed in order to fulfil this task. However, I should mention that without the following tools it would be impossible to finish this project:
'Spy++ is a utility that gives you a graphical view of the system’s processes, threads, windows, and window messages.
'API Viewer (href="http://www.activevb.de/rubriken/apiviewer/index-apiviewereng.html") is a utility that helps you write the API declarations, by providing the correct syntax of each function.
'Shows how to use API functions in order to specify a control (i.e. combo box, listbox,...) in a specific window.
'
'It requires the following steps
' • Check the file path and if is valid, use the FollowHyperlink method in order to open the PDF file.
' • With the FindWindow API function find the window of Adobe Reader or Adobe Professional that contains the opened PDF file and bring it to foreground using the SetForegroundWindow API function.
' • Find the subsequent child windows with the FindWindowEx API function.
' • Use the SendMessage and PostMessage API functions in order to send the desired page number and window zoom value to the corresponding textboxes.
'API calls:
'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 Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
'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 Function FindWindowEx Lib "user32" Alias "FindWindowExA" (ByVal hWnd1 As Long, ByVal hWnd2 As Long, ByVal lpsz1 As String, ByVal lpsz2 As String) As Long
'Sets the specified window's show state.
Public Declare Function ShowWindow Lib "user32.dll" (ByVal hwnd As Long, ByVal nCmdShow As Long) As Long
'Brings the thread that created the specified window into the foreground and activates the window.
'Keyboard input is directed to the window, and various visual cues are changed for the user.
'The system assigns a slightly higher priority to the thread that created the foreground
'window than it does to other threads.
Public Declare Function SetForegroundWindow Lib "user32" (ByVal hwnd As Long) As Long
'Suspends the execution of the current thread until the time-out interval elapses.
Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
'Sends the specified message to a window or windows. The SendMessage function calls the window procedure
'for the specified window and does not return until the window procedure has processed the message.
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
'Places (posts) a message in the message queue associated with the thread that created the specified
'window and returns without waiting for the thread to process the message.
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
'Synthesizes a keystroke. The system can use such a synthesized keystroke to generate a WM_KEYUP or
'WM_KEYDOWN message. The keyboard driver's interrupt handler calls the keybd_event function.
Public Declare Sub keybd_event Lib "user32.dll" (ByVal bVk As Byte, ByVal bScan As Byte, ByVal dwFlags As Long, ByVal dwExtraInfo As Long)
'Constants used in API functions.
Public Const SW_MAXIMIZE = 3
Public Const WM_SETTEXT = &HC
Public Const VK_DELETE = &H2E
Public Const KEYEVENTF_KEYUP = &H2
Public Const BM_CLICK = &HF5&
Public Const WM_CLOSE As Long = &H10
Public Const ¼ As Double = 0.25
Public Const ½ As Double = 0.5
Public Const ¾ As Double = 0.75
Public Const ¹÷³ As Double = 0.333
Public Const ²÷³ As Double = 0.667
Private Sub TestPDF()
OpenPDF ThisWorkbook.Path & "\" & "Sample File.pdf", 6, 143
URLToPDF ThisWorkbook.Path & "\" & "Sample File.pdf"
End Sub
Private Sub URLToPDF(pageURL As String, PDFFullPath As String)
' main procedure.
' Loops through all the URLs at column C and print the web pages as PDF using Adobe Professional.
' First, it checks the folder’s path that was selected in the previous step.
' If the folder’s path exists and is not blank it tries to find if there are any
' illegal characters in the PDF files’ path;
' if it finds anyone it replaces it with a “-“.
' Afterwards, the code calls the WebpageToPDF sub using as parameters the URL
' address and the corresponding PDF file name that is provided in the main sheet (by the user).
Dim arrSpecialChr() As String
Dim dblSpCharFound As Double
Dim PDFFullPath As String
Dim i As Long
Dim j As Integer
'An array with special characters that cannot be used for naming a file.
arrSpecialChr() = Split("\ / : * ? " & Chr$(34) & " |", " ")
'Set the default printer to PDFCreator (or whatever you like).
SetDefaultPrinter "PDFCreator"
'Convert the URLs to PDFs.
On Error Resume Next
'Check if the PDF name contains a special/illegal character.
For j = LBound(arrSpecialChr) To UBound(arrSpecialChr)
dblSpCharFound = VBA.InStr(1, PDFFullPath, arrSpecialChr(j))
If dblSpCharFound > 0 Then
PDFFullPath = VBA.Replace(PDFFullPath, arrSpecialChr(j), "-")
End If
Next j
'PDFFullPath = PDFFolder & PDFPath
On Error GoTo 0
'Save the PDF files to the selected folder.
Call WebpageToPDF(pageURL, PDFFullPath & ".pdf")
'Inform the user that macro finished.
MsgBox "Web page successfully saved as PDF!", vbInformation, "Done"
End Sub
Private Sub WebpageToPDF(pageURL As String, PDFPath As String)
'Creates a new web browser object, opens a selected URL and then prints to selected printer
'The API functions FindWindow and SetForegroundWindow are used in order to find the IE window
'and bring it to the foreground (above other windows).
'The macro needs a reference to Windows Script Host Object Model Library, as well
'as to the Microsoft Internet Controls Library in order to work.
'From VBA editor go to Tools -> References -> add the two references.
'Or you can find them at C:\Windows\system32\wshom.ocx and C:\Windows\system32\ieframe.dll.
Dim WebBrowser As InternetExplorer
Dim StartTime As Date
Dim intRet As Long
'Create new web browser object, make it visible,
'maximize the window and navigate to the desired url.
Set WebBrowser = New InternetExplorer
WebBrowser.Visible = True
ShowWindow WebBrowser.hwnd, SW_MAXIMIZE
WebBrowser.Navigate (pageURL)
'Wait until the web page is fully loaded.
Do
DoEvents
Loop Until WebBrowser.ReadyState = READYSTATE_COMPLETE
'Check if the internet explorer window exists.
StartTime = Now()
Do Until Now() > StartTime + TimeValue("00:00:05")
intRet = 0
DoEvents
'IEFrame is the class name for internet explorer.
intRet = FindWindow("IEFrame", vbNullString)
If intRet 0 Then Exit Do
Loop
'If the IE window exists, print the web page as PDF.
If intRet 0 Then
Call SetForegroundWindow(intRet)
WebBrowser.ExecWB OLECMDID_PRINT, OLECMDEXECOPT_DONTPROMPTUSER
Call PDFPrint(PDFPath)
SetForegroundWindow (intRet)
End If
'Release the web browser object.
WebBrowser.Quit
Set WebBrowser = Nothing
End Sub
Private Sub PDFPrint(ByVal strPDFPath As String)
'!!!!!!!!!!!!!!!!!!!!!!
'In order to change the default name (and path) of the save as dialog,
'I used Spy++ in order to specify and edit the combo box
'that contains the file name.
'The sequence goes like this: Save PDF File As (main window) ? DUIViewWndClassName (first child) ? DirectUIHWND (second child) ? FloatNotifySink (third child) ? ComboBox ? Edit.
'Having found the Edit property of the combo box the SendMessage API is used to send the PDF file path.
'
'Well, here there is a tricky part: for some unknown reason if you pass the PDF path using
'directly the SendMessage function and then press the Save button (again, using SendMessage)
'the file is not saved with the desired name and at the desired path!
'The file is named by the URL (for example vba-macro-to-convert-) and is saved at the last folder
'you selected within IE window! Quite strange….
'I overcome this obstacle by doing a small trick: when I pass the PDF path in the combo box
'I use a space before the path. So in the combo box, the SendMessage function passes a string like
'" " & "C:\Users\???st??\Desktop\New folder\" & "Daily Schedule Charts.pdf" (notice the blank space before C).
'Then, I delete this space using the keybd_event API function.
'This function simulates a key press (here the delete button) and a key release.
'Why I did this? Well, because when I was experimenting (without using code)
'I saw that the PDF path changed only if there was a keyboard change.
'So, I tried to simulate this observation using VBA code.
'Having passed the PDF path successfully and pressed the Save button,
'then the macro checks if the printer has finished printing (i.e. creating the PDF file)
'by using the CheckPrinterStatus function.
'If the function returns "Idle" it means that the printing finished.
'Finally, since the Adobe Professional opens the file after finishing the printing,
'a combination of FindWindow and PostMessage API functions are used in order to find the PDF window and close it.
'!!!!!!!!!!!!!!!!!!!!!!
'Prints a web page as PDF file using Adobe Professional.
'API functions are used to specify the necessary windows while
'a WMI function is used to check printer's status.
Dim Ret As Long
Dim ChildRet As Long
Dim ChildRet2 As Long
Dim ChildRet3 As Long
Dim comboRet As Long
Dim editRet As Long
Dim ChildSaveButton As Long
Dim PDFRet As Long
Dim PDFName As String
Dim StartTime As Date
'Find the main print window.
StartTime = Now()
Do Until Now() > StartTime + TimeValue("00:00:05")
Ret = 0
DoEvents
Ret = FindWindow(vbNullString, "Save PDF File As")
If Ret 0 Then Exit Do
Loop
If Ret 0 Then
SetForegroundWindow (Ret)
'Find the first child window.
StartTime = Now()
Do Until Now() > StartTime + TimeValue("00:00:05")
ChildRet = 0
DoEvents
ChildRet = FindWindowEx(Ret, ByVal 0&, "DUIViewWndClassName", vbNullString)
If ChildRet 0 Then Exit Do
Loop
If ChildRet 0 Then
'Find the second child window.
StartTime = Now()
Do Until Now() > StartTime + TimeValue("00:00:05")
ChildRet2 = 0
DoEvents
ChildRet2 = FindWindowEx(ChildRet, ByVal 0&, "DirectUIHWND", vbNullString)
If ChildRet2 0 Then Exit Do
Loop
If ChildRet2 0 Then
'Find the third child window.
StartTime = Now()
Do Until Now() > StartTime + TimeValue("00:00:05")
ChildRet3 = 0
DoEvents
ChildRet3 = FindWindowEx(ChildRet2, ByVal 0&, "FloatNotifySink", vbNullString)
If ChildRet3 0 Then Exit Do
Loop
If ChildRet3 0 Then
'Find the combobox that will be edited.
StartTime = Now()
Do Until Now() > StartTime + TimeValue("00:00:05")
comboRet = 0
DoEvents
comboRet = FindWindowEx(ChildRet3, ByVal 0&, "ComboBox", vbNullString)
If comboRet 0 Then Exit Do
Loop
If comboRet 0 Then
'Finally, find the "edit property" of the combobox.
StartTime = Now()
Do Until Now() > StartTime + TimeValue("00:00:05")
editRet = 0
DoEvents
editRet = FindWindowEx(comboRet, ByVal 0&, "Edit", vbNullString)
If editRet 0 Then Exit Do
Loop
'Add the PDF path to the file name combobox of the print window.
If editRet 0 Then
SendMessage editRet, WM_SETTEXT, 0&, ByVal " " & strPDFPath
keybd_event VK_DELETE, 0, 0, 0 'press delete
keybd_event VK_DELETE, 0, KEYEVENTF_KEYUP, 0 ' release delete
'Get the PDF file name from the full path.
On Error Resume Next
PDFName = Mid(strPDFPath, WorksheetFunction.Find("*", WorksheetFunction.Substitute(strPDFPath, "\", "*", Len(strPDFPath) _
- Len(WorksheetFunction.Substitute(strPDFPath, "\", "")))) + 1, Len(strPDFPath))
On Error GoTo 0
'Save/print the web page by pressing the save button of the print window.
Sleep 1000
ChildSaveButton = FindWindowEx(Ret, ByVal 0&, "Button", "&Save")
SendMessage ChildSaveButton, BM_CLICK, 0, 0
'Sometimes the printing delays, especially in large colorful web pages.
'Here the code checks printer status and if is idle it means that the
'printing has finished.
Do Until CheckPrinterStatus("Adobe PDF") = "Idle"
DoEvents
If CheckPrinterStatus("Adobe PDF") = "Error" Then Exit Do
Loop
'Since the Adobe Professional opens after finishing the printing, find
'the open PDF document and close it (using a post message).
StartTime = Now()
Do Until StartTime > StartTime + TimeValue("00:00:05")
PDFRet = 0
DoEvents
PDFRet = FindWindow(vbNullString, PDFName & " - Adobe Acrobat Pro")
If PDFRet 0 Then Exit Do
Loop
If PDFRet 0 Then
PostMessage PDFRet, WM_CLOSE, 0&, 0&
End If
End If
End If
End If
End If
End If
End If
End Sub
Private Sub OpenPDF(ByVal strPDFPath As String, ByVal strPageNumber As String, ByVal strZoomValue As String)
' Opens a PDF file to a specific page and with a specific zoom.
' API functions are used to specify the necessary windows
' and send the page and zoom info to the PDF reader window.
Dim strPDFName As String
Dim lParent As Long
Dim lFirstChildWindow As Long
Dim lSecondChildFirstWindow As Long
Dim lSecondChildSecondWindow As Long
Dim dtStartTime As Date
'Check if the PDF path is correct.
If FileExists(strPDFPath) = False Then
MsgBox "The PDF path is incorect!", vbCritical, "Wrong path"
Exit Sub
End If
'Get the PDF file name from the full path.
On Error Resume Next
strPDFName = Mid(strPDFPath, InStrRev(strPDFPath, "\") + 1, Len(strPDFPath))
On Error GoTo 0
'The following line depends on the apllication you are using.
'For Word:
'ThisDocument.FollowHyperlink strPDFPath, NewWindow:=True
'For Power Point:
'ActivePresentation.FollowHyperlink strPDFPath, NewWindow:=True
'Note that both Word & Power Point pop up a security window asking
'for access to the specified PDf file.
'For Access:
'Application.FollowHyperlink strPDFPath, NewWindow:=True
'For Excel:
ThisWorkbook.FollowHyperlink strPDFPath, NewWindow:=True
'Find the handle of the main/parent window.
dtStartTime = Now()
Do Until Now() > dtStartTime + TimeValue("00:00:05")
lParent = 0
DoEvents
'For Adobe Reader.
'lParent = FindWindow("AcrobatSDIWindow", strPDFName & " - Adobe Reader")
'For Adobe Professional.
lParent = FindWindow("AcrobatSDIWindow", strPDFName & " - Adobe Acrobat Pro")
If lParent 0 Then Exit Do
Loop
If lParent 0 Then
'Bring parent window to the foreground (above other windows).
SetForegroundWindow (lParent)
'Find the handle of the first child window.
dtStartTime = Now()
Do Until Now() > dtStartTime + TimeValue("00:00:05")
lFirstChildWindow = 0
DoEvents
lFirstChildWindow = FindWindowEx(lParent, ByVal 0&, vbNullString, "AVUICommandWidget")
If lFirstChildWindow 0 Then Exit Do
Loop
'Find the handles of the two subsequent windows.
If lFirstChildWindow 0 Then
dtStartTime = Now()
Do Until Now() > dtStartTime + TimeValue("00:00:05")
lSecondChildFirstWindow = 0
DoEvents
lSecondChildFirstWindow = FindWindowEx(lFirstChildWindow, ByVal 0&, "Edit", vbNullString)
If lSecondChildFirstWindow 0 Then Exit Do
Loop
If lSecondChildFirstWindow 0 Then
'Send the zoom value to the corresponding window.
SendMessage lSecondChildFirstWindow, WM_SETTEXT, 0&, ByVal strZoomValue
PostMessage lSecondChildFirstWindow, WM_KEYDOWN, VK_RETURN, 0
dtStartTime = Now()
Do Until Now() > dtStartTime + TimeValue("00:00:05")
lSecondChildSecondWindow = 0
DoEvents
'Notice the difference in syntax between lSecondChildSecondWindow and lSecondChildFirstWindow.
'lSecondChildSecondWindow is the handle of the next child window after lSecondChildFirstWindow,
'while both windows have as parent window the lFirstChildWindow.
lSecondChildSecondWindow = FindWindowEx(lFirstChildWindow, lSecondChildFirstWindow, "Edit", vbNullString)
If lSecondChildSecondWindow 0 Then Exit Do
Loop
If lSecondChildSecondWindow 0 Then
'Send the page number to the corresponding window.
SendMessage lSecondChildSecondWindow, WM_SETTEXT, 0&, ByVal strPageNumber
PostMessage lSecondChildSecondWindow, WM_KEYDOWN, VK_RETURN, 0
End If
End If
End If
End If
End Sub
Private Function FileExists(strFilePath As String) As Boolean
'Checks if a file exists.
On Error Resume Next
If Not Dir(strFilePath, vbArchive) = vbNullString Then FileExists = True
On Error GoTo 0
End Function
Private Function FolderExists(strFolderPath As String) As Boolean
'Checks if a folder exists.
On Error Resume Next
If Not Dir(strFolderPath, vbDirectory) = vbNullString Then FolderExists = True
On Error GoTo 0
End Function
Private Function FolderSelection() As String
' Shows the folder picker dialog in order the user to select
' the folder in which the downloaded files will be saved.
Dim FoldersPath As String
'Show the folder picker dialog.
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "Select a folder to save your files..."
.Show
If .SelectedItems.Count = 0 Then
MsgBox "You did't select a folder!", vbExclamation, "Canceled"
Exit Function
Else
FoldersPath = .SelectedItems(1)
End If
End With
'Pass the folder's path to the cell.
FolderSelection = FoldersPath & "\"
End Function
Figure 2: The API Viewer window.
Figure 3: The PDFFolderSelection sub results (folder picker dialog).
3. The URLToPDF constitutes the main procedure. First, it checks the folder’s path that was selected in the previous step. If the folder’s path exists and is not blank it tries to find if there are any illegal characters in the PDF files’ path; if it finds anyone it replaces it with a “-“. Afterwards, the code calls the WebpageToPDF sub using as parameters the URL address and the corresponding PDF file name that is provided in the main sheet (by the user).
5. The WebpageToPDF creates a new web browser object, makes it visible, maximizes the browser window and navigates to the desired URL. If the IE window is visible it popups the print window. The API functions FindWindow and SetForegroundWindow are used in order to find the IE window and bring it to the foreground (above other windows). Then the code calls the PDFPrint procedure.
Figure 4: WebpageToPDF sub results.
6. In order to change the default name (and path) of the save as dialog, I used
Spy++ in order to specify and edit the combo box that contains the file name. The sequence goes like this: Save PDF File As (main window) → DUIViewWndClassName (first child) → DirectUIHWND (second child) → FloatNotifySink (third child) → ComboBox → Edit. Having found the Edit property of the combo box the SendMessage API is used to send the PDF file path.
Figure 5: Showing the hierarchy of Save As PDF window in Spy++.
Well, here there is a tricky part: for some unknown reason if you pass the PDF path using directly the SendMessage function and then press the Save button (again, using SendMessage) the file is not saved with the desired name and at the desired path! The file is named by the URL (for example vba-macro-to-convert-) and is saved at the last folder you selected within IE window! Quite strange….
I overcome this obstacle by doing a small trick: when I pass the PDF path in the combo box I use a space before the path. So in the combo box, the SendMessage function passes a string like “ C:\Users\Χρήστος\Desktop\New folder\ Daily Schedule Charts.pdf” (notice the blank space before C). Then, I delete this space using the keybd_event API function. This function simulates a key press (here the delete button) and a key release. Why I did this? Well, because when I was experimenting (without using code) I saw that the PDF path changed only if there was a keyboard change. So, I tried to simulate this observation using VBA code.
Having passed the PDF path successfully and pressed the Save button, then the macro checks if the printer has finished printing (i.e. creating the PDF file) by using the CheckPrinterStatus function. If the function returns “Idle” it means that the printing finished.
Finally, since the Adobe Professional opens the file after finishing the printing, a combination of FindWindow and PostMessage API functions are used in order to find the PDF window and close it.
In comparison with my previous attempt the VBA code below doesn’t use the Adobe object system or the sendkeys method. Instead, a combination of various API functions is used in order to find and manipulate the page number and page zoom textboxes. The general idea behind this code can be divided in four steps:
- Check the file path and if is valid, use the FollowHyperlink method in order to open the PDF file.
- With the FindWindow API function find the window of Adobe Reader or Adobe Professional that contains the opened PDF file and bring it to foreground using the SetForegroundWindow API function.
- Find the subsequent child windows with the FindWindowEx API function.
- Use the SendMessage and PostMessage API functions in order to send the desired page number and window zoom value to the corresponding textboxes.
- Similarly to my previous post, the Spy++ software was used in order to specify the windows hierarchy in Adobe Reader/Professional. The picture above shows the window tree of a sample PDF document.The VBA code in this post can be used with almost all the office programs. I have tested it with Access, Excel, Word and Power Point (both 2010 and 2003 versions) and works like charm. Since it doesn’t require any reference to Adobe library and no sendkeys are used is probably much easier to use and more reliable than my previous codes.
VBA code
The short video below demonstrates how the above VBA code can be used with Access, Word, Power Point and Excel 2010.
[youtube https://www.youtube.com/watch?v=xadHPW2BYS0]
