Loop through Folder for PDF Files - Open PDF in Word and extract text into Excel

MHamid

Active Member
Hello,

I am using the code below in Excel to open a PDF File in Word andextract the data as text only into Excel. The code works perfectly fine as itis, however, I want to loop through all pdf files within the folder instead ofhardcoding the filename in pathAndFileName variable.

My goal is to be able to:

  1. Open each PDF file in Word
  2. Extract its data as text into the Excel “Test”
  3. Run two excel macros to parse the data that Ineed into another sheet within the same workbook set in the code
  4. Close Word without saving the file
  5. Clear both Word and PDF
  6. Clear the data in Excel “Test” worksheet
  7. Move PDF File to another folder
  8. Repeat steps 1-6 for the next PDF File

Can anyone assist in tweaking this code to do what I need?

Code:
[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]Sub pdf_To_Excel_Word()[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]'Macro opens PDF Files as an editable Word Document[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]'Copies the contents of the Word document[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]'Pastes the Clipboard contents into Excel[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]'Declare Variables[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]    Dim myWorksheet As Worksheet[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]    Dim wordApp AsWord.Application[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]    Dim myWshShell As wshShell[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]    Dim pathAndFileName As String[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]    Dim registryKey As String[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]    Dim wordVersion As String[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]    [/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]'Set Variables[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]    Set myWorksheet =ActiveWorkbook.Worksheets("Test")[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]    Set wordApp = NewWord.Application[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]    Set myWshShell = New wshShell[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]    pathAndFileName ="C:\Users\mh15601\Desktop\Projects\Audit Plan\2019\Testing\PDF Files\DONOT DELETE\A171065-ICG-TTS-Cash Management and Trade-Citibank NA-IndonesiaAudit Report.pdf"[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]    wordVersion = wordApp.Version[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]    registryKey ="HKCU\SOFTWARE\Microsoft\Office\" & wordVersion &"\Word\Options\"[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]'Open and Copy PDF Files[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]    myWshShell.RegWriteregistryKey & "DisableConvertPdfWarning", 1,"REG_DWORD"[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]    [/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]    wordApp.documents.Open _[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]       Filename:=pathAndFileName, _[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]        confirmconversions:=False[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]    [/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]    myWshShell.RegWriteregistryKey & "DisableConvertPdfWarning", 0,"REG_DWORD"[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]    [/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]'Copy Data from Word[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]   wordApp.ActiveDocument.Content.Copy[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]    [/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]'Excel[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]    With myWorksheet[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]       .Range("A1").Select[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]        .PasteSpecialFormat:="Text"[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]    End With[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]    [/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]'Close Word[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]    wordApp.QuitSaveChanges:=wDoNotSaveChanges[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]    [/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]'Clear Word and PDF[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]    Set wordApp = Nothing[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]    Set myWshShell = Nothing[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]    [/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]    [/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]End Sub

Please note: there has been an issue when I type directly in the forum.So I have been typing these messages in word and copy/pasting into the forum.So some of my sentences/words may be missing spaces. That is not how it’s beingtyped it’s just how it’s being pasted for some reason.

Thank you

 

Rijnsent

Well-known Member
Hi MHamid,
first of all: cool that you have a directory named "DONOT DELETE" ;). Secondly: this code helps to loop though files: https://exceloffthegrid.com/vba-code-loop-files-folder-sub-folders/
Integrated with your code that would be something like the code below (untested).
Hope, that helps,
Koen

Code:
Sub pdf_To_Excel_Word_2()
'Macro opens PDF Files as an editable Word Document
'Copies the contents of the Word document
'Pastes the Clipboard contents into Excel

'Declare Variables
    Dim myWorksheet As Worksheet
    Dim wordApp AsWord.Application
    Dim myWshShell As wshShell
    Dim pathAndFileName As String
    Dim registryKey As String
    Dim wordVersion As String
    Dim fileName As Variant

'Set Variables
    Set myWorksheet = ActiveWorkbook.Worksheets("Test")
    Set wordApp = NewWord.Application
    Set myWshShell = New wshShell
    
    wordVersion = wordApp.Version
    registryKey = "HKCU\SOFTWARE\Microsoft\Office\" & wordVersion & "\Word\Options\"

fileName = Dir("C:\Users\mh15601\Desktop\Projects\Audit Plan\2019\Testing\PDF Files\DONOT DELETE\*.pdf")

While fileName <> ""
    
    'Insert the actions to be performed on each file
    'This example will print the file name to the immediate window
    Debug.Print fileName


    'Open and Copy PDF Files
        myWshShell.RegWriteregistryKey & "DisableConvertPdfWarning", 1,"REG_DWORD"
        wordApp.documents.Open fileName:=pathAndFileName, confirmconversions:=False
        myWshShell.RegWriteregistryKey & "DisableConvertPdfWarning", 0,"REG_DWORD"
    'Copy Data from Word
       wordApp.ActiveDocument.Content.Copy
    
    'Excel
        LastRow = myWorksheet.Cells(Cells.Rows.Count, 1).End(xlUp).Row
        With myWorksheet
           .Range("A" & LastRow).Select
            .PasteSpecialFormat:="Text"
        End With
    
    'Close Word
        wordApp.QuitSaveChanges:=wDoNotSaveChanges

    'Set the fileName to the next file
    fileName = Dir
Wend

'Clear Word and PDF
    Set wordApp = Nothing
    Set myWshShell = Nothing


End Sub
 

MHamid

Active Member
Hello,

I am getting an error message stating "Run-time error '5174': Sorry, we couldn't find your file. Was it moved, renamed, or deleted?". The file exists and it is pointing to the correct location.
When I click Debug the highlighted portion of the code is below

Code:
wordApp.Documents.Open fileName:=pathAndFileName, ConfirmConversions:=False
Full Code:
Code:
Sub pdf_To_Excel_Word_2()
'Macro opens PDF Files as an editable Word Document
'Copies the contents of the Word document
'Pastes the Clipboard contents into Excel

'Declare Variables
    Dim myWorksheet As Worksheet
    Dim wordApp As Object
    Dim myWshShell As wshShell
    Dim pathAndFileName As String
    Dim registryKey As String
    Dim wordVersion As String
    Dim fileName As Variant
'Set Variables
    Set myWorksheet = ActiveWorkbook.Worksheets("Test")
    Set wordApp = New Word.Application
    Set myWshShell = New wshShell
    
    wordVersion = wordApp.Version
    registryKey = "HKCU\SOFTWARE\Microsoft\Office\" & wordVersion & "\Word\Options\"
pathAndFileName = Dir("C:\Users\mh15601\Desktop\Projects\Audit Plan\2019\Testing\PDF Files\DO NOT DELETE\*.pdf")
While pathAndFileName <> ""
    
    'Insert the actions to be performed on each file
    'This example will print the file name to the immediate window
    'Debug.Print fileName

    'Open and Copy PDF Files
        myWshShell.RegWrite registryKey & "DisableConvertPdfWarning", 1, "REG_DWORD"
        wordApp.Documents.Open fileName:=pathAndFileName, ConfirmConversions:=False
        myWshShell.RegWrite registryKey & "DisableConvertPdfWarning", 0, "REG_DWORD"
    
    'Copy Data from Word
       wordApp.ActiveDocument.Content.Copy
    
    'Excel
        LastRow = myWorksheet.Cells(Cells.Rows.Count, 1).End(xlUp).Row
        With myWorksheet
            .Range("A" & LastRow).Select
            .PasteSpecial Format:="Text"
        End With
    
    'Close Word
        wordApp.Quit SaveChanges:=wDoNotSaveChanges
    'Set the pathAndFileName to the next file
    pathAndFileName = Dir
Wend
'Clear Word and PDF
    Set wordApp = Nothing
    Set myWshShell = Nothing

End Sub
Thank you
 

Some videos you may like

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
Top