Macro stuck in infinite Dir loop

[David Turner]

I've tried to cobble together some code based on snippets given by the
experts here to run one or more macros on all the files in a folder.
Unfortunately, it keeps opening and and closing each file in an infinite Dir
loop. Can anyone see what's going wrong? I've tried all sorts of things to no
avail.
Any help much appreciated.
David Turner

Sub BatchRun()

Application.ScreenUpdating = False

Dim strFileName As String
Dim strPath As String
Dim sOrgPath As String
Dim oDoc As Document
Dim fDialog As FileDialog
Dim macroList() As String
Dim macroName As String
Dim k As Long
Dim FileArray()
Dim f As Long

ReDim macroList(0)

sOrgPath = Options.DefaultFilePath(wdDocumentsPath)

Set fDialog = Application.FileDialog(msoFileDialogFolderPicker)
With fDialog
.InitialFileName = sOrgPath
.Title = "Select folder and click OK"
.AllowMultiSelect = False
.InitialView = msoFileDialogViewList
If .Show <> -1 Then
MsgBox "Cancelled By User", , "BatchRun"
Exit Sub
End If
strPath = fDialog.SelectedItems.Item(1)
If Right(strPath, 1) <> "\" Then strPath = strPath + "\"
End With

Do
macroName = InputBox("Enter the name of the macro that you want to
run.", "Macro Name", "Macro1")
If Len(macroName) = 0 Then
MsgBox ("Nothing entered. Exiting routine.")
Exit Sub
Else
macroList(UBound(macroList)) = macroName
ReDim Preserve macroList(UBound(macroList) + 1)
End If
Loop While MsgBox("Do you want to run an additional macro?", vbYesNo +
vbQuestion, _
"More macros?") = vbYes

f = 0
strFileName = Dir$(strPath & "*.doc")
Do While (Len(strFileName) > 0)
Set oDoc = Documents.Open(strPath & strFileName)
For k = 0 To UBound(macroList) - 1
Application.Run macroList(k)
Next k
oDoc.Save
oDoc.Close
Set oDoc = Nothing
f = f + 1
ReDim Preserve FileArray(1 To f)
FileArray(f) = strFileName
strFileName = Dir$()
Loop

Application.ScreenUpdating = True
MsgBox "All Done"

End Sub

 

[Doug Robbins - Word MVP on news.microsoft.com]

The usual way of using a Dir loop is

While strFileName<>""
Set oDoc = Documents.Open(strPath & strFileName)
For k = 0 To UBound(macroList) - 1
Application.Run macroList(k)
Next k
oDoc.Save
oDoc.Close
Set oDoc = Nothing
f = f + 1
ReDim Preserve FileArray(1 To f)
FileArray(f) = strFileName
strFileName = Dir$()
Loop

Of course, there is always the chance that the macros that you are running
is causing the problem.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

 

[David Horowitz]

David,
Standard debugging tips I would recommend for you:
I always use "Option Explicit" in all my modules. (Tools > Options > Require
variable declarations)
What I would do is set a breakpoint on the Application.Run line of code by
hitting F9 (Debug > Toggle Breakpoint) then hit F5 to run the program. Then
when your breakpoint gets hit, I'd hit Shift-F8 (Debug > Step Over) and see
which macro hangs you up.
Once you discover which macro sends you off into never-never land, you can
start all over again (maybe using Run > Reset), using Shift-F8 to Step Over
all the macros leading up to the one that's the problem, and then using F8
(Step Into) to go line-by-line through the offending macro.
HTH.
Dave

 

[Karl E. Peterson]

I think you're right about the macros being run causing the problem though.
It runs OK with some macros (usually very short and simple) and gets stuck in
a loop with others.

Do the "problem" macros call Dir() by any chance? That'd definitely be one of those
"foul the waters" red flag kinda things. The other thing I'll point out is that
it's considered "Best Form" to build an array of filenames in your Dir() loop, then
enter another loop to process each element of the array. This not only avoids the
previous problem with nested Dir calls, but also avoids issues related to changing
the directory content during the loop.

 

[David Turner]

Standard debugging tips I would recommend for you:
I always use "Option Explicit" in all my modules. (Tools > Options > Require
variable declarations)

Thanks for your reply Dave. Yes, that's definitely good practice and I
should do that all the time. I'll give it a try.

What I would do is set a breakpoint on the Application.Run line of code by
hitting F9 (Debug > Toggle Breakpoint) then hit F5 to run the program. Then
when your breakpoint gets hit, I'd hit Shift-F8 (Debug > Step Over) and see
which macro hangs you up.

It's not so much a case of a macro hanging up. The offending macro runs just
fine on its own but when run from batch macro, the whole thing goes into an
endless loop, with each file being opened, processed and closed in turn and
then beginning over again. This happens when I'm stepping through the batch
macro for testing purposes from within the VBA interface of the template
concerned. If I just attach the template and run the macro with all the files
and the template closed, the files are processed normally.

Once you discover which macro sends you off into never-never land, you can
start all over again (maybe using Run > Reset), using Shift-F8 to Step Over
all the macros leading up to the one that's the problem, and then using F8
(Step Into) to go line-by-line through the offending macro.

I'll give that a try. Many thanks for your tips.

Dave

 

[David Turner]

:

Do the "problem" macros call Dir() by any chance? That'd definitely be one of those
"foul the waters" red flag kinda things.

Thanks for your reply Karl. No they don't.

The other thing I'll point out is that

it's considered "Best Form" to build an array of filenames in your Dir() loop, then
enter another loop to process each element of the array. This not only avoids the
previous problem with nested Dir calls, but also avoids issues related to changing
the directory content during the loop.

Yes, I think you've probably hit the nail on the head. It should "know"
which files are to be processed "before" running the macro concerned, rather
than trying to run it on each file from within the Dir loop and then going
back to look for the next file to be processed and getting confused over
which is the active document no doubt.
Thanks for the tip. Much appreciated.

BR,
Dave