VBA to fetch information from multiple word document to place in Excel spreadsheet

Hi Everyone,
We have a client intake form that we use to record our initial contact information in word format. Every time this form is filled we save them at a specific folder our server. I would like write a VBA code in Excel document that allows me to fetch a list of information that was collected and filled on the table that is available in every (Word format) client intake form we have in the folder. I attached a snapshot of the information that I am trying to fetch from every word forms filled out and bring init to the Excel register (attached a snapshot also).

I found the following VBA code online however first it asks me to locate the file every time I run it and secondly it bring nothing on the register and I could not find the way to make it work at all. Please see below;

Sub GetFormData()

Application.ScreenUpdating = False
Dim wdApp As New Word.Application
Dim wdDoc As Word.Document
Dim CCtrl As Word.ContentControl
Dim strFolder As String, strFile As String
Dim WkSht As Worksheet, i As Long, j As Long
strFolder = GetFolder
If strFolder = "" Then Exit Sub
Set WkSht = ActiveSheet
i = WkSht.Cells(WkSht.Rows.Count, 1).End(xlUp).Row

strFile = Dir(strFolder & "\*.docx", vbNormal)
While strFile <> ""
i = i + 1
Set wdDoc = wdApp.Documents.Open(Filename:=strFolder & "\" & strFile, AddToRecentFiles:=False, Visible:=False)
With wdDoc
j = 0
For Each CCtrl In .ContentControls
j = j + 1
WkSht.Cells(i, j) = FmFld.Result
Next
End With
wdDoc.Close SaveChanges:=False
strFile = Dir()
Wend
wdApp.Quit
Set wdDoc = Nothing: Set wdApp = Nothing: Set WkSht = Nothing
Application.ScreenUpdating = True


End Sub


Function GetFolder() As String
Dim oFolder As Object
GetFolder = ""
Set oFolder = CreateObject("Shell.Application").BrowseForFolder(0, "Choose a folder", 0)
If (Not oFolder Is Nothing) Then GetFolder = oFolder.Items.Item.Path
Set oFolder = Nothing
End Function

.... I appreciate if you could help me do this task. Thank you in advance!

Assuming each word doc has just one table, try this...

Please Login or Register  to view this content.

Hi Dangelor,
I appreciate the response. I have two tables in the form but I am only trying to extract the information from the first table. Would this work with word documents that have two tables in each?

Hi Dangelor,
I appreciate the response. I have two tables in the form but I am only trying to extract the information from the first table. Would this work with word documents that have two tables in each?

Well, it's set to get the data from table #1. So, if it gives the wrong data, we can set it to table #2. Give the code a try and see what happens.