Get Data from MS Word Forms Automatically into Excel


How to get data from MS Word Forms automatically into Excel using VBA.
Creating forms in Microsoft Word is easily including forms with dates. Analyzing the form data in MS Word can be difficult. Now we can transfer the data from Word forms into MS Excel quickly and easily using VBA code.
Watch the training video below to see how a form is created in MS Word and then using VBA we transfer the data into Excel where it can be analyzed conveniently:


View the video on YouTube.
The complete VBA code to transfer data from Word forms into Excel:
Sub getWordFormData()
Dim wdApp As New Word.Application
Dim myDoc As Word.Document
Dim CCtl As Word.ContentControl
Dim myFolder As String, strFile As String
Dim myWkSht As Worksheet, i As Long, j As Long

myFolder = “C:\Users\takyar\myForms”
Application.ScreenUpdating = False

If myFolder = “” Then Exit Sub
Set myWkSht = ActiveSheet
ActiveSheet.Cells.Clear
Range(“A1”) = “First Name”
Range(“a1”).Font.Bold = True
Range(“B1”) = “Last Name”
Range(“B1”).Font.Bold = True
Range(“C1”) = “House No”
Range(“C1”).Font.Bold = True
Range(“D1”) = “Street/Locality”
Range(“D1”).Font.Bold = True
Range(“E1”) = “City”
Range(“E1”).Font.Bold = True
Range(“F1”) = “Zip”
Range(“F1”).Font.Bold = True
Range(“G1”) = “Gender”
Range(“G1”).Font.Bold = True
Range(“H1”) = “Date of Birth”
Range(“H1”).Font.Bold = True
Range(“I1”) = “Email”
Range(“I1”).Font.Bold = True
Range(“J1”) = “Mobile”
Range(“J1”).Font.Bold = True
Range(“K1”) = “Course Joined”
Range(“K1”).Font.Bold = True
Range(“L1”) = “Fees”
Range(“L1”).Font.Bold = True

i = myWkSht.Cells(myWkSht.Rows.Count, 1).End(xlUp).Row
strFile = Dir(myFolder & “\*.docx”, vbNormal)

While strFile <> “”
i = i + 1

Set myDoc = wdApp.Documents.Open(Filename:=myFolder & “\” & strFile, AddToRecentFiles:=False, Visible:=False)

With myDoc
j = 0
For Each CCtl In .ContentControls
j = j + 1
myWkSht.Cells(i, j) = CCtl.Range.Text
Next
myWkSht.Columns.AutoFit
End With
myDoc.Close SaveChanges:=False
strFile = Dir()
Wend
wdApp.Quit
Set myDoc = Nothing: Set wdApp = Nothing: Set myWkSht = Nothing
Application.ScreenUpdating = True

End Sub

Download a sample file:


Further reading:
Extracting Word form Data and exporting to Excel spreadsheet