PDA

View Full Version : Solved: Excel Getting Data from Word Form



griffism
11-02-2007, 02:47 PM
Ok. I've been banging my head against this code for too long. :banghead:
I don't know how close I am to getting it working, but I need a break. Maybe you can help.

I'm pretty new to VBA, so go easy on the lingo, please. (Thanks.)

I'm trying to pull results from multiple Word Forms into one Excel Spreadsheet.

The latest error I'm getting is 462, "The remote server machine does not exist or is unavailable." My guess is this is having a hard time finding the workbook (which is why I added myWB, but that didn't work either).

Anyway, here's the code:


Sub GetData()
Dim wdApp As Word.Application
Dim wdDoc As Word.Document
Dim myWB As Workbook
Dim fs As FileSearch
Dim fName As String
Dim i As Long, j As Long, k As Long, l As Long, fileCount As Long
Dim f

On Error GoTo Exits
Set myWB = ActiveWorkbook
Set wdApp = CreateObject("Word.Application")

i = 0
j = 2
k = 1
l = 1

Set fs = Word.Application.FileSearch

fs.NewSearch
fs.FileType = msoFileTypeWordDocuments
fs.LookIn = "C:\Documents and Settings\wn596c\My Documents\ULA\Survey\MMP Results\"
fs.SearchSubFolders = False
fs.Execute

fileCount = fs.FoundFiles.Count

For k = 1 To fileCount
fName = fs.FoundFiles(k)

If fileCount < k Then
MsgBox "Cannot find any files."
End If

Set wdDoc = wdApp.Documents.Open(fName)

wdApp.Visible = False

For Each f In wdDoc.FormFields
i = i + 1
myWB.Cells(i, j) = f.Result
Next

i = 0
j = j + 1

wdApp.Documents.Close
Next

Exits:
MsgBox "You did something wrong! " & Err.Number & " - " & Err.Description

wdApp.Quit
Set wdApp = Nothing
Set wdDoc = Nothing
Set fs = Nothing
End Sub


Edited 10-Nov-07 by geekgirlau. Reason: insert vba tags

griffism
11-02-2007, 04:04 PM
Never mind I got it working!

geekgirlau
11-09-2007, 02:41 PM
FYI, Word allows you to save forms data in text format, which eliminates the need to loop through the fields:


With ActiveDocument
.SaveFormsData = True

.SaveAs FileName:=gstr_PATH & "MYDATA.txt", FileFormat:= _
wdFormatText, SaveFormsData:=True, LineEnding:=wdCRLF
End With


By the way, when you post code, please use the vba tags - if you select the text and click on the "VBA" button, your code is easier to read (I've edited your post to do this for you).

Please post your final code when you have a chance - someone else may benefit from it in the future. Oh, and welcome to the Board :hi: