You are using FileSearch. Did you it look up in Help?
1. "currentFile.Open does not seem to be the proper syntax."
No, it most certainly is not. currentFile is a string. Strings do not have
an Open method. Application has an Open method.
2. If you use a With statement (and you should), then you do not need to use
the fs object like that - Item(i). You can just use i
Dim currentFile As String
Dim fs As FileSearch
Dim i As Long
Set fs = Application.FileSearch
With fs
.LookIn = "C:\zzz"
.FileName = "*.doc*"
If .Execute(SortBy:=msoSortByFileName, _
SortOrder:=msoSortOrderAscending) > 0 Then
For i = 1 To .FoundFiles.Count
currentFile = .FoundFiles(i)
MsgBox currentFile
Next i
Else
MsgBox "There were no files found."
End If
End With
I am not sure why you are making each foundfile into an explicit string into
a variable. If you are opening the file, then I would follow the suggestion
regarding using a document object.
Sub yadda()
Dim ThatDoc As Document
Dim fs As FileSearch
Dim i As Long
Set fs = Application.FileSearch
With fs
.LookIn = "C:\zzz"
.FileName = "*.doc*"
If .Execute(SortBy:=msoSortByFileName, _
SortOrder:=msoSortOrderAscending) > 0 Then
For i = 1 To .FoundFiles.Count
Set ThatDoc = Application.Open Filename:= .FoundFiles(i)
' do your action to ThatDoc using the ThatDoc object
' presumably with a
ThatDoc.Save
ThatDoc.Close
' then destroy the object
Set ThatDoc = Nothing
Next i
Else
MsgBox "There were no files found."
End If
End With
Alternatively, you could pass the string to another Sub. If your code is
long and involved, I would strongly suggest this route. If keeps your
procedures tidy, and makes debugging MUCH easier.
Sub yadda()
Dim fs As FileSearch
Dim i As Long
Set fs = Application.FileSearch
With fs
.LookIn = "C:\zzz"
.FileName = "*.doc*"
If .Execute(SortBy:=msoSortByFileName, _
SortOrder:=msoSortOrderAscending) > 0 Then
For i = 1 To .FoundFiles.Count
' this sends each file to the DoStuff procedure
Call DoStuff(.FoundFile(i))
Next i
Else
MsgBox "There were no files found."
End If
End With
End Sub
Sub DoStuff (strFile As String)
Application.Open Filename:=strFile
' do whatever it is with the opened file
' saving, closing...whatever
End Sub
That way you can keep the instructions actioning each file separate from
other instructions that have nothing to do with it. And technically speaking,
the FileSearch instructions have absolutely NOTHING to do with what you do
with each file found.
So keep 'em separate.
"I don't want to post the entire code, since it is too long,"
If it is too long, then it probably IS too long. Break it up into manageable
chunks. Again, it makes it much much easier to debug things.
Also, as a suggestion, very often long VBA code comes from:
a) not using Objects fully
b) not using logic statements to their full design and power
I do not know how many times I have seen lines and lines and lines of IF...
ELSE statements, when a single Select Case would have done the job.
Not that I am saying this is what you are doing. How would I know? But I am
saying that it is likely that you can "chunk" up your long code better,
making calls to other procedures perhaps. Building efficient Functions
perhaps.
In any case, Application.Open opens files, and it takes a string.
.FoundFiles(i) - if you are using a With statement - is that string.
Otherwise, yes, you can use fs as the explicit parent object.
Application.Open Filename:=fs.FoundFiles(i)
[quoted text clipped - 21 lines]
