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
Thank you for posting! I do have an issue however, this code will only allow one file to transfer
from .docx to excel, not several as outlined by the video.
Why does mine only extract data to row m?
I get a compile error on the first list.
Fixed that issue. How can I convert a checkbox in my Word Form to text?
How did you fix the issue? I’ve gone line by line and can’t figure out what is causing it or how to correct. I have been unable to figure it out with Google. My code:
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
Same here!
I also downloaded the sample file, didn’t help
I tried to run but the program highlighed
Sub getWordFormData ()
Dim wdApp As New Word. Application
and gave me the Compile error
User Defined type not defined
Just download the sample file at http://www.exceltrainingvideos.com/tag/get-data-from-microsoft-word-forms-into-excel/
I’ve been playing with the code to try and get the results to populate in a single column, versus row.
Also trying to figure out how to name the ranges based on the filename, and not a static header.
Are you able to assist with either of these?
Thanks!
Jacob
Just do a search at http://www.exceltrainingvideos.com/
hi, I want to have same data in my excel and word files.
so i don’t know how i can integrate them.
in excel file each row represents one person and each column represents one of their characteristics.
but in word i want to rearrange the table. each page represents a person. and every person has a table but i want to link each cell of word table to specific cell in excel file. so i would just enter my data in one file and i would have 2 different tables with same data in different file types.
how can i do that?
Thanks for this excellent training video.
I have one problem however. It looks as if the date format from my word document (for which i used a date input field) sometimes changes when getting into excel, while at other times the correct format is maintained. Can you give a help with this?
Thanks!
Chris
Hi Dinesh Kumar Takyar,
Thank you for your training videos. I learn from them so much. Keep them coming!
I would also like ask something. I understand the code above loops through all content controls in the word document. Is it possible to loop only through specific content control?
My goal is to transfer data from word to excel. But I don’ t need all the data from word, just some of them.
I appreciate your kind response. Thank you.
Hi Dinesh Kumar Takyar,
I read the thread mentioning download the sample file, but i could not get it. Could you enlighten me?