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

12 thoughts on “Get Data from MS Word Forms Automatically into Excel

  1. Jon

    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.

    Reply
      1. Erin

        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

        Reply
  2. Pingback: Get Data from Microsoft Word Forms into Excel Using VBA

  3. Joram

    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

    Reply
  4. Jacob

    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

    Reply
  5. s

    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?

    Reply
  6. Chris

    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

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *