Help debugging macro

Hi there,

I have created a macro that looks in a folder specified by the user,
does a search in the Word documents in said folder, then moves some
data to Excel.

I am stuck at this code:

For i = 1 To fs.FoundFiles.Count
currentFile = fs.FoundFiles.Item(i)
' My code here...
Next i

When I run tha macro in debug mode, currentFile takes on the name of
the first file in the folder (in this case, c:\temp\CCPS6970.doc), but
the rest of the code does not run properly, because the document is
not opening up, and "currentFile.Open" does not seem to be the proper
syntax.

I don't want to post the entire code, since it is too long, but if you
deem it necessary to help me, I'll gladly do it.

Thanks for your input.

Carlos

stevencraigmiller(at)comcast(dot)net>
Mon Jun 30 11:13:02 PDT 2008

To: supersub15,

How about:

Dim newDoc as Document

set newDoc = Documents.Open(FileName:=currentFile)

That's assuming that "currentFile" is a string.

Steven Craig Miller

"supersub15" wrote:

> Hi there,
>
> I have created a macro that looks in a folder specified by the user,
> does a search in the Word documents in said folder, then moves some
> data to Excel.
>
> I am stuck at this code:
>
> For i = 1 To fs.FoundFiles.Count
> currentFile = fs.FoundFiles.Item(i)
> ' My code here...
> Next i
>
> When I run tha macro in debug mode, currentFile takes on the name of
> the first file in the folder (in this case, c:\temp\CCPS6970.doc), but
> the rest of the code does not run properly, because the document is
> not opening up, and "currentFile.Open" does not seem to be the proper
> syntax.
>
> I don't want to post the entire code, since it is too long, but if you
> deem it necessary to help me, I'll gladly do it.
>
> Thanks for your input.
>
> Carlos
>

fumei
Mon Jun 30 12:28:14 PDT 2008

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.

fumei
Mon Jun 30 12:42:40 PDT 2008

Curses. For every instance where I have used FoundFile in the posts...it
must be FoundFiles.

FoundFile is invalid.

fumei wrote:
>Just because I have a bugbear about explicitness...
>
> For i = 1 To .FoundFiles.Count
> ' this sends each file to the DoStuff procedure
> Call DoStuff(.FoundFile(i))
> Next i
>
>My comment is technically incorrect....i.e. wrong.
>
>It does NOT send each file to the DoStuff procedure. It sends a STRING -
>which is the path and name of each FoundFile - to the DoStuff procedure.
>
>You could of course actually send the file, although I am not sure there is
>an advantage.
>
>Sub yadda()
>Dim fs As FileSearch
>Dim i As Long
>Dim ThatDoc As Document
>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:= .FoundFile(i)
> ' this really does sends each file to the DoStuff procedure
> Call DoStuff(ThatDoc)
>
> ' note that the ThatDoc object should be destroyed HERE
> ' in the procedure that created it
> Set ThatDoc = Nothing
> Next i
> Else
> MsgBox "There were no files found."
> End If
>End With
>End Sub
>
>Sub DoStuff (Incoming As Document)
>
> With Incoming
> ' action the opened file
> ' blah blah
>
> ' then Close it
> .Close wdSaveChanges
> End With
>
> ' control is passed back to originating Sub
>End Sub
>
>>You are using FileSearch. Did you it look up in Help?
>>
>[quoted text clipped - 120 lines]
>>>
>>>Carlos

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.aspx/word-programming/200806/1

supersub15
Wed Jul 02 04:59:25 PDT 2008

Thanks Steven and fumei. I'll try the code you posted.

Carlos