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

MHamid

Active Member
Joined
Jan 31, 2013
Messages
340
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
Joined
Oct 17, 2005
Messages
1,124
Office Version
365
Platform
Windows
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
Joined
Jan 31, 2013
Messages
340
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
 

Forum statistics

Threads
1,078,437
Messages
5,340,270
Members
399,361
Latest member
Linford

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top