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!
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!
