Get Data from Microsoft Word Forms into Excel Using VBA

[@SamvadSoul]

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

[@mjpender9443]

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

[@aarontcuthbertson]

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

[@alisherniza]

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.

[@vsali]

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

[@wendells.williams7060]

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.

[@ritabartlett5155]

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

[@MynaMiranda]

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.

[@pierrickdefossez3157]

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

[@Exceltrainingvideos]

This link will guide: www.exceltrainingvideos.com/get-data-from-ms-word-forms-automatically-into-excel/

[@kylefreedman1424]

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.

[@HLWizard_GirlWizard]

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.

[@srinivasannarayanaswami5378]

Indeed very useful Sir. Thanks a ton!

[@Exceltrainingvideos]

Glad it was helpful! Please share my KZbin channel with all your friends.

[@michellearnon3931]

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

[@clares5798]

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,

[@ivanneshkov9363]

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?

[@999_pan]

Looking at the vba code it does not seem to need to match. The word extract starts with "For each CCtl In .ContentControls" This means it will go through each control in turn and return the data - if you have 10 form items in your word document then it will return 10 columns of data in the excel workbook, irregardless of whether you have opted to enter 10 headings in the workbook

[@microsoftdestination6249]

@@999_pan Dear Al E Ivan Neshkov , you have replied to him? no or yes sir!

[@RatneshVermaRtn21]

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?

[@dr2man]

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

[@eeimg]

I am facing the same issue. After i run it, compile error appear.

[@rebeccafinneyfernandes7056]

I am also having a similar problem. the header comes in bold but not the actual data from the forms. what could be the issue?

[@Joelee-mq5je]

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

[@krn14242]

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.

[@Exceltrainingvideos]

krn14242 myFolder= environ("UserProfile") & "\myForms" - Super!

[@adrianleong5848]

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?

[@Exceltrainingvideos]

Will work on this idea. I haven't used MS Teams.

[@jansleeboom2193]

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

[@Exceltrainingvideos]

This link will guide: kzbin.info/www/bejne/iaXLnGSGr8uHaq8

[@officekundig2018]

@@Exceltrainingvideos dear Dinesh, thanks for replying an answer that quick. However this is not what I am looking for. In your example the search criteria are all in one column and the results pasted in the second sheet in record format. I have a Word form (template) used for a quotation. Figures and the address information need to be copied over to specific fields in the invoice in a Excel template. I tried to do so via OLElinks but these are breaking when opening the document for the second time. Also when renaming the final quotation and invoice documents breaks the link. I hope you understand my issue and yes I know there nice application s for accounting but my client is stubbornly and want to keep this way of working -;) I hope you can help. Kind regards, Jan

[@garyearls5352]

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?

[@OpenMeUpTec]

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.

[@Exceltrainingvideos]

+OPEN ME UP Have a look at these links and also do a search on my website: www.exceltrainingvideos.com/import-data-into-word-from-excel-using-vba/ www.exceltrainingvideos.com/create-mailing-labels-in-ms-word-using-excel-named-range/ www.exceltrainingvideos.com/using-excel-data-for-mail-merge-in-word/

[@OpenMeUpTec]

thanks for your help I will look into and see how I go.

[@AD4605]

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

[@Exceltrainingvideos]

www.exceltrainingvideos.com/import-data-into-word-from-excel-using-vba/

[@masson426]

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.

[@Exceltrainingvideos]

Can you share your code here?

[@cddolman]

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 🤣

[@Exceltrainingvideos]

How about: kzbin.info/www/bejne/rnnamqiirZeAh8U

[@philipbroadbent9333]

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?

[@jackieoneal2180]

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.

[@femmiehh]

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!

[@nikunjgattani999]

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

[@jaimeq90]

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

[@MrMohanchem]

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

[@Exceltrainingvideos]

No

[@Chris-uw4nb]

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.

[@Exceltrainingvideos]

This link will help: www.exceltrainingvideos.com/get-data-from-ms-word-forms-automatically-into-excel/

[@jareddevilla4018]

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

[@ushabtieng]

Fantasic video, well explained. Thank you!

[@512Riddler]

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.

[@grimordwow]

Ctrl + Break won't kill the macro running? If it doesn't there may be something dying on your OS\Excel. Have you tried creating a break point on that line and check the values on your variables to see if everything is as it should? Also, did you add the extension to your strfile variable (.xls\.xlsx, etc) ?

[@999_pan]

in the vbe use F8 to move through each line of code in turn to check out where the time is being taken up

[@khoachu220]

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?

[@Exceltrainingvideos]

Try again

[@shwetaagarwal9487]

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.

[@Exceltrainingvideos]

Can you share your code or file?

[@shwetaagarwal9487]

@@Exceltrainingvideos - Code Sub getNewWordFormData() Dim wdApp As New Word.Application Dim myDoc As Word.Document Dim CCtl As Word.ContentControl Dim myFolder As String, strFile As String Dim myWorkSheet As Worksheet, i As Long, j As Long myFolder = "C:\Users\shwetaa\Desktop\Shweta\automation" Application.ScreenUpdating = False If myFolder = "" Then Exit Sub Set myWorkSheet = ActiveSheet ActiveSheet.Cells.Clear Range("A1") = "NAME" Range("A1").Font.Bold = True Range("B1") = "COMPANY" Range("B1").Font.Bold = True Range("C1") = "DEPARTMENT" Range("C1").Font.Bold = True Range("D1") = "LOCATION" Range("D1").Font.Bold = True Range("E1") = "JOINING DATE" Range("E1").Font.Bold = True i = myWorkSheet.Cells(myWorkSheet.Rows.Count, 1).End(xlUp).Row strFile = Dir(myFolder & "\Form*.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 myWorkSheet.Cells(i, j) = CCtl.Range.Text Next myWorkSheet.Columns.AutoFit End With myDoc.Close SaveChanges:=False strFile = Dir() Wend wdApp.Quit Set myDoc = Nothing Set wdApp = Nothing Set myWorkSheet = Nothing Application.ScreenUpdating = True End Sub

[@shobhakharbhusal6063]

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

[@gihankaushalya6885]

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

[@killerkt24]

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!

[@Exceltrainingvideos]

Check your code again line by line. And if you copied and pasted the code from my website, then chances are it won't work.

[@killerkt24]

Thank you for you reply. Below is the code I am using. I am also using form fields and not content control so I believe I have made the correct changes. Any advice on why none of the data is showing up in the Excel file? Sub getWordFormData() Dim wdApp As New Word.Application Dim myDoc As Word.Document Dim FFtl As Word.FormField Dim myFolder As String, strFile As String Dim myWkSht As Worksheet, i As Long, j As Long myFolder = "C:\MyForms" Application.ScreenUpdating = False If myFolder = "" Then Exit Sub Set myWkSht = ActiveSheet ActiveSheet.Cells.Clear Range("A1") = "Proposal Tracking Number" Range("A1").Font.Bold = True Range("B1") = "Proposal Title" Range("B1").Font.Bold = True Range("C1") = "Principal Investigator" Range("C1").Font.Bold = True Range("D1") = "Reviewer" Range("D1").Font.Bold = True i = myWkSht.Cells(myWkSht.Rows.Count, 1).End(xlUp).Row strFile = Dir(myFolder & "\*.doc", vbNormal) While strFile "" i = i + 1 Set myDoc = wdApp.Documents.Open(Filename:=myFolder & "\" & strFile, AddToRecentFiles:=False, Visible:=False) With myDoc j = 0 For Each FmField In .FormFields j = j + 1 myWkSht.Cells(i, j) = FmFld.Result 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

[@Exceltrainingvideos]

What is FmFld?

[@killerkt24]

It stands for "Form Field". Since I used form fields in my document instead of content control, I replaced it in the code. This was recommended by a thread connected to this video.

[@Exceltrainingvideos]

Sub getWordFormData() Dim wdApp As New Word.Application Dim myDoc As Word.Document Dim FmFld As Word.FormField Dim myFolder As String, strFile As String Dim myWkSht As Worksheet, i As Long, j As Long myFolder = "C:\MyForms" Application.ScreenUpdating = False If myFolder = "" Then Exit Sub Set myWkSht = ActiveSheet ActiveSheet.Cells.Clear Range("A1") = "Proposal Tracking Number" Range("A1").Font.Bold = True Range("B1") = "Proposal Title" Range("B1").Font.Bold = True Range("C1") = "Principal Investigator" Range("C1").Font.Bold = True Range("D1") = "Reviewer" Range("D1").Font.Bold = True i = myWkSht.Cells(myWkSht.Rows.Count, 1).End(xlUp).Row strFile = Dir(myFolder & "\*.doc", vbNormal) While strFile "" i = i + 1 Set myDoc = wdApp.Documents.Open(Filename:=myFolder & "\" & strFile, AddToRecentFiles:=False, Visible:=False) With myDoc j = 0 For Each FmFld In .FormFields j = j + 1 myWkSht.Cells(i, j) = FmFld.Range 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 Note: Ensure that your data is in form fields. You can test by right-clicking the Text Form Field and inputting a default value in the window that pops up.

[@KhalilYasser]

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

[@Exceltrainingvideos]

You are welcome. This link will help: www.exceltrainingvideos.com/get-data-from-ms-word-forms-automatically-into-excel/ In case your problem is still not solved, let me know.

[@OnionReels]

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

[@Exceltrainingvideos]

+Charles Anah No.

[@mohamedelfounti4484]

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

[@Exceltrainingvideos]

Use an inputbox: www.exceltrainingvideos.com/copy-paste-data-from-one-excel-worksheet-to-another-using-an-inputbox/

[@mohamedelfounti4484]

@@Exceltrainingvideos Hello Professor Dinesh, Thank you for your reaction! But what i ment, was how can I get specific data from a specific word document without defining a fix filepath or kind of form. So if i look for e.g. a word , Vba should look thrue al the word documents in a specific folder and extract the whole paragraph,title an sentence. I want to make a an encyclopedia that excists from multiple word documents. Kind regards,

[@saidif11]

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

[@anushasalian4867]

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

[@Exceltrainingvideos]

Did you take a reference to the Microsoft Word Object Library? Watch the video again starting at 7:16 min.

[@masterkey603]

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

[@Exceltrainingvideos]

We can say so.

[@jareddevilla4018]

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

[@vigneshr2828]

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

[@Exceltrainingvideos]

Your Word macro is perfect. Will work to get the data into Excel. In the meantime have a look at my earlier reply with link to relevant VBA tutorials.

[@vigneshr2828]

@@Exceltrainingvideos thanq so much Dinesh..

[@vigneshr2828]

@@Exceltrainingvideos Eagerly waiting for your video on this scenario :) Thanks

[@moseskadogoli2257]

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

[@Exceltrainingvideos]

Good question! We can use copy/paste but the process can be quite time consuming in many cases.

[@poonamsachdeva9172]

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

[@Exceltrainingvideos]

This link will guide: www.exceltrainingvideos.com/design-user-form-with-listbox-labels-textboxes-optionbuttons-command-button/

[@OnionReels]

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

[@Exceltrainingvideos]

+Charles Anah Where's your code?

[@juniordiverkat1825]

I have the same problem as Charles Anah. Here is my code below: 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 = "S:\Quality\Quality Systems Manual" Application.ScreenUpdating = False If myFolder = "" Then Exit Sub Set myWkSht = ActiveSheet ActiveSheet.Cells.Clear Range("A1") = "Document Name" Range("a1").Font.Bold = True Range("B1") = "Issue Date" Range("B1").Font.Bold = True Range("C1") = "Supersedes" Range("C1").Font.Bold = True Range("D1") = "Last Revision Date" Range("D1").Font.Bold = True Range("E1") = "Last Revision Description" Range("E1").Font.Bold = True i = myWkSht.Cells(myWkSht.Rows.Count, 1).End(xlUp).Row strFile = Dir(myFolder & "\*.doc", 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

[@rayquant5279]

Did you turn on the necessary references? In the top menu bar, click Tools > References. There are a few default ones selected, which is fine, but you may need to scroll down to "Microsoft Word 15.0 Object Library" and select that. Double check the other ones that the instructor has selected at 7:14.

[@yo_swarnali]

Which version of ms Excel is this?

[@Exceltrainingvideos]

Excel 2013

[@yo_swarnali]

@@Exceltrainingvideos Sir are you online now? I'm implementing your code for my VBA project. I'm facing one problem, can you please help me out? I wrote your code but no output is showing in the spreadsheet except the column headings..

[@RC-ql5lp]

@@yo_swarnali 1) Check your Word source folder to make sure you have .docx files exist there. 2) data existing in these .docx files

[@msl8dycc251]

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

[@Exceltrainingvideos]

This link will help: www.exceltrainingvideos.com/tag/get-data-from-microsoft-word-forms-into-excel/

[@msl8dycc251]

Thank you! Also, is it possible to extract both content controls and form fields at the same time? Or extract form field data into specific cells?

[@pssoh71]

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?

[@Exceltrainingvideos]

Check out this link: www.exceltrainingvideos.com/tag/get-data-from-microsoft-word-forms-into-excel/ You also get a sample file which can be downloaded.

[@pssoh71]

Dinesh Kumar Takyar : I try to download the sample file but no response

[@pssoh71]

Thank you Sir.

[@kumi3621]

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

[@garyearls5352]

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

[@Exceltrainingvideos]

+Gary Earls You can the complete VBA code and a sample file here: www.exceltrainingvideos.com/tag/get-data-from-microsoft-word-forms-into-excel/

[@beila00]

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.

[@houstonvihaan7972]

Instablaster.

[@krn14242]

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.

[@Exceltrainingvideos]

krn14242 Lovely!

[@kenyeresgellert]

Legend you are!

[@Exceltrainingvideos]

Thank you! Please share the Excel VBA tutorials with your friends.

[@McKeeEM]

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

[@rayquant5279]

Did you turn on the necessary references? In the top menu bar, click Tools > References. There are a few default ones selected, which is fine, but you may need to scroll down to "Microsoft Word 15.0 Object Library" and select that. Double check the other ones that the instructor has selected at 7:14

[@bricc322]

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

[@Exceltrainingvideos]

Thanks for sharing!

[@masson426]

@Celia Alves , where exactly you change or add this in the code as I am having a same error and I am not good in vba. thanks is advance for your help.

[@masson426]

@@Exceltrainingvideos Hi, where exactly you change or add this in the code as I am having a same error and I am not good in vba. thanks is advance for your help.

[@MrSandeep1987]

Thank you sir.

[@kmjha2413]

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

[@Exceltrainingvideos]

These links will help: www.exceltrainingvideos.com/extract-data-from-multiple-tables-in-web-page-with-vba/ www.exceltrainingvideos.com/how-to-import-data-from-word-tables-into-excel-with-vba/ You can also search my KZbin channel or website: www.exceltrainingvideos.com/

[@kmjha2413]

Thanks Sir. I went through your video but my problem is different. I have to put the data of all word doc having only one table of two columns. I want to import data of only second column in excel sheet under the column with header name of word table column one.

[@microsoftdestination6249]

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

[@Exceltrainingvideos]

Yes, sure

[@artbelief777]

compile error: User-defined type not defined

[@Exceltrainingvideos]

Did you activate the Microsoft Word Object Library from Tools --> Refrences? This link will guide: www.exceltrainingvideos.com/compare-2-worksheets-to-create-report/ Watch the video carefully 7:15 minutes onwards to see how the Microsoft Word Object Library is selected.

[@alexandracormier4023]

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?