The following should paste each pdf document in strPath to a new worksheet.
I'll let you decode what you want to do with the PDFs
Sub pdf_To_Excel_Word()
'Macro opens PDF Files as a editable Word Documenta
'Copies the contents of the Word documents
'Pastes the Clipboard contents into Excel
'Declare Variables
Dim myWorksheet As Worksheet
Dim wordApp As Object
Dim myWshShell As Object
Dim strPath As String
Dim oDoc As Object
Dim strFile As String
Dim registryKey As String
Dim wordVersion As String
'Set Variables
On Error Resume Next
Set wordApp = GetObject(, "Word.Application")
If Err Then
Set wordApp = CreateObject("Word.Application")
End If
On Error GoTo 0
Set myWshShell = CreateObject("WScript.Shell")
strPath = "C:\Users\mh15601\Desktop\Projects\Audit Plan\2019\Testing\PDF Files\DONOT DELETE\"
wordVersion = wordApp.Version
registryKey = "HKCU\SOFTWARE\Microsoft\Office\" & wordVersion & "\Word\Options\"
'Open and Copy PDF Files
myWshShell.RegWrite registryKey & "DisableConvertPdfWarning", 1, "REG_DWORD"
strFile = Dir$(strPath & "*.pdf")
While strFile <> ""
Set oDoc = wordApp.Documents.Open(FileName:=strPath & strFile, confirmconversions:=False)
oDoc.Content.Copy
'Excel
Set myWorksheet = ActiveWorkbook.Worksheets.Add
With myWorksheet
.Range("A1").Select
.PasteSpecial Format:="Text"
End With
oDoc.Close SaveChanges:=0
strFile = Dir$()
Wend
'Close Word
wordApp.Quit SaveChanges:=0
myWshShell.RegWrite registryKey & "DisableConvertPdfWarning", 0, "REG_DWORD"
'Clear Word and PDF
Set wordApp = Nothing
Set myWshShell = Nothing
End Sub