Extract pdf text to excel

[S Oberlander]

I got the below code from How to Extract Specific Data from PDF to Excel Using VBA - ExcelDemy
Works great but the pdf path will vary.
How can I have the user get a popup to select which file should get extracted, also is it possible to make this work on multiple files/have the user select multiple files at once.

Sub Extract_Data_from_PDF()

Set MyWorksheet = ActiveWorkbook.Worksheets("Sheet1")
Application_Path = "C:\Program Files (x86)\Adobe\Acrobat 9.0\Acrobat\Acrobat.exe"
PDF_Path = "C:\Users\Myname\Downloads\Statement_05_2022.pdf"

Shell_Path = Application_Path & " """ & PDF_Path & """"
Call Shell(pathname:=Shell_Path, windowstyle:=vbNormalFocus)

Application.Wait Now + TimeValue("0:00:03")

SendKeys "%vpc"
SendKeys "^a"
SendKeys "^c"

MyWorksheet.Range("A1").PasteSpecial Paste:=xlPasteAll

Call Shell("TaskKill /F /IM Acrobat.exe", vbHide)

End Sub

 

[pjmorris]

I'm afraid I'd only do this extract in extremis - we tell our suppliers to provide Excel files as well as the PDF. If you really have to do it I'd explore the new capability within PowerQuery to extract data from PDFs.

Apologies, not the answer requested, but it would be my proposal.

All the best.

 

[S Oberlander]

I'm not asking about extraction suggestions. Believe me I exhausted all other options.
I need to simply know how to get a popup so the pdf_path variable is based on user input.
bonus is if anyone knows how to make this work on multiple files.

 

[pjmorris]

This piece of code might help - you'll probably need to adapt it as I use it to capture the path as a hyperlink to the activecell on a sheet. After the Application.GetOpenFileName function returns sFile has the relevant string.

Sub GetFileShortcut()
  Dim sFile As String
  Dim sPath As String
  Dim sName As String

  sFile = Application.GetOpenFilename(, , "Get Link", , False)
  If sFile <> "" Then
    sPath = Left(sFile, InStrRev(sFile, "\"))
    sName = Right(sFile, Len(sFile) - InStrRev(sFile, "\"))
    ActiveSheet.Hyperlinks.Add anchor:=ActiveCell, Address:=sPath, TextToDisplay:=sName
  End If
End Sub

HTH

 

[S Oberlander]

Not really what I'm looking for.
I need something like Application.FindFile but this simply opens the file.
I need a the path string, the file should remain closed.

 

[pjmorris]

you have'nt tried it - thats exactly what you get in sPath

 

[S Oberlander]

My bad

PDF_Path = sPath & sName does the trick
Any way to select multiple files like this?
Then I can loop thru the next part of the code for each file separately

 

[S Oberlander]

Also, would you know how to edit this line Call Shell("TaskKill /F /IM Acrobat.exe", vbHide)
it should only close the file that was opened and not the entire acrobat?

 

[S Oberlander]

Gosh I hope you can tell I'm low on gas caffeine and sleep
sFile=Application.GetOpenFilename(, , "Get Link", , False) is the only part of your code that I need

 

[S Oberlander]

Below is my edited code that does everything I need it to including the bonus. I'm just posting in case anyone finds it helpful.
@pjmorris I marked your post as the solution to give you credit for leading me to this.

Sub Extract_Data_from_PDF()
Dim App_Path As String
Dim Pdf_path As Variant
Dim shell_path As String
Dim i As Variant
Dim lastrow As Long
        
    App_Path = "C:\Program Files (x86)\Adobe\Acrobat 9.0\Acrobat\Acrobat.exe"
    Pdf_path = Application.GetOpenFilename("Pdf Files (*.pdf), *.pdf", , "Select Statements", , True)
    
    For i = LBound(Pdf_path) To UBound(Pdf_path)
    
        shell_path = App_Path & " """ & Pdf_path(i) & """"
        Call Shell(pathname:=shell_path, windowstyle:=vbNormalFocus)
        
        Application.Wait Now + TimeValue("0:00:03")
        
        SendKeys "%vpc"
        SendKeys "^a"
        SendKeys "^c"
    
        With ActiveSheet
            lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
        End With
        Range("a" & lastrow + 1).Select
        ActiveSheet.PasteSpecial Format:="Text"
    
    Next i

End Sub