pdf to excel

[mrinal saha]

Hi folks,

I have been trying to execute the below code but unble to do so...Issue is code is running without any error but giving no result. It is opening the pdf file but not copying the data and pasting it into excel..

And any idea if I have to do this with multiple pdf files than how to go about it?

Sub pdf_test()
Dim obj As Variant
Dim ws As Worksheet
Set ws = ActiveSheet
obj = Shell("C:\Program Files\Adobe\Reader 9.0\Reader\AcroRd32.exe C:\Documents and Settings\287940\Desktop\Test Macros\Mrinal Saha.pdf")
'wait time to open the adobe
Application.Wait (Now + TimeSerial(0, 0, 2))
'Application.OnTime Now + TimeValue("00:00:02")
'select all
SendKeys ("^a")

'copy the data
SendKeys ("^c")
'close the shell app
SendKeys ("%{F4}")
AppActivate "Microsoft Excel"
ws.Range("a2").Select
'paste the data
SendKeys ("^v")

End Sub

Thanksss,
Mrinal

 

[Ruddles]

I have been trying to execute the below code but unble to do so...Issue is code is running without any error but giving no result. It is opening the pdf file but not copying the data and pasting it into excel..

If you step through the code one statement at a time, does it do everything and in the correct sequence? I mean, whe you execute the statement SendKeys ("^a"), is all the data in the PDF selected? After the statement SendKeys ("^c") is executed, if you do a manual paste operation, is the data there? Keep stepping through the code testing that each statement has actually worked until you find the one that hasn't.

And any idea if I have to do this with multiple pdf files than how to go about it?

Whoah, slow down! Get your code to work for a single sheet before you start modifying it otherwise you'll never work out where it's going wrong!

 

[Ruddles]

Try this:-

Option Explicit
 
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
 
Private Const SW_SHOWNORMAL As Long = 1
Private Const SW_SHOWMAXIMIZED As Long = 3
Private Const SW_SHOWMINIMIZED As Long = 2
 
Sub pdf_test()
 
Dim obj As Variant
Dim ws As Worksheet
 
Set ws = ActiveSheet

obj = ShellExecute(0, "Open", "C:\Documents and Settings\287940\Desktop\Test Macros\Mrinal Saha.pdf", "", "c:\", SW_SHOWMAXIMIZED)
 
[COLOR=green]'wait time to open the adobe
[/COLOR]Application.Wait (Now + TimeSerial(0, 0, 5))
 
[COLOR=green]'select all & copy
[/COLOR]SendKeys ("^A^C"), 1
 
[COLOR=green]'close the shell app
[/COLOR]SendKeys ("%FX"), 1
 
AppActivate "Microsoft Excel"
ws.Range("a2").Select
 
[COLOR=green]'paste the data
[/COLOR]ActiveSheet.Paste

End Sub

 

[Ruddles]