Access windows (in WindowsOS)

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

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. 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. 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:

 

1. Check the file path and if is valid, use the FollowHyperlink method in order to open the PDF file.
2. 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.
3. Find the subsequent child windows with the FindWindowEx API function.
4. 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.