# Exporting Word Form Data to Excel



## DougS (Jul 5, 2004)

I am trying to make an database of members for a racing organazation.

Each member has a word document which contains the membership form filled out with Word's Form feature. I know you can do Options > Save > Save Form data only, then save for each file, but i'd like to automate the process and save only form data for all of the documents at once.

Secondly, I'd like to import them into an excel spreadsheet. I've used the data import feature, but once again, the process has to be repeated for every single file. I cannot select more than one. Again, I'd like to autmate it. How?

Better yet would be a method to take all the .doc file's form data straight into excel without going to the middle .txt file.

Thanks for your help,
Doug


----------



## Rollin_Again (Sep 4, 2003)

Can you post a sample member data form? I'll work on a macro to do what you want but I need a sample for coding and for testing purpose.

Rollin


----------



## DougS (Jul 5, 2004)

Sure. Heres a form. 

I just want the form data going side to side.

eg.
Name Date Age 
Driver 01/02 25
Driver1 03/03 33

Except with all the info.

Thanks
Doug


----------



## Rollin_Again (Sep 4, 2003)

I'm almost done with your macro. There are a couple of issues to address:

1. Is there any reason you are choosing to house your data in a spreadsheet instead of a true database? What you are doing is much better suited for Access instead of Excel. If you ever need the data in Excel format later, Access can export its data straight into Excel.

