PDA

View Full Version : [SOLVED] Copying from PDFs - Only pastes last file?



JKB
12-10-2015, 02:21 AM
Hi everybody! I have this code:


Sub Sheet2_Button1_Click()
Dim strPath As String
Dim MyFile As String
Dim Adobefile
Dim sep As String: sep = Application.PathSeparator
Dim shTemp As Worksheet: Set shTemp = Worksheets("Temp")
Dim shMain As Worksheet: Set shMain = Worksheets("Main")
strPath = "C:\Users\jkb\Desktop\PDF"
MyFile = Dir(strPath & sep & "*.pdf")
Do While MyFile <> ""
ActiveWorkbook.FollowHyperlink strPath & sep & MyFile
SendKeys ("^a")
SendKeys ("^c")
Application.Wait (Now + TimeValue("00:00:01"))
SendKeys "%{F4}", True
AppActivate "Microsoft Excel"
shTemp.Activate
ActiveSheet.Range("A1").Select
SendKeys ("^v")
MyFile = Dir()
Loop
End Sub

In the folder PDF i have 3 pdfs, my problem is that it only pastes the last one of them, i can see it opens all 3, and at least selects everything in the sheet, but only pastes the last file into excel. In general i think using adobe reader in excel is super annoying! Earlier it didnt close the files after opening, then i found that making it wait a second before pressing alt+f4 was the solution... Any ideas to how i can make it paste all documents into excel?

- In general my code is super unstable... If any tips on how i can make it more stable, it would also be much appreciated. First time using Adobe from VBA.! :)

JKB
12-10-2015, 02:31 AM
I somehow managed to solve the problem myself. It seems that "SendKeys ("^v")" was a bad idea. Using activecell.pastespecial it worked!

But if anybody have some experience in extracting data from a bunch of PDFs, feel free to share! :)