VBA to extract word table information for all files in a folder

Question

I have a folder which contains multiple .docx files. And I have below code to extract the word table information to an Excel as a summary.

But now the code can only apply to a single file with the file name specified, may I know how to loop all the .docx file in the folder to do same action? I am new to vba and have looked over other post for looping all files in a folder, but don't know how to integrate into my code. Thank you.

Sub extractData()
Dim wd As New Word.Application
Dim doc As Word.Document
Dim sh As Worksheet
wd.Visible = True
Set doc = wd.Documents.Open(ActiveWorkbook.Path & "\test file_v20211012.docx")
Set tbls = doc.Tables
Set sh = ActiveSheet

lr = sh.Cells(Rows.Count, 1).End(xlUp).Row + 1

For i = 1 To 10
    sh.Cells(lr, i + 6).Value = Application.WorksheetFunction.Clean(tbls(4).Rows(i + 1).Cells(2).Range.Text)
Next

For i = 1 To 10
    sh.Cells(lr, i + 16).Value = Application.WorksheetFunction.Clean(tbls(5).Rows(i + 1).Cells(2).Range.Text)
Next

doc.Close
wd.Quit
Set doc = Nothing
Set sh = Nothing
Set wd = Nothing
End Sub

If you search for "looping through folders/directories" you will find a whole bunch of answers here on Stack overflow — Ike, Oct 13 '21 at 15:51
I am new to vba and have looked over other post on how to loop through the files in a folder, but have no idea on how to integrate into the above code. Set doc = wd.Documents.Open(ActiveWorkbook.Path & "\test file_v20211012.docx") — Brdchan, Oct 14 '21 at 04:21
But that's the way to learn VBA ... reading, understanding, adapting code to your needs. — Ike, Oct 14 '21 at 06:06

VBA to extract word table information for all files in a folder

0 Answers0