Get Data from Microsoft Word Forms into Excel Using VBA 

Very Good Dinesh. Thank you. You are a very clear communicator and teacher. Thank you for the video.

You saved me Thank you so much for detailed explanation of each step.

Fantasic video, well explained. Thank you!

Dinesh, Very well explained in a simple way at the right pace. Especially for Indians your accent & voice is right. I have been watching your other videos as well. Thanks

Great tutorial. Any pointers on doing this when your form contains check boxes?

I am using Word and Excel 2016, I did everything as you had it, but I receive a "Compile error: User-defined type not defined" message. The yellow arrow highlights.... Sub getWordFormData ()The following text is highlighted in blue...Dim wdApp As New Word.ApplicationCould you help me figure this out?

very clever exactly what I need to create I will try it tomorrow will save me days of work capturing data from job application documents

This great. I use Word forms all the time and the most frequently asked question is can you input the data you collect into a spreadsheet so we can do some statistical analysis. The only way I could do it before was by inporting each Word file's data into an Excel file manually then concatenate the files into a single excel file. This was tedious. This program does all the heavy lifting. Thank you. Thank you. Thank you.

Dinesh, great video. Another shortcut I have learned when referring to a folder path. Use the environ function. For example: myFolder= environ("UserProfile") & "\MyForms\" would return the name of whomever the current user logged in is. Give it a try.

Thank you sir.

Hi, thanks for your training videos. I have learned many things from you. Here in this video you have shown objIE as New InternetExplorer, this property 8s not visible in my excel form. How can I get it?

Sir i have a problem. after the declaring the CCtl I still have the same error "Method or data member not found" please help. btw im using ms office 2010

Great tutorial, thank you! But is there a reverse process: i.e. where excel file feeds the word document? You will have to have name ranges and the values of the name ranges need to be transferred to a word document's place holders: content controls. Please advise.

I am running into an issue where a form that has dropdown lists and text has the dropdown list values pulling in twice into the excel form. Is there a specific part of the code that needs to distinguish between a text content control and a dropdown list content control and how would I prevent a numerical value from a dropdown list field pulling in twice? For example if I have a dropdown list content control titled "Age" and the entry is 30, it pulls into excel as 30 in one column, and 30 in the next so all of the consequent data is shifted by a column.

Excellent contribution, you saved my job, but if there are some check box and radio botton in my form, and I want appear in excel file 1 if is activated or 0 if is inactivated, how I can integrate it into the macro?, thanks. greetings from México.

Indeed very useful Sir. Thanks a ton!

Thank You sir for sharing knowledge.. I have a word document which has data in text boxes (not in forms). How can i import that data into excel sheet using vba

Dinesh, I would like to do the same from a Word Document to another Word Document (With a different format). Would this run with the same type of code in Word? Best Regards JQ

Hi, This is fantastic, and I've been able to get the code to run. But have now realised that the information I would like to export is not in "content control" form, but rather a "field". Can this code be tweaked to grab the data from the fields? Or is a completely different code? Thanks,

yes sir i did that, 3 times now i even copy it and saved as plain text, I now think my mistake is that this works with FORM DATA and not just data in word.if thats the case any vba to pull data to exceland continue to add data to the next row as long as the data is in myforms, better yet can it be done with pdf data?

Legend you are!

I have watched your video and tried on my own but there’s an error that says object variable or with block variable not set. The error triggered after the line of Active.Sheet.Cell.Clear. Idk how to fix it.

Dinesh, great video. Question: If text in a content control has non text characters, such as bullet points, bold and carriage returns, how can formatting be carried over into excel? In this example, all formatting and other non-text characters are lost because a pure string is written back to the cell. Is there a way to adjust to VBA so formatting isn't lost when going from Word to Excel?

How can the script be changed to insert the data into a single column instead of the single row in your example?

Thank you for your videos I have learned a lot from you. Although I am having a problem with the VBA in this one, when I run the module it stops at myFolder = “C:\Users on\MyForms” I get a VBA Compile error: syntax error Tried a number of things with regard to the path with no success. Can you or others here help? Thank you, Ron

Hello, thank you very much for the tutorial. I am wondering, do the heading in my Word folder have to match the Heading we define in the Excel Folder in order for the program to work?

Dear Mr. Takyar, using your helpful video, I created the form I needed. But now I have to replicate for Microsoft Word 2003 as this is what they use at work. Any suggestions as to how to tweak the form and VBA code? Thanks Firas

Hello, Mr. Kumar. I come to thank you for sharing this video. It helped me on my work. I would like to note that when we are checking if the folder is empty (and when it was), Excel would freeze and give me the error "Microsoft Excel is waiting for another application to complete an OLE action." I would then have to call the task manager to be able to close Excel. I solved it by using the following code: Can you send the Note one ms.word or any Ida plz

Dear Dinesh what an excellent video tutorial! I have over 1800 word documents which are locked forms and also mail-merge documents that need moving to a new environment and hence i have to attach a new datasource which of course re-sets all form fields to their default value and loses the stored data. But with your macro (modified for formfields) I can safely store all the information (and also inserted the filename in column A). Now I just need to create another macro to put it back in to each document once the new mail-merge datasource is attached! I don't suppose you have one ready for me do you 🤣

Hi, newbie here. I would just like to know to how create button and connect it to vba?

thank you very much about this lesson. did you make a lesson vise versa from Excel to Word?

Do you have a video on how to do the opposite as I need to get data from an excel sheet and put it on word to print shipping labels. if you don't have a video can you please show me where I can find more info or teach me how to 1. get data from 1 line in excel into 1 word document and then repeat this for each line in excel creating a new word document for each line. or 2. get data from excel into word template where each line in excel create a new page in work and uses the template again.

After you saved the word form to your folder --- you went to the excel doc where you already had the headings. You mentioned at that point that the headings already have a VBA coding --- can you explain that. When I hit run, I am not getting any data in excel.

Dear Dinesh, THis code is perfect for my project. However, the code won't run and tells me that de foldername is invalid. However, i copied the location from the 'file explorer' so don't know why it is invalid. Can you help me with your experience what could be the problem? Thanks!

Sir i have a problem. after the declaring the CCtl I still have the same error "Method or data member not found" please help. btw im using ms office 2010.. Sir I need an immidiate response. 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. 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.

Can this be done on MS teams? Example, there are Word Doc on MS teams and I would like to set excel to grab data from the Word Doc. How would the set up in VBA look?

When I try to run the code, I get a run time error 91 (Object variable or With block variable not set) on the For Each CCtl In .ContentControls line. I tried using a rich text content control and the plain text content controls, still got the same error. What do I need to check? I copy/pasted the code from the website and adjusted the quotation marks from angled to regular.

Thank you, how do you deal with checkboxes and radiobuttons?

Thank you very much. Can you upload sample word documents so as to apply this lesson?

Hi Sir this video was really helpful but I got into trouble when the order of each category in the form are mixed. What code should I use to define the Values of the first Cells according to the Name of the Content Controls?

I am using a form I created in word with a few check boxes and laid out everything just as you did with your word doc and corresponding excel spreadsheet. Once I get to the section of code: Set myDoc = wdApp.Documents.Open(Filename:=myFolder & "\" & strFile, AddToRecentFiles:=False, Visible:=False) VB times out and just hangs with no way for me to reset. I am trying to just pull form data into the spreadsheet exactly like you have instructed in this very informative video. Any further guidance would be greatly appreciated.

hi sir need some help , can i capture some details from mail . i have to complete a excel sheet every day . i updated them manually but i saw your videos . lot's of people send CV's to me and i want to update their name and some details to excel ,can i automated this using VB please reply sir??

Hallo Dinesh, I have a word document from where not all data should uploaded to an excel file. I first tried with OLElinks but these are breaking when using both word and excel templates. Your video refers to only word documents with columns. Is there an option / code available to copy only specific data from word to specific and different field areas in excel? kind regards, Jan

do you know how to get Data from Microsoft Word Forms into Access Using VBA?

Hello Professor Dinesh, First and for all, i would like to thank you for this lesson! Because i use it so much, i wonder if there was a possibility that, instead of given a fix filepath, there is a possibility that vba prompts me to choose a path in the file explorer before extracting the data Thanks in advance. Greetings, El Founti Mohamed

Thanks for the tutorial. What if someone didn't know how to write the VBA code

is there any VBA video for excel to word using content control

Hi there, I watched your video to get data from Word forms in Excel using vba. My word document has formfields that contain the data I want, and I only want two of the fields, not all of them. I have tried to play with your code but am stuck and was hoping you can help. Here is what I originally started with: The code I wrote, in Excel, is pulling just one form currently. I would like for it to pull every file that I have saved in a particular destination. Sub wrd11() Dim wrd As Word.Application Set wrd = CreateObject("Word.Application") With wrd .Documents.Open "C:\Users\dculnane\Documents\Performance Evaluations\Working forms\PE Manager Form.docx" .Visible = True End With Range("a2").Value = wrd.ActiveDocument.FormFields("Text8").Result Range("b2").Value = wrd.ActiveDocument.FormFields("Text20").Result wrd.Quit End Sub I found your video, but I can’t figure out where to change my code. this works, but only pulls from one file. I need to use formfields and not content controls. Here is what I have so far: Sub getWordFormData() Dim wdApp As New Word.Application Dim myDoc As Word.Document Dim FFtl As Word.FormFields Dim myFolder As String, strFile As String Dim myWkSht As Worksheet, i As Long, j As Long myFolder = "C:\temp\test" Application.ScreenUpdating = False If myFolder = "" Then Exit Sub Set myWkSht = ActiveSheet ActiveSheet.Cells.Clear Range("A1") = "Employee Name" Range("A1").Font.Bold = True Range("B1") = "Total" Range("B1").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 FFtl In .FormFields j = j + 1 myWkSht.Cells(i, j) = FFtl.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 Thank you for your help.

Hello Dinesh Sir, I really liked your video. Could you please tell me how do i capture the word document radio button vales in my excel sheet. e.g. I have a question and the answer is three radio buttons with different age groups I will select one radio button and i want to understand how you capture that through VBA code Thanks Poonam

Thank you for your video and i have copy the exact VBA code by change the myFolder directory only, but i got into error Sub getWordFormData() Dim wdApp As New Word.Application -> User define type not define, i could not tell what wrong with it, can advise?

Another tip. Instead of setting bold after each value in your header. Use With/End With. For example. After you write the headers in row 1. Type With range("a1:l1") .font.bold=true End With This will bold all values and save a lot of typing.

Is it like a reverse..mail merge???

Hello Sir. My code is on my desktop. Could it be what is causing the problem? Please suggest where I should place it.

Thanks, For me after the third field the values are repeating. i.e the fourth cell has 3rd cell value .Fifth cell has 4t cell value and so on. please suggest.

I write the code same as you but while running it shows user defined type not defined.Why sir?

This is really what i need but im having trouble geting it to work I get run time error 1004 application define or object define error, can you please help

Hi Dinesh. Thanks for the wonderful video. I have a scenario where i have to read all the word docx from folder in which i have to fetch the text inside [ ] bracket and store in a excel in a single column one below the other. Can you help? The below is my current code which extracts the words in another word doc. Sub ExtractText() Dim cDoc As Word.Document, nDoc As Word.Document Dim cRng As Word.Range, nRng As Word.Range Set cDoc = ActiveDocument Set nDoc = Documents.Add Set cRng = cDoc.Content Set nRng = nDoc.Content cRng.Find.ClearFormatting With cRng.Find .Forward = True .Text = "[" .Wrap = wdFindStop .Execute Do While .Found cRng.Collapse Word.WdCollapseDirection.wdCollapseEnd cRng.MoveEndUntil Cset:="]", Count:=Word.wdForward nRng.FormattedText = cRng.FormattedText nRng.InsertParagraphAfter nRng.Collapse Word.WdCollapseDirection.wdCollapseEnd cRng.Collapse Word.WdCollapseDirection.wdCollapseEnd .Execute Loop End With End Sub

Hello, Mr. Kumar. I come to thank you for sharing this video. It helped me on my work. I would like to note that when we are checking if the folder is empty (and when it was), Excel would freeze and give me the error "Microsoft Excel is waiting for another application to complete an OLE action." I would then have to call the task manager to be able to close Excel. I solved it by using the following code: If Dir(myFolder & "\" & "*.*") = "" Then Application.ScreenUpdating = True MsgBox "The folder is empty." Exit Sub End If

Hello Sir, Thank you for the video. I have a question I am unable to solve with Google, so hope you can help. When I run the code, I get the error message, "User-defined type not defined". Here is 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 = "K:\fschauni01\group_share\OneBusey\_Relationship_Reviews\Relationship Review and Prospect Initiative Tracking\Archive\Forms" Application.ScreenUpdating = False If myFolder = "" Then Exit Sub Set myWkSht = ActiveSheet ActiveSheet.Cells.Clear Range("A1") = "Business Name" Range("A1").Font.Bold = True Range("B1") = "Contact" Range("B1").Font.Bold = True Range("c1") = "RM" Range("c1").Font.Bold = True Range("d1") = "LOB" Range("d1").Font.Bold = True Range("E1") = "NotesDescriptionWealth Management" Range("E1").Font.Bold = True Range("F1") = "NotesDescriptionPrivate Client" Range("F1").Font.Bold = True Range("G1") = "NotesDescriptionCash Management" Range("G1").Font.Bold = True Range("H1") = "NotesDescriptionDepository Services" Range("H1").Font.Bold = True Range("I1") = "NotesDescriptionCommercial Lending" Range("I1").Font.Bold = True Range("J1") = "LOB_2" Range("J1").Font.Bold = True Range("K1") = "NotesDescription" Range("K1").Font.Bold = True Range("L1") = "Where held" Range("L1").Font.Bold = True Range("M1") = "Private Client" Range("M1").Font.Bold = True Range("N1") = "Click here to enter text" Range("N1").Font.Bold = True Range("O1") = "Date" Range("O1").Font.Bold = True Range("P1") = "Notes" Range("P1").Font.Bold = True Range("Q1") = "Click here to enter text_2" Range("Q1").Font.Bold = True Range("R1") = "Action Item" Range("R1").Font.Bold = True Range("S1") = "Responsible Party" Range("S1").Font.Bold = True Range("T1") = "Status" Range("T1").Font.Bold = True Range("U1") = "Date_2" Range("U1").Font.Bold = True Range("V1") = "Click here to enter text_3" Range("V1").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 Do you know what is causing the error? I've compared to the code line by line and I don't see it. Many thanks! Erin

Hello. How can I get data from Microsoft word textboxes into Excel using vba?

Hello Dinesh. I have used your macro but am having issues with the data not being transferred from the Word document to Excel. After running the macro, I only receive the bolded headers and none of the data. Help!

How to make a userform browser in excel form word data. Can you make? No. Great Video , I will make UserForm of this

Hello Sir. I find your video very interesting. However, i have an issue with mine. I must say, that am a newbie with programming in Excel. When I run the code, I get the error message, "User-defined type not defined". Please can you assist here

Which version of ms Excel is this?

compile error: User-defined type not defined

Instead of word form, how data can be imported from word table Sir. Please make a video.