VBA Pdf Excel with ShellExecute not pasting to Excel

Home / Uncategorized / VBA Pdf Excel with ShellExecute not pasting to Excel

Question:
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?


Answer:

Read more

Leave a Reply

Your email address will not be published. Required fields are marked *