How to copy data from pdf to excel using VBA for multiple sheets?

harikris2013

Board Regular
Joined
May 3, 2013
Messages
65
Hi,

I have 200 sheets of data in PDF file which needs to copy to 200 sheets in excel workbook

I need to use only acrobat reader 5.

I have below macro which is copying only one and pasting to sheet1

Please modify the below macro

Sub StartAdobe()

Dim AdobeApp As String
Dim AdobeFile As String
Dim StartAdobe

AdobeApp = "C:\Program Files (x86)\Adobe\Acrobat 5.0\Reader\AcroRd32.exe"
AdobeFile = "C:\Users\Dell\Desktop\Dead Men Tell No Tales.pdf"

StartAdobe = Shell("" & AdobeApp & " " & AdobeFile & "", 1)

Application.OnTime Now + TimeValue("00:00:05"), "FirstStep"

End Sub


Private Sub FirstStep()


SendKeys ("^a")
SendKeys ("^c")

Application.OnTime Now + TimeValue("00:00:10"), "SecondStep"

End Sub

Private Sub SecondStep()

AppActivate "Microsoft Excel"

Range("A1").Activate
SendKeys ("^v")


End Sub
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Are these 200 pages in one pdf, or 200 pdf files? I am assuming the latter although you write that it is only one file.

You need to loop through each of the files in a sub that then calls the startAdobe sub. But somewhere you will also want to close the pdf files.

Let's assume that all the pdf files are in one folder. The following will do the trick. I have used some of your code, but not the paste part, as we can use Excel's paste for that.


Read throuh the code (plus comments) and see what it does and how it differs from yours


<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br><br><SPAN style="color:#00007F">Sub</SPAN> LoopThroughFiles()<br>    <SPAN style="color:#00007F">Dim</SPAN> strFile <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, strPath <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> colFiles <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">New</SPAN> Collection<br>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> rLog <SPAN style="color:#00007F">As</SPAN> Range, rOut <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> wsLog <SPAN style="color:#00007F">As</SPAN> Worksheet, wsOutp <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <br>    strPath = "C:\TEMP\"<br>    strFile = Dir(strPath)<br>    <SPAN style="color:#007F00">' Make a log sheet</SPAN><br>    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> wsLog = Sheets("PdfProcessLog")<br>    <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0<br>    <SPAN style="color:#00007F">If</SPAN> wsLog <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> wsLog = ThisWorkbook.Sheets.Add(before:=Sheets(1))<br>        wsLog.Name = "PdfProcessLog"<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Set</SPAN> rLog = wsLog.Range("A1")<br>    rLog.CurrentRegion.ClearContents<br>    rLog.Value = "PDF files copied to sheets"<br>    <br>    <SPAN style="color:#007F00">' load all the files in a Collection</SPAN><br>    <SPAN style="color:#00007F">While</SPAN> strFile <> ""<br>        <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">StrComp</SPAN>(Right(strFile, 3), "pdf", vbTextCompare) = 0 <SPAN style="color:#00007F">Then</SPAN><br>            colFiles.Add strFile<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        strFile = Dir<br>    <SPAN style="color:#00007F">Wend</SPAN><br>    <br>    Application.DisplayAlerts = <SPAN style="color:#00007F">False</SPAN><br>    <br>    <SPAN style="color:#007F00">'Loop through the pdf's stored in the collection</SPAN><br>    <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> colFiles.Count<br>        <SPAN style="color:#007F00">'List filenames in Column A of the log sheet</SPAN><br>        rLog.Offset(i, 0).Value = colFiles(i)<br>        strFile = Left(colFiles(i), Len(colFiles(i)) - 4)<br>        <br>        <SPAN style="color:#007F00">' Delete sheet with filename if exists</SPAN><br>        <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> wsOutp = Sheets(strFile)<br>        <SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0<br>        <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> wsOutp <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>            wsOutp.Delete<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#007F00">' (Re)Create the worksheet, give it the file name</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> wsOutp = ThisWorkbook.Sheets.Add(after:=wsLog)<br>        wsOutp.Name = strFile<br>        <br>        <SPAN style="color:#007F00">' Now open the file, and copy contents</SPAN><br>        OpenClosePDF colFiles(i), strPath<br>        CopyStep wsOutp<br>    <SPAN style="color:#00007F">Next</SPAN> i<br>    <br>    Application.DisplayAlerts = <SPAN style="color:#00007F">True</SPAN><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><SPAN style="color:#00007F">Sub</SPAN> OpenClosePDF(<SPAN style="color:#00007F">ByVal</SPAN> sAdobeFile <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, <SPAN style="color:#00007F">ByVal</SPAN> sPath <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>)<br><br>    <SPAN style="color:#00007F">Dim</SPAN> sAdobeApp <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> vStartAdobe <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN><br>    <br>    sAdobeApp = "C:\Program Files (x86)\Adobe\Acrobat 5.0\Reader\AcroRd32.exe"<br>    sAdobeApp = "C:\Program Files\Adobe\Reader 11.0\Reader\AcroRd32.exe"<br>    vStartAdobe = Shell("" & sAdobeApp & " " & sPath & sAdobeFile & "", 1)<br>    Application.Wait (Now + TimeValue("0:00:01"))<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CopyStep(wsOutp <SPAN style="color:#00007F">As</SPAN> Worksheet)<br><br>    <SPAN style="color:#007F00">' select all & copy</SPAN><br>    SendKeys "^a", <SPAN style="color:#00007F">True</SPAN><br>    SendKeys "^c", <SPAN style="color:#00007F">True</SPAN><br>     Application.Wait (Now + TimeValue("0:00:01"))<br>   <SPAN style="color:#007F00">' Paste into the sheet from cell A1</SPAN><br>    wsOutp.Paste Cells(1, 1)<br>   <br>    Application.Wait (Now + TimeValue("0:00:01"))<br>    AppActivate "Adobe Reader"<br>    <SPAN style="color:#007F00">' close Reader</SPAN><br>    SendKeys "%{F4}", <SPAN style="color:#00007F">True</SPAN><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br></FONT>
 
Upvote 0
forgot to say - in the OpenClosePDF sub you need to comment out the wrong path to your adobe
 
Upvote 0
Please go through the code: in the sub LoopThroughFiles you see a line where the path for the pdf's is set (At them monent it reads C:Temp). Change it to where your pdfs are.

Try to understand what the code is doing, else you have endless problems if you ever want to change something.
 
Upvote 0
Hi,

Thanks i am new to excel VBA and could not figure out whats happening

I tried with changing the path but i am getting a error like "There was error opening this document.The file does not exist"

Error line: AppActivate "Adobe Reader"{which showed after pressing debug}
 
Upvote 0
What is the folder name of where your pdf's are stored?
 
Upvote 0
Desktop is not a path. The path to Desktop is something like:
C:\Users\John Dory\Desktop
But why would you want 200 pdf files on your desktop? Unless they are in a folder in your desktop. Can you please give me the proper path, so I can amend the code so it will run?
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,034
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top