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