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.

2. Why does mine only extract data to row m?

3. I get a compile error on the first list.

   1. Fixed that issue. How can I convert a checkbox in my Word Form to text?

      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

         1. Same here!
            I also downloaded the sample file, didn’t help

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

5. 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. Just download the sample file at https://www.exceltrainingvideos.com/tag/get-data-from-microsoft-word-forms-into-excel/

      1. I am getting an error in the first line itself. please help.

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

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

   1. Just do a search at https://www.exceltrainingvideos.com/

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

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

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

10. Hi Dinesh Kumar Takyar,

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

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

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

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

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

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

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