Opening and searching pdf files from Excel

The file PdfLink.xlsb (free download with open source code) contains two short but useful macros for opening and searching pdf files from Excel.

Search1

The first is based on code from My Engineering World. I have just added a simple interface to allow the file path and name, page number and scale factor to be entered on the spreadsheet.  The code may be copied to any other file; just create the range names: Path_name, Pdf_name, Page_num, and Scale_fact, at any convenient location in the new file.

The My Engineering World blog also has a wide range of other software available for free download, including engineering applications, as well as other routines for working with pdf files, using both Acrobat Reader, and the full Acrobat Professional package.

The second opens the Windows File Explorer search, using the search path and text entered on the spreadsheet.  Typical results are shown below:

Searchres1

The search results can be set up to show the files in list format (together with selected details), and a file preview on the right.  The preview is very small by default, but can be dragged to be of readable size, and the selected size (plus the other selected settings) are stored for the next use.  The Windows search is now greatly improved from earlier versions.  Search indexing takes place in the background, without noticeable effect on other operations, search is now very fast (on indexed folders), and the preview also works near instantly.

Both the View pdf and Search macros require the full path to be entered, or will work on the current path if the path entry is left blank.  My favoured method to copy the full directory path is using the Total Commander file management package.  Select the directory you want to copy, then Mark – Copy Name With Path to Clipboard:

Search2

For those who prefer to stick with File Explorer, a similar facility is available, but as usual it is well-buried:  Open File Explorer, select the directory you want, then shift-Right Click and select Copy as path:

Search3

Update 7 Jan 2016:
Bob Phillips commented with code for 64 bit Excel which I have copied in a code box below. I have also updated the download file, so it should work in both 32 bit and 64 bit Excel. Please let me know if not:

Replace the API definitions with this code

'API declaration for the windows "Search" dialog
#If VBA7 Then
    Private Declare PtrSafe Function ShellSearch 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 String
 
#Else
    Private Declare Function ShellSearch 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 String
#End If
Private Const SW_SHOWNORMAL = 1
'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.
#If VBA7 Then
    Public Declare PtrSafe Function FindWindow Lib "user32" Alias "FindWindowA" ( _
        ByVal lpClassName As String, _
        ByVal lpWindowName As String) As LongPtr
#Else
    Public Declare Function FindWindow Lib "user32" Alias "FindWindowA" ( _
        ByVal lpClassName As String, _
        ByVal lpWindowName As String) As Long
#End If
 
'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.
#If VBA7 Then
    Public Declare PtrSafe Function FindWindowEx Lib "user32" Alias "FindWindowExA" ( _
        ByVal hWnd1 As LongPtr, _
        ByVal hWnd2 As LongPtr, _
        ByVal lpsz1 As String, _
        ByVal lpsz2 As String) As LongPtr
#Else
    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
#End If
 
'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.
#If VBA7 Then
    Public Declare PtrSafe Function SetForegroundWindow Lib "user32" (ByVal hwnd As LongPtr) As LongPtr
#Else
    Public Declare Function SetForegroundWindow Lib "user32" (ByVal hwnd As Long) As Long
#End If
 
'Sends the specified message to a window or windows. The SendMessage function calls the window procedure
'for the specified window and does not lParenturn until the window procedure has processed the message.
#If VBA7 Then
    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
#Else
    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
#End If
 
'Places (posts) a message in the message queue associated with the thread that created the specified
'window and lParenturns without waiting for the thread to process the message.
#If VBA7 Then
    Public Declare PtrSafe Function PostMessage Lib "user32.dll" Alias "PostMessageA" ( _
        ByVal hwnd As LongPtr, _
        ByVal wMsg As Long, _
        ByVal wParam As LongPtr, _
        ByVal lParam As Long) As LongPtr
 
#Else
    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 WM_SETTEXT = &HC
Public Const VK_RETURN = &HD
Public Const WM_KEYDOWN = &H100

and also change some variables in the OpenPDF procedure:


