Open PDF file in Excel with VBA

Question

I am having trouble opening my pdf file in excel. I wrote a macro to open a pdf document, copy everything and paste it into an excel workbook but I cant get the pdf file to open. I keep getting the 1004 runtime error. Any ideas of help would be appreciated. Here is what I have tried so far:

Public Sub PDFCopy()

Dim o As Variant
Dim App As AcroPDDoc
Worksheets("Sheet3").Range("A2").Activate

'App.Open ("C:\NetworkDiagrams\100-Viking.pdf")
o = Shell("calc.exe", vbNormalNoFocus)
' ActiveWorkbook.FollowHyperlink ("C:\NetworkDiagram\100-Viking.pdf")

Application.Wait Now + TimeValue("00:00:05")
SendKeys ("^a")
SendKeys ("^c")
SendKeys "%{F4}"

Worksheets("Sheet3").Range("A2").Activate

SendKeys ("^v")



End Sub

All three methods have given me the same runtime error. I am out of ideas.

score 1 · Answer 1 · edited May 23 '17 at 11:57

There are two ways to do this.

First, you need to know what is installed in your system.
Acrobat is different from Acrobat or Adobe Reader.

Here's the code if you only have Acrobat Reader. You use the Shell function.
Then to copy the content of PDF, you use the SendKeys.
Kind of dirty code and not 100% reliable but I can say that it still works.

Sub Get_Pdf()
    Dim XLName As String, PDFPath As String, READERPath As String
    Dim OpenPDF, sh As Worksheet

    XLName = ThisWorkbook.Name
    Set sh = Thisworkbook.Sheets(1)
    PDFPath = Application.GetOpenFilename(filefilter:="PDF file (*.pdf), *.pdf")
    If UCase(PDFPath) = "FALSE" Then Exit Sub
    '~~> Below path differs depending Adobe version and installation path
    READERPath = "C:\Program Files\Adobe\Reader 11.0\Reader\AcroRd32.exe "
    Shell READERPath & PDFPath, vbNormalFocus: DoEvents

    Application.Wait Now + TimeValue("00:00:2")

    SendKeys "^a", True
    Application.Wait Now + TimeValue("00:00:2")

    SendKeys "^c"
    Application.Wait Now + TimeValue("00:00:2")

    Windows(XLName).Activate
    sh.Paste sh.Range("A1")
    SendKeys "%{F4}", True
End Sub

If however you have the Acrobat Installed, refer to this post and check the link posted on the correct answer.
An update was posted on the link and it covers opening PDF even if only ADOBE reader is installed.

score 0 · Answer 2 · answered Dec 16 '11 at 19:56

Not sure if this will work for you, but it opens the PDF and copies it in A2; hopefully someone can chime in with something a little cleaner.

Public Sub PDFCopy()

    'Filepath for your Adobe reader
    MyPath = "C:\Program Files\Adobe\Reader 10.0\Reader\AcroRd32.exe"
    'Filepath for your PDF to open
    MyFile = "C:\Documents\test.pdf"
    Shell MyPath & " " & MyFile, vbNormalFocus

    SendKeys ("^a")
    SendKeys ("^c")
    SendKeys "%{F4}"

    Windows("Test.xlsm").Activate
    Worksheets("Sheet2").Activate
    ActiveSheet.Range("A2").Select

    SendKeys ("^v")

End Sub

Open PDF file in Excel with VBA

2 Answers2