Copy text from a opened PDF File to Excel

alxx_ander · Jan 26th 2019

Hi,
Im trying to copy text from a PDF File to an excel workbook, I have managed to open the PDF File with a userform the user selects the file to open using Application.GetOpenFilename then I use a Function I found to open the PDF file, so far so good, but then when I try to use sendkeys to select all and copy it doesnt work, this file ( Macro ) is going to be used on several computers on several places so I dont know the path of the program used to open the PDF File, any ideas on how to select and copy the text from the PDF File ? Thanks in advance !

Code

Public Bitacora
Dim Bitacora2 As String
Sub Bitacora_a_Excel()
Application.ScreenUpdating = False
UserForm1.Show
Bitacora2 = Bitacora
Call OpenAnyFile(Bitacora2)
Application.Wait (Now + TimeValue("0:00:10"))
SendKeys ("^a")
SendKeys ("^c")

This if the code in the user form

Code

Private Sub CommandButton1_Click()
Bitacora = Application.GetOpenFilename(Title:="Elegir Archivo", filefilter:="PDF files (*.pdf), *.pdf")
FileNum = FreeFile()
If Bitacora = "" Then
Exit Sub
End If
CommandButton2.Visible = True
End Sub
Private Sub CommandButton2_Click()
Unload Me
End Sub

Display More

**Kenneth Hobson** · Jan 27th 2019

Not all pdf files have content that can be selected and copied. If you have Acrobat, not Adobe Reader, one can get the content. Some 3rd party programs that we can use in Shell() can do it probably. After that, you are left with the kludgy SendKeys() sort of method. If you want to continue with that method, see:

http://www.ozgrid.com/forum/showthread.php?t=199670

alxx_ander · Jan 27th 2019

Hi Kenneth thaks for your reply, the pdf files that I´m trying to select and copy are all text and have the same format, if I open manually one of these PDF with Adobe Reader I can select all and copy using CTR + A and CTRL + C, but I want to automatize these process for the final user, by the way I cant use a third party program.

This is the function I use to open the PDF file, but after the file is opened I cant select and copy, the sendkeys ("^a") select the text (code) inside vba instead of selecting the text in the PDF file, watching your code on the other thread I see or at least I think that I need the path of the adobe reader, but the thing is that this excel file is going to be used by several final users on several computers and I dont know the path of their adobe readers, it is posible to track or identify the path after the PDF file is opened, after the "objshell.open" line ? so I can use it on the code of the other thread.

Code

Function OpenAnyFile(strPath As String)
  Set objshell = CreateObject("Shell.Application")
  objshell.Open (strPath)
  End Function

Regards Alex

**Kenneth Hobson** · Jan 27th 2019

Try what I posted in the other thread. The exepath function gets the path

gijsmo · Feb 4th 2019

I've done some PDF extraction in the past and it can get tricky.

As you've indicated you can use SendKeys but that works within Excel rather than Acrobat.

The sequence of events you need are:
1. Launch the PDF file (open it)
2. Activate the opened PDF file
3. SendKeys to highlight and copy all
4. Re-activate the Excel workbook that will store the pasted content
5. Dump the copy into an Excel sheet (presumably)
6. Close the PDF file

To launch the PDF file, I use

Code

ActiveWorkbook.FollowHyperlink "\path\filename.pdf", NewWindow:=True

To activate the PDF file gets a little trickier as it depends on whether Acrobat Reader or Acrobot Pro is installed.

I have used something like the following to determine if Acrobat Reader or Acrobat Pro is installed. This seems to work OK with the later versions of Acrobat.

Code

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