#If VBA7 Then
Dim lParent                     As LongPtr
Dim lFirstChildWindow           As LongPtr
Dim lSecondChildFirstWindow     As LongPtr
Dim lSecondChildSecondWindow    As LongPtr
#Else
Dim lParent                     As Long
Dim lFirstChildWindow           As Long
Dim lSecondChildFirstWindow     As Long
Dim lSecondChildSecondWindow    As Long
#End If

This entry was posted in Computing - general, Excel, VBA and tagged , , , , , . Bookmark the permalink.

7 Responses to Opening and searching pdf files from Excel

  1. Bob Phillips says:

    I can’t see Copy Path in my File Explorer, but the way that I do it is to select the file, then click in the navigation path box above the file list which then shows the path which you can copy.

    Like

  2. James Brown says:

    @Bob, you need to hold down the Shift key while right mouse clicking on the file. This will show ‘Copy File Path’ in the context menu.

    Liked by 1 person

  3. Raul Moreno says:

    Hello, Could you please leave the link for 64-bit file. Thank you.

    Like

    • dougaj4 says:

      There is no 64-bit version. If the 32 bit version doesn’t work it shouldn’t be too hard to adapt it, but I don’t have 64 bit Office.

      Like

    • Bob Phillips says:

      I made the changes and quickly tested it. Looks okay so far, but let me knoe.

      Replace the API definitions with this code

      ‘API declaration for the windows “Search” dialog
      #If VBA7 Then
      Private Declare PtrSafe Function ShellSearch& 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)

      #Else
      Private Declare Function ShellSearch& 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)
      #End If

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

      #If VBA7 Then
      Public Declare PtrSafe Function FindWindow Lib “user32” Alias “FindWindowA” ( _
      ByVal lpClassName As String, _
      ByVal lpWindowName As String) As LongPtr
      #Else
      Public Declare Function FindWindow Lib “user32” Alias “FindWindowA” ( _
      ByVal lpClassName As String, _
      ByVal lpWindowName As String) As Long
      #End If

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

      #If VBA7 Then
      Public Declare PtrSafe Function FindWindowEx Lib “user32” Alias “FindWindowExA” ( _
      ByVal hWnd1 As LongPtr, _
      ByVal hWnd2 As LongPtr, _
      ByVal lpsz1 As String, _
      ByVal lpsz2 As String) As LongPtr
      #Else
      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
      #End If

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

      #If VBA7 Then
      Public Declare PtrSafe Function SetForegroundWindow Lib “user32” (ByVal hwnd As LongPtr) As LongPtr
      #Else
      Public Declare Function SetForegroundWindow Lib “user32” (ByVal hwnd As Long) As Long
      #End If

      ‘Sends the specified message to a window or windows. The SendMessage function calls the window procedure
      ‘for the specified window and does not lParenturn until the window procedure has processed the message.

      #If VBA7 Then
      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
      #Else
      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
      #End If

      ‘Places (posts) a message in the message queue associated with the thread that created the specified
      ‘window and lParenturns without waiting for the thread to process the message.

      #If VBA7 Then
      Public Declare PtrSafe Function PostMessage Lib “user32.dll” Alias “PostMessageA” ( _
      ByVal hwnd As LongPtr, _
      ByVal wMsg As Long, _
      ByVal wParam As LongPtr, _
      ByVal lParam As Long) As LongPtr

      #Else
      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

      and also change some variables in the OpenPDF procedure

      #If VBA7 Then
      Dim lParent As LongPtr
      Dim lFirstChildWindow As LongPtr
      Dim lSecondChildFirstWindow As LongPtr
      Dim lSecondChildSecondWindow As LongPtr
      #Else
      Dim lParent As Long
      Dim lFirstChildWindow As Long
      Dim lSecondChildFirstWindow As Long
      Dim lSecondChildSecondWindow As Long
      #End If

      Like

  4. Fred Johnson says:

    Is it possible to make the code search sub-folders as well?

    Like

    • dougaj4 says:

      It’s a Windows setting.
      If you open File Explorer and type something in the search box, it should open the Search Tools tab, which has an “All Subfolders” icon.

      If you select that it should stay in place until you change it (or automatic update changes it for you).

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s