Open PDF and copy/paste into excel

[MIRA7]

Good day
I have a pdf document of 85 pages.
I would like to:
1) open the pdf document
2) copy the data on the pdf document
3) paste it into excel
4) Repeat step 2 and 3 for each page of the pdf.
Thanks

 

[MIRA7]

I just tried replacing this line by

AppActivate Application.Caption

, and when I run the code it copies that same code line and paste it next to it on the same line of the code.


now I replaced it by :

AppActivate "Excel"

and it copies the content of the PDF at the end of the module itself instead of the worksheet.

Here is the Complete Code :

Sub startadobe()
Dim AdobeApp As String
Dim AdobeFile As String
Dim startadobe
 
AdobeApp = "C:\Program Files (x86)\Adobe\Reader 11.0\Reader\AcroRd32.exe"
AdobeFile = "M:\TEST1\CARS\Unit134\Combined.pdf"
 
startadobe = Shell(Q(AdobeApp) & " " & Q(AdobeFile), 1)
 
Application.OnTime Now + TimeValue("00:00:10"), "FirstStep"
 
End Sub
Private Sub FirstStep()
SendKeys ("^a")
SendKeys ("^c")
 
Application.OnTime Now + TimeValue("00:00:10"), "SecondStep"
 
End Sub
 
Private Sub SecondStep()
 
AppActivate "Excel"
 
Range("A1").Activate
SendKeys ("^v")
End Sub
Private Function Q(text As String)
    Q = Chr(34) & text & Chr(34)
End Function

 

[John_w]

This works for me. Run the macro from the Excel UI, not from the VBA editor.

Option Explicit


Public Sub Copy_and_Paste_From_PDF_File()

    Dim AdobeApp As String
    Dim AdobeFile As String
    Dim StartAdobe
     
    ActiveSheet.Cells.Clear
    
    AdobeApp = "C:\Program Files (x86)\Adobe\Reader 11.0\Reader\AcroRd32.exe"
    AdobeFile = "M:\TEST1\CARS\Unit134\Combined.pdf"
    
    StartAdobe = Shell(Q(AdobeApp) & " " & Q(AdobeFile), 1)
    
    Application.OnTime Now + TimeValue("00:00:03"), "FirstStep"
    
End Sub

Private Sub FirstStep()
    SendKeys "^a^c", True
    Application.OnTime Now + TimeValue("00:00:03"), "SecondStep"
End Sub
 
Private Sub SecondStep()
    AppActivate Application.Caption
    ActiveSheet.Range("A1").Select
    SendKeys "^v", True
End Sub

Private Function Q(text As String)
    Q = Chr(34) & text & Chr(34)
End Function

But SendKeys and AppActivate are known to be unreliable so it may not work for you.

 

[MIRA7]

I'm sorry - I m new to all that. How do I run it from the Excel UI and not from the VBA Editor. I ve not done it before.

 

[John_w]

Run it from the Developer tab - https://support.office.com/en-gb/article/Run-a-macro-5e855fd2-02d1-45f5-90a3-50e645fe3155#bmrunmacro

 

[MIRA7]

THANK YOU SO MUCH - It works perfect now.
one more thing, the PDF file has multiple sheets. the code only copies the first sheet and not the rest.
How can I make it read the whole PDFs, or the next pages.

 

[John_w]

FirstStep does Ctrl+A followed by Ctrl+C in the PDF document, so it should select and copy all pages in the PDF document. It works for me.

 

[joshjohnsrud]

I also have a PDF that I would like to convert to Excel format. I am completely ignorant as to what you guys are talking about above. But how can I attach my file so we can see my PDF?

Thanks so much

 

[John_w]

The code is opening a specific PDF file in Acrobat Reader. 3 seconds later it does Ctrl+A to select all the contents and Ctrl+C to copy it to the Clipboard. 3 seconds later it switches back to Excel and does Ctrl+V to paste the Clipboard into the active Excel sheet.

You can't attach a file on this forum. Instead, upload it to a file sharing site and provide the link.

 

[joshjohnsrud]

Okay great. https://advisorconnect.sharefile.com/d-s7aae213e66149f4a

 

[John_w]

The code successfully imports "Index Historical Performance.pdf" into Excel. However it has lost the tabular format of the PDF and some lines are in the wrong row, e.g. "Past performance is no guarantee of future results." at the bottom of the page is in row 5. You can see this by doing a manual copy and paste into Excel, which is exactly what the code is doing. A lot of processing of the imported lines to put values etc. into separate cells would be needed to get the data looking like the original.