fraanchtoast
09-06-2012, 02:01 PM
Hi all,
VBA rookie here. I am trying to write an Excel macro that opens a number of Word documents one-by-one, performs a find loop, and writes the found text to the spreadsheet. My code is throwing a type mismatch error on the line
Set oDoc = Documents.Open(MyFile, Visible:=False)
Any help would be appreciated!!
Sub DetailFinder()
Dim fd As FileDialog
Dim PathOfSelectedFolder As String
Dim SelectedFolder
Dim SelectedFolderTemp
Dim MyPath As FileDialog
Dim fs
Dim ExtraSlash
ExtraSlash = "\"
Dim MyFile As Object
Dim wRng As Word.Range
Dim oWord As Word.Application
Dim WordWasNotRunning As Boolean
Dim oDoc As Word.Document
'Prepare to open a modal window, where a folder is selected
Set MyPath = Application.FileDialog(msoFileDialogFolderPicker)
With MyPath
'Open modal window
.AllowMultiSelect = False
If .Show Then
'The user has selected a folder
'Loop through the chosen folder
For Each SelectedFolder In .SelectedItems
'Name of the selected folder
PathOfSelectedFolder = SelectedFolder & ExtraSlash
Set fs = CreateObject("Scripting.FileSystemObject")
Set SelectedFolderTemp = fs.GetFolder(PathOfSelectedFolder)
'Loop through the files in the selected folder
For Each MyFile In SelectedFolderTemp.Files
'Open each file:
Set oDoc = Documents.Open(MyFile, Visible:=False)
Set wRng = oDoc.Range
i = 1
Do
'Word Macro
With wRng.Find
.Text = "Stg-?*psi/ft."
.Forward = True
.Wrap = wdFindStop
.MatchWildcards = True
.Execute
End With
If wRng.Find.Found Then
'Increment the row
'xRow = xRow + 1
'Add the Find to the worksheet
WS.Cells(1, i) = wRng
'Call ClearFindAndReplaceParameters
i = i + 1
End If
Loop While wRng.Find.Found
MyFile.Close savechanges:=wdDoNotSaveChange
Next
Next
End If
End With
End Sub
VBA rookie here. I am trying to write an Excel macro that opens a number of Word documents one-by-one, performs a find loop, and writes the found text to the spreadsheet. My code is throwing a type mismatch error on the line
Set oDoc = Documents.Open(MyFile, Visible:=False)
Any help would be appreciated!!
Sub DetailFinder()
Dim fd As FileDialog
Dim PathOfSelectedFolder As String
Dim SelectedFolder
Dim SelectedFolderTemp
Dim MyPath As FileDialog
Dim fs
Dim ExtraSlash
ExtraSlash = "\"
Dim MyFile As Object
Dim wRng As Word.Range
Dim oWord As Word.Application
Dim WordWasNotRunning As Boolean
Dim oDoc As Word.Document
'Prepare to open a modal window, where a folder is selected
Set MyPath = Application.FileDialog(msoFileDialogFolderPicker)
With MyPath
'Open modal window
.AllowMultiSelect = False
If .Show Then
'The user has selected a folder
'Loop through the chosen folder
For Each SelectedFolder In .SelectedItems
'Name of the selected folder
PathOfSelectedFolder = SelectedFolder & ExtraSlash
Set fs = CreateObject("Scripting.FileSystemObject")
Set SelectedFolderTemp = fs.GetFolder(PathOfSelectedFolder)
'Loop through the files in the selected folder
For Each MyFile In SelectedFolderTemp.Files
'Open each file:
Set oDoc = Documents.Open(MyFile, Visible:=False)
Set wRng = oDoc.Range
i = 1
Do
'Word Macro
With wRng.Find
.Text = "Stg-?*psi/ft."
.Forward = True
.Wrap = wdFindStop
.MatchWildcards = True
.Execute
End With
If wRng.Find.Found Then
'Increment the row
'xRow = xRow + 1
'Add the Find to the worksheet
WS.Cells(1, i) = wRng
'Call ClearFindAndReplaceParameters
i = i + 1
End If
Loop While wRng.Find.Found
MyFile.Close savechanges:=wdDoNotSaveChange
Next
Next
End If
End With
End Sub