February 23, 2015

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

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

  1. 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.

      1. 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

  2. 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

      1. 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

  3. 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

  4. 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?

  5. 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

  6. 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.

  7. Hi Dinesh Kumar Takyar,

    I read the thread mentioning download the sample file, but i could not get it. Could you enlighten me?

  8. 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!

  9. 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.

  10. 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?

  11. 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!

  12. 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.

  13. 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.

Comments are closed.