2. If you still want to use Excel to hold your data, do you already have an existing spreadsheet that you are entering your data in? If so, please give me the name of the Excel file and the location of it on your drive. If you can, it would be better to zip the workbook and post it so I can see how you want the data to be arranged. (if you don't have Winzip you can just change the extension of the workbook to *.txt* and attach it as a text file and I'll change it back to *.xls* when I download it.

3. How do you plan on processing multiple documents? Do you want to store all the un-processed documents in one directory and then automatically move them to another after they have been processed? Do you want to store them in one directory and then delete them after being processed? Either way, you need to have a place to hold the unprocessed files and the files need to be moved or deleted after being processed so that the next time you run the code it will not add duplicate data. I hope that makes sense. If you understand what I am talking about, give me the name and locations of the directories that will hold your processed and unprocessed data.

Rollin


----------



## DougS (Jul 5, 2004)

Okay.

1. Sure if i can export it that works fine. I just need to get it to excel for people without access.

2. N/A

3. Just going from one folder to another works. A processed folder and an unprocessed folder is fine. I do need to keep the original word files. 

Just having it scan the unprocessed folder, import it, then move it to processed will be great.

Full locations of the DIRs if you need that:
C:/Documents and Settings/Duane/Desktop/CIKA 2004/Members/Processed/
C:/Documents and Settings/Duane/Desktop/CIKA 2004/Members/UnProcessed/
The DB can go in C:/Documents and Settings/Duane/Desktop/CIKA 2004/Members/

Thanks,
Doug


----------



## Rollin_Again (Sep 4, 2003)

For now, I'll make the macro work with Excel. We can fine tune your app and convert it to Access later since I do not know how experienced you are in using Access. I'll finish coding tommorow so you have something in the meanwhile. Going from Excel to Access is very simple  


Rollin


----------



## DougS (Jul 5, 2004)

Excel is fine really, It doesn't need to be searchable or anything. Just make each form a row and each field a column.

Eg.

---------Field1-----Field2-----Field 3--
Form1--Answer----Answer----Answer--
Form2--Answer----Answer----Answer-- 
Form3--Answer----Answer----Answer--

Thanks
Doug


----------



## Rollin_Again (Sep 4, 2003)

Here is the code you need to automate your process. Follow these steps to implement the code.

1. Create a Blank Excel Workbook Called "Members.xls" and place it in a folder called *Members* within the main *CIKA 2004* folder on your desktop. The full path to the "Members.xls" workbook should be

*"C:/Documents and Settings/Duane/Desktop/CIKA 2004/Members/Members.xls"*

2. Create a 2 folders within the same *CIKA 2004* directory called Processed and UnProcessed. Place all your unprocessed word form documents in the unprocessed folder.

3. Open the newly created "Members.xls" workbook and press *ALT + 11* to bring up the VB editor. On the VB editor 
click *TOOLS --> REFERENCES* and look through the list of items and place checks by the items *"Microsoft Scripting Runtime"* and *"Microsoft Word Object Library"* then click OK. Next on the VB editor click *INSERT --> MODULE* to insert a new module. Copy and paste the code below into the blank module window and then close the editor and re-save the workbook

4. Run the Macro by clicking *TOOLS --> MACRO --> MACROS* and then selecting the macro from the list. Each time you have new word documents to process simply place them in the UnProcessed folder and re-run the macro. They will be moved to the processed folder automatically.

5. After you run the macro for the first time manually add your own column headings to the "Members.xls" workbook. 
NOTE: Some of the columns will contain empty cells due to the fact that some of the form fields on the documents will be blank.


```
Dim vField As FormField
Dim fso As Scripting.FileSystemObject
Dim fsDir As Scripting.Folder
Dim fsFile As Scripting.File
Dim wdApp As Word.Application
Dim myDoc As Word.Document
Dim vColumn As Integer
Dim vLastRow As Integer
Dim x As Integer

Sub AddFormFields()

vLastRow = ActiveSheet.UsedRange.Rows.Count + 1
vColumn = 1

Set fso = New Scripting.FileSystemObject

Set fsDir = fso.GetFolder _
("C:\Documents and Settings\Duane\Desktop\CIKA 2004\Members\UnProcessed")

Set wdApp = New Word.Application
wdApp.Visible = True

For Each fsFile In fsDir.Files

wdApp.Documents.Open (fsFile)

Set myDoc = wdApp.ActiveDocument

For Each vField In wdApp.Documents(myDoc).FormFields

vField.Select

vValue = vField.Result

Workbooks("Members.xls").Activate
Cells(vLastRow, vColumn).Select

If vField.Type = 71 Then

Select Case vField.Name

Case "Check1"
vColumn = vColumn - 1
If vField.Result = "1" Then
ActiveCell.Value = "YES"
End If

Case "Check2"
If vField.Result = "1" Then
ActiveCell.Value = "NO"
End If

End Select

Else

ActiveCell.Value = vValue
End If

vColumn = vColumn + 1

Next

vColumn = 1
vLastRow = vLastRow + 1

x = Split(fsFile, Application.PathSeparator)
vFileName = x(UBound(x))

wdApp.ActiveDocument.Close

Name fsFile As _
"C:\Documents and Settings\Duane\Desktop\CIKA 2004\Members\Processed\" & vFileName

Next

wdApp.Quit
    
    
End Sub
```
Rollin


----------



## DougS (Jul 5, 2004)

Compile Error
Expected Array

UBound was selected in this peice of code:

x = Split(fsFile, Application.PathSeparator)
vFileName = x(UBound(x))


Am I missing something?
Folders are correct.

The only different thing between the instructions and me setup is that Microsoft Word Object Library is actually Called Microsoft Word 11 Object Library

Edit: I got a security message apon exiting, I turned down the security, but it did not solve the error.


----------



## Rollin_Again (Sep 4, 2003)

I am not sure why you are getting that error. When I run the code it works fine.

Anyways, delete the following two lines of code


```
x = Split(fsFile, Application.PathSeparator)
vFileName = x(UBound(x))
```
and replace them with


```
vFileName = wdApp.ActiveDocument.Name
```
Also, what is the exact security message that you were getting when exiting?

Rollin


----------



## DougS (Jul 5, 2004)

It said something about an unsigned macro and told me how to turn down the security to allow it which i did. 

That new code worked great.

Thanks
Doug


----------



## Rollin_Again (Sep 4, 2003)

I'm glad I could help you out :up: 

If you have any problems just lemme know.

Rollin


----------



## shakirdi (Jul 7, 2010)

Hi Rollin,

I read your thread and I am running into the same problem Doug is. My form is in the same format his is and I need a way to import the data from teh Word forms into Access.

Also, I need to Access to store the data right away after the form is filled out. I want the user to only interact with Word. I do not want anything visible to them on the back end.

They will only fill out the Word form and the data will be stored in Access for me to review later on.

I am very challeneged when it comes to programming. Can you please help me do this?

Thanks!

Dina


----------

