Pull data from multiple pdf files into separate sheets of a workbook

[Zubair57]

Hi Gurus,

I have a folder full of pdf files which have similar template. I want to copy the data from each of these files into separate sheet of a workbook.
I have extracted the list of files and file paths in the workbook and tried to run this macro

My predicament is that this macro only copies data from the last pdf in the list.
If I run this macro separately for each pdf, it works fine. Could you help me out?


Sub Shell_Copy_Paste()
Dim o As Variant
Dim wkSheet As Worksheet, WSn As String, i As Integer

For i = 1 To 3
Mypdf = Worksheets("List").Cells(i, 2).Value
o = Shell("C:\Program Files\Adobe\Reader 9.0\Reader\AcroRd32.exe " & Mypdf, vbNormalFocus)
WSn = Worksheets("List").Cells(i, 3).Value
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = WSn

Set wkSheet = Worksheets(WSn)


SendKeys "^a" 'Select All
SendKeys "^c" 'Copy

SendKeys "%{F4}" 'Close shell application
wkSheet.Range("A1").Select
SendKeys "^v" 'Paste
o = ""
Set wkSheet = Nothing


Next i
End Sub