ExcelNewBee
New Member
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.
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.
Code:
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