tianan

New Member
Joined
Jul 23, 2010
Messages
3
Hi,

I need to copy the text of 620 pdf files in one folder to excel. The task involves: open one pdf file from the folder, select all, copy and paste the text to the column of excel. Close this pdf file and then open another pdf file, select all, copy and paste the text to the next column in excel. Do this until all the pdf files are pasted into excel.

I found two suggestions from internet but none of them works.

Solution 1:
Sub Example()
Dim ie As Object

Set ie = CreateObject("InternetExplorer.Application")

ie.Visible = True
ie.Navigate "http://www.uscis.gov/files/form/I-9.pdf"

Do Until Not ie.Busy And ie.ReadyState = READYSTATE_COMPLETE
DoEvents
Loop


Do While ie.QueryStatusWB(OLECMDID_SELECTALL) <> OLECMDF_SUPPORTED + OLECMDF_ENABLED
DoEvents
Loop
ie.ExecWB OLECMDID_SELECTALL, OLECMDEXECOPT_DONTPROMPTUSER
ie.ExecWB OLECMDID_COPY, OLECMDEXECOPT_DONTPROMPTUSER

I can't get ExecWB to work, even though I added Microsoft Internet control and Microsoft HTML library to the reference in VB.

Solution 2:
Sub StartAdobeApp()
Dim AdobeApp As String
Dim AdobeFile As String
Dim StartAdobe
For i = 11002 To 11003
AdobeApp = "C:\Program Files\Adobe\Reader 9.0\Reader\AcroRd32.exe"
AdobeFile = "c:\Documents and Settings\pdf\" & CStr(i) & ".pdf"
StartAdobe = Shell("" & AdobeApp & " " & AdobeFile & "", 1)

Application.OnTime Now + TimeValue("00:00:10"), "FirstStep"
Call FirstStep
Next i
End Sub
Private Sub FirstStep()
SendKeys ("^a")
SendKeys ("^c")
Application.OnTime Now + TimeValue("00:00:10"), "SecondStep"
Call SecondStep
End Sub

Private Sub SecondStep()
SendKeys ("%fx")
AppActivate "Microsoft Excel"
ThisWorkbook.Activate
Sheets("working").Activate
Range("a1").Select
nextColumn = Cells(1, Columns.Count).End(xlToLeft).Column + 1
Cells(1, nextColumn).Select
SendKeys ("^v")

End Sub

It only copied the last pdf file to both of the columns (I only tested it with two pdf files). Not sure why the first pdf file wasn't copied.

Also I couldn't figure out how to loop through the folder to the the pdf file names, so I used for i=11002 to 11003. Problem with this is file 11003 may not exist, not sure if my code will have problem when it couldn't find the file. (I haven't reach that far yet).

I am new to VB and spent the whole week on this without success. Please could someone help me?

Many Thanks!
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Watch MrExcel Video

Forum statistics

Threads
1,123,293
Messages
5,600,773
Members
414,405
Latest member
Zaurb

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
Top