0

I've been trying to get VBA in Excel to copy a PDF to Excel.

I tried different things, which I list below in case someone wants to try some fortune:

  • I tried also the code here, which indicate to enable the reference, but this seems to only work if you have Acrobat pro installed.

  • Use Shell and ShellExecute: I tried the code here, but I kept getting an error when the pdf was opening saying that it could not find the file. I tried using ShellExecute following commentshere and code here, but I couldn't manage to make it work.

Finally, using ShellExecute I found a code here that allows me to open the pdf file without error

Private Declare Function ShellExecute 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 Long

Sub EVN()
    Filename = "C:\test.pdf" 
    ShellExecute 0, "Open", Filename, "", "", vbNormalNoFocus
End Sub

The problem is how to copy the contents once I've opened the file. I tried adding the following after the file is open:

' select all using ctrl a
SendKeys "^a", True
' wait 2 secs
Application.Wait Now + TimeValue("00:00:2")
' copy all using ctrl c
SendKeys "^c"
' wait 2 secs
Application.Wait Now + TimeValue("00:00:2")
' activate this wokbook
Windows("PdfToExcel.xlsb").Activate
Range("a2").Select
ActiveSheet.Paste

But I get an error message when it gets to Range("a2").Select saying Run-time error 1004: Application-defined or object-defined error.

Is there a way to succesfully copy the contents of the pdf file once open to paste it in Excel?

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Selrac
  • 2,203
  • 9
  • 41
  • 84
  • Have you tired qualifying your worksheet? (i.e. **`Dim oW As Worksheet: Set oW = ThisWorkbook.Worksheets("Sheet1")`**). You can then use it as `oW.Range("A2").PasteSpecial..`. I suspect that you are getting this error because Excel doesn't know which range you are referring to. Other advantage of qualifying your worksheet is that you don't have to activate or select your sheets – Zac Aug 15 '17 at 08:14
  • Okay, that work to get rid of the error message, but it is not coping the data from the pdf and the paste uses what I have already in the clipboard. – Selrac Aug 15 '17 at 11:04
  • I managed to resolve the problem by adding a timing after opening the pdf file as per solution here~: https://stackoverflow.com/questions/4675281/using-appactivate-and-sendkeys-in-vba-shell-command – Selrac Aug 15 '17 at 11:57

0 Answers0