Public Function IsAcrobatOrPro(ByVal strPDFPath As String) As Integer
  #If VBA7 Then
    Dim lParent As LongPtr
  #Else
    Dim lParent As Long
  #End If
  Dim dtStartTime As Date
  Dim strPDFName  As String
  
  IsAcrobatOrPro = 0
  
  On Error Resume Next
  strPDFName = Mid(strPDFPath, InStrRev(strPDFPath, "\") + 1, Len(strPDFPath))
  On Error GoTo 0
  
  dtStartTime = Now()
  Do Until Now() > dtStartTime + TimeValue("00:00:05")
    lParent = 0
    DoEvents
    'For Adobe Reader.
    lParent = FindWindow("AcrobatSDIWindow", strPDFName & " - Adobe Reader")
    If lParent <> 0 Then
      IsAcrobatOrPro = 1
      Exit Do
    End If
    'For Adobe Professional.
    lParent = FindWindow("AcrobatSDIWindow", strPDFName & " - Adobe Acrobat Pro")
    If lParent <> 0 Then
      IsAcrobatOrPro = 2
      Exit Do
    End If
  Loop
End Function

Display More

Once the PDF file is activated the SendKeys commands can be used.
Again, this can get tricky. If the Page Display in Acrobat is set by default to Enable Scrolling, then Ctrl+A will highlight the entire file, otherwise only the active page.
One way around this is to send the Shift+Ctrl+H key sequence to put acrobat into auto-scroll mode.

Then send the Ctrl+A and finally Ctrl+C key commands.

Timing can get tricky too so best to put some wait commands in.

I use the following code to close the PDF file, which again requires using the FindWindow code.

Code

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




#If VBA7 Then
Private Declare PtrSafe Function SetForegroundWindow Lib "user32" (ByVal hwnd As LongPtr) As Long
#Else
Private Declare Function SetForegroundWindow Lib "user32" (ByVal hwnd As Long) As Long
#End If




#If VBA7 Then
Private Declare PtrSafe Function SendMessage Lib "user32" Alias "SendMessageA" _
               (ByVal hwnd As LongPtr, ByVal wMsg As LongPtr, ByVal wParam As LongPtr, ByVal lParam As Any) As LongPtr
#Else
Private 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


Private Const WM_CLOSE = &H10




Public Sub ClosePDF(ByVal strPDFPath As String)
  Dim strPDFName  As String
  Dim dtStartTime As Date
  #If VBA7 Then
  Dim lParent     As LongPtr
  #Else
  Dim lParent     As Long
  #End If
  
  On Error Resume Next
  strPDFName = Mid(strPDFPath, InStrRev(strPDFPath, "\") + 1, Len(strPDFPath))
  On Error GoTo 0


  '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")
    If lParent <> 0 Then Exit Do
    'For Adobe Professional.
    lParent = FindWindow("AcrobatSDIWindow", strPDFName & " - Adobe Acrobat Pro")
    If lParent <> 0 Then Exit Do
  Loop
  
  If lParent <> 0 Then
    SetForegroundWindow (lParent)
  #If VBA7 Then
    SendMessage lParent, WM_CLOSE, 0, CLngPtr(0)
  #Else
    SendMessage lParent, WM_CLOSE, 0, CLng(0)
  #End If
  End If
End Sub

Display More

To put that altogether, you will also need to know the caption of the workbook where you want to paste the data in order to re-activate the window.

To get the window caption, use this on the workbook you want to paste data to:

Code

sCaption = wbPDF.Application.Caption

wbPDF is the workbook object for where the data will be pasted into

Putting that altogether is along the following lines:

Code

'code to open a PDF file, read the contents and paste to empty worksheet
'sPDFile is the path & filename of the PDF file to process
'wsDump is presumed to be an empty worksheet to dump PDF contents to
'sCaption is the caption of the workbook where the data will be dumped
Private Function ReadPDFContents(ByVal sPDFile As String, wsDump As Worksheet, ByVal sCaption As String) As Boolean
  Dim k As Integer
   
  ReadPDFContents = False
  
  'open the PDF file
  ActiveWorkbook.FollowHyperlink sPDFile, NewWindow:=True
  Application.Wait (Now + TimeValue("00:00:01"))
  
  'determine which version is running
  k = IsAcrobatOrPro(sPDFile)
  If k = 0 Then GoTo QuickExit
  
  'switch to the PDF file
  AppActivate IIf(k = 1, JustFileName(sPDFile) & " - " & "Adobe Reader", JustFileName(sPDFile) & " - " & "Adobe Acrobat Pro")
  Application.Wait (Now + TimeValue("00:00:01"))
    
  'this will put acrobat into auto-scroll mode
  'this is a workaround to setting the page display view to Enable Scrolling
  'this will cause auto-scrolling on the opened PDF file 
  'this is not required if page display view is already defaulted to Enable Scrolling (Preferences --> Accessibility)
  SendKeys ("+^h")
  'select All Data In The PDF file
  SendKeys ("^a"), True
  'copy the Data
  SendKeys ("^c"), True
  Application.Wait (Now + TimeValue("00:00:01"))
    
  'reactive the workbook where the dumped data is to be stored
  AppActivate sCaption
  With wsDump
    'paste PDF copied data onto an empty worksheet
    .Range("A1").PasteSpecial
  End With
  
  ClosePDF sPDFile
  'check to see if data was successfully dumped
  If wsDump.Range("A1").SpecialCells(xlLastCell).Row > 0 Then ReadPDFContents = True
  
QuickExit:
  DoEvents
  
  Exit Function
End Function

Display More

The JustFileName code is as follows:

Code

'returns just the filename from the fully qualified filename
Function JustFileName(sFullPath As String) As String
  JustFileName = sFullPath
  If InStrRev(sFullPath, "\") = 0 Then Exit Function
  
  JustFileName = Mid(sFullPath, InStrRev(sFullPath, "\") + 1)
End Function

As with all code involving esoteric functions that manipulate other applications and SendKeys, this may or may not work for you.

I have used code similar to this on a limited basis and it seems to work on the specific files I have needed it for and using the specific version/s of Acrobat I've been able to test it on.

Copy text from a opened PDF File to Excel

Participate now!

Share