PDF To Excel Conversion Macro

[ExcelNewBee]

I have a vba code which copies the data from a pdf file and paste it in cell A1 of the macro workbook. The code is working perfectly fine. However, this code only converts 1 file each time.
Is there a way to copy data from all the pdf files in the folder and paste it in cell A1 of the macro work book.

Sub PDF2Excel()
Dim pathPDF As String, textPDF As String
Dim openPDF As Object
Dim pathCell As Range, fileCell As Range
Dim objPDF As MsForms.DataObject
Dim textArray() As String
Dim i As Integer, j As Integer
Set objPDF = New MsForms.DataObject
Set pathCell = ActiveSheet.Cells(1, 19)
Set fileCell = ActiveSheet.Cells(2, 19)
pathPDF = pathCell & fileCell
i = InStrRev(pathPDF, ".pdf")
If i = 0 Then
    pathPDF = pathPDF & ".pdf"
End If
If Dir(pathPDF) = "" Then
    MsgBox "The filename you provided could not be found!"
Else
    Set openPDF = CreateObject("Shell.Application")
    openPDF.Open (pathPDF)
    Application.Wait Now + TimeValue("00:00:2")
    SendKeys "^a"
    Application.Wait Now + TimeValue("00:00:2")
    SendKeys "^c"
    Application.Wait Now + TimeValue("00:00:1")
End If
AppActivate ActiveWorkbook.Windows(1).Caption
ActiveSheet.Range("A1").Select
SendKeys "^v"
ActiveSheet.Paste Destination:=Worksheets("Sheet1").Range("A1")
End Sub

 

[Marc L]

You can use the VBA Dir function on that folder adding "\*.pdf" with a loop checking the variable result if not empty …​

​

 

[ExcelNewBee]

Thanks Marc, Could you plese help, Loop is not working.

 

[Marc L]

It should work as explained in VBA help - a must read ! - but as I can't guess anything …​

​

So from many samples anyone can find on web :​

macro to copy closed excel files to a master sheet in same folder

Hi I have lot of excel files in which there is each excel file has only sheet tab All of them has headers starting from From A to K NOTE sample provided is from A to I but should be A to K The excel files are stored in C:\\Invoices . Looking a macro to copy all closed excel files in that...

chandoo.org

Another one :​

Copy data from multiple workbooks into summary tool workbook

Respected Sirs, We have 70 to 80 workbooks in identical format for tools database recording. The users have filled up the required details and sent for consolidation. We are doing this manually and this is taking a lot of time with errors and rework. Kindly help with a code to open different...

chandoo.org