MS Word form data to Excel 2010

**thereallukeg** · 04-11-2012, 03:55 PM

I am new to macros or any kind of programing and found some help in another thread you guys had HERE

I still cant get it right. I want to automate a process that i can send a fillable word document to 100 different people and at the end of the form it sends the form to me, and takes the data from the word doc into txt, and puts it in my excel document.

right now, I have it where it will send me the Word document, I run a macro, and it will save the txt document form answers, I have to then open up Excel and run another macro to input the data....

if the excel document is accessible on a shared drive for all 100 people, i would think there should be an easy way to automate getting the Word data into my excel sheet.

Both the forms are attached for any help i can get.

**MedicalServices** · 04-11-2012, 04:52 PM

I think you should be able to use the 'Scripting.FileSystemObject' and open the excel file on the shared drive and write the data directly to the workbook. You could add this to your CommandButton2 Sub to run immediately after the save action.

**thereallukeg** · 04-11-2012, 04:59 PM

forgot the word doc was locked, password: luke

The only thing i have in the word code is:

Private Sub CommandButton1_Click()
ActiveDocument.SendForReview _
Recipients:="[email protected]", _
Subject:="Maintenance Work Order", _
ShowMessage:=True, _
IncludeAttachment:=True
End Sub

i should just put:
Scripting.FileSystemObject

after that?

I have a script that will open the document in excel and the document:
Shell ("C:\Program Files\Microsoft Office\Office14\EXCEL.EXE ""S:\PLANT\MAINT\WORK ORDER LIST.xlsx""")

but i dont want the person filling out the document to SEE all that is going on behind the scenes.

When i get the word document, I open it up and run the small button macro on the bottom:
ActiveDocument.SaveFormsData = True
ChangeFileOpenDirectory "S:\PLANT\MAINT\WorkOrderData\"
ActiveDocument.SaveAs2 FileName:= _
"S:\PLANT\MAINT\WorkOrderData\Maintenance Work Order Info.txt", _
FileFormat:=wdFormatText, LockComments:=False, Password:="", _
AddToRecentFiles:=True, WritePassword:="", ReadOnlyRecommended:=False, _
EmbedTrueTypeFonts:=False, SaveNativePictureFormat:=False, SaveFormsData _
:=True, SaveAsAOCELetter:=False, Encoding:=1252, InsertLineBreaks:=False, _
AllowSubstitutions:=False, LineEnding:=wdCRLF, CompatibilityMode:=0

this will save the file as a .txt file for me to upload to excel.

Ending goal is this, all done without having to run 3 different/separate macros
- Form filled out (word)
- data saved to .txt file (word)
- data uploaded to top line of excel document

**MedicalServices** · 04-11-2012, 05:21 PM

I see
Actually I think I was hasty in suggesting the FilesSystemObject
You can Dim an Object and set it to create a new instance of Excel (with .visible-False so they don't see what going on behind the curtain) and then open the workbook and set your range values directly from the Word document.

Please Login or Register  to view this content.

**thereallukeg** · 04-11-2012, 06:05 PM

Originally Posted by MedicalServices

I see
Actually I think I was hasty in suggesting the FilesSystemObject
You can Dim an Object and set it to create a new instance of Excel (with .visible-False so they don't see what going on behind the curtain) and then open the workbook and set your range values directly from the Word document.

Please Login or Register  to view this content.

thanks for the help, I just put together all of this, and it is now hung up on the part that says "Rows("2:2").Select"

do I need to do something to each part i copied from excel to work in the background for it to know it needs to be done in excel?

Please Login or Register  to view this content.

**thereallukeg** · 04-12-2012, 10:19 AM

Please Login or Register  to view this content.

found this, I have tried to have word run my excel macro, but does not open excel, and run it

**MedicalServices** · 04-12-2012, 10:27 AM

Have you tried importing your module from the excel document and calling the procedure? You will probably have to pass it some variables that would normally be intrinsic when run from Excel, but I think it is a good place to start.

do I need to do something to each part i copied from excel to work in the background for it to know it needs to be done in excel?

You shouldn't have to do anything extra to tell the macro that you are working in Excel...you already have a variable set to hte instance of the worbook when you opened it.
While i havent fiddled around much with importing CSV files, from the looks of your original attachment, when you save the word doc it is saved in a CSV type of format.
Just of the top of my head, I would probably use the Dim xlFile As Object, xlWkbk As Object to open the workbook, and the FileSystemObject to open the TXT file to read it and write the data to the worksheet.
I will try a couple of optinos and see what I can come up with.

**thereallukeg** · 04-13-2012, 10:09 AM

Thank you, I spent another 3 hours poking around online and through a VBA manual with no luck... will take whatever info to point me in the right direction

**MedicalServices** · 04-13-2012, 10:27 AM

I'm still getting and error that I can;t seem to resolve...
Have you looked into creating an XML schema and exporting it as XML? From that point all you would have to do is import it into your mapped Excel workbook.
I use XML quite a bit once I discovered it--it's also nice because if worse comes to worse you can open it and read it in notepad...

**thereallukeg** · 04-13-2012, 02:13 PM

Installed and tried to run something simple, a few rows and all of my columns, and gets hung up on:

XmlDoc As msxml2.DOMDocument50

I will have to dig more into this later tonight, maybe i am missing something simple on this one. At least I have a new path to go down