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 https://www.exceltrainingvideos.com/tag/get-data-from-microsoft-word-forms-into-excel/
I am getting an error in the first line itself. please help.
Hello Takyar Sir,
My program works absolutely fine except that it cannot capture my option button control data like for example i am trying to capture whether the user is male or female so i am using option button, but my form does not capture any data for that and keeps the field blank. Kindly suggest how to solve this
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 https://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?
Hello Dinesh.
How should I modify the myFolder statement to look in the same folder where the Excel file is located?
Also, some of my content controls are in a table. The macro extracts them all of those into a single cell, which is not useful. How can I avoid that?
Thank you!
Hi Dinesh,
This macro is really a magic and its working perfectly. however, if I have in one word document with two different form for eg (sample form 1 and sample form 2) then how will macro identify and put next form into new excel line. The rule is all the form has same header but different data or in different page.
As currently macro not looking if word have two different form or multiple form. it simply put all the date in one row. looking for your help.
Basically to avoid multiple documents looking for solution.
I’d created a presentation/tutorial in MS PowerPoint, run it in a terminal mode, where users navigate through vba coded buttons. Now, I’m figuring a way to capture who views the PowerPoint (also, trying to capture their feedback). Could anyone please help with a vba code that could be tied to a userform in the beginning or the end of the presentation that would allow users to fill and submit the feedback/registrations? Could an embedded Excel w/VBA help with this?
Hi thanks for sharing! I made a few changes to reflect new column headings, but the macro just doesn’t run. Here’s the 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\ed\Core International\Core International Shared Drive – Core International Shared Drive\6. Tools and Software\Data Extraction Macro trials\Public Works test files\test data”
Application.ScreenUpdating = False
If myFolder = “” Then Exit Sub
Set myWkSht = ActiveSheet
ActiveSheet.Cells.Clear
Range(“A1”) = “role_purpose”
Range(“A1”).Font.Bold = True
Range(“B1”) = “Key_accountabilities”
Range(“B1”).Font.Bold = True
Range(“C1”) = “Time”
Range(“C1”).Font.Bold = True
Range(“D1”) = “Resources”
Range(“D1”).Font.Bold = True
Range(“E1”) = “Needed”
Range(“E1”).Font.Bold = True
Range(“F1”) = “Collaterl_team”
Range(“F1”).Font.Bold = True
Range(“G1”) = “working_well”
Range(“G1”).Font.Bold = True
Range(“H1”) = “not – working”
Range(“H1”).Font.Bold = True
Range(“I1”) = “rel1”
Range(“I1”).Font.Bold = True
Range(“J1”) = “rel1working”
Range(“J1”).Font.Bold = True
Range(“K1”) = “rel1why”
Range(“K1”).Font.Bold = True
Range(“L1”) = “rel2”
Range(“L1”).Font.Bold = True
Range(“M1”) = “rel2working”
Range(“M1”).Font.Bold = True
Range(“M1”) = “rel2why”
Range(“M1”).Font.Bold = True
Range(“N1”) = “rel3”
Range(“N1”).Font.Bold = True
Range(“O1”) = “rel3working”
Range(“O1”).Font.Bold = True
Range(“P1”) = “rel3why”
Range(“P1”).Font.Bold = True
Range(“Q1”) = “rel4”
Range(“Q1”).Font.Bold = True
Range(“R1”) = “rel4working”
Range(“R1”).Font.Bold = True
Range(“S1”) = “rel4why”
Range(“S1”).Font.Bold = True
Range(“T1”) = “rel5”
Range(“T1”).Font.Bold = True
Range(“U1”) = “rel5working”
Range(“U1”).Font.Bold = True
Range(“V1”) = “rel5why”
Range(“V1”).Font.Bold = True
Range(“W1”) = “rel6”
Range(“W1”).Font.Bold = True
Range(“X1”) = “rel6working”
Range(“X1”).Font.Bold = True
Range(“Y1”) = “rel6why”
Range(“Y1”).Font.Bold = True
Range(“Z1”) = “forward”
Range(“Z1”).Font.Bold = True
Range(“AA1”) = “confidence”
Range(“AA1”).Font.Bold = True
Range(“AB1”) = “external”
Range(“AB1”).Font.Bold = True
Range(“AC1”) = “confidence”
Range(“AC1”).Font.Bold = True
Range(“AD1”) = “change
Range(“AD1”).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
I’m using the O365 version of word. I get “run-time error 1004” and “method ‘Range” of object ‘_Global’ failed. Can you please tell me how to fix this?
Thanks!
Dear Mr Takyar
Thank you for the informative videos on youtube. I have tried to complete the data importation function “get Sub getWordFormData()” but when I run the script i get an error mesaage “Compile error User-defined type not defined” with ”
Dim wdApp As New Word.Application” highlighted.
Dear Mr Takyar
The above video is really helpful. I want to explore a scenario where instead of content control i want to extract normal text in a paragraph in between the [ ] brackets and store it in a excel in single column one below the other. If are any such videos for the scenario kindly share. Thanks in advance.