"Code execution has been interrupted" message

[Dave O]

I've written the following code to open all the files in a directory.
For some reason I get a "Code execution has been interrupted" message
at the "ActiveWorkbook.Save" line and at the "ActiveWorkbook.Close"
line, and I can't figure out why. Can anyone make a suggestion?

Thanks

Sub test()
Dim FileNm As String

FileNm = Dir("T:\2.7 Cost Baseline\*.xls")

Do While FileNm <> ""
Workbooks.Open FileName:=FileNm
ActiveWorkbook.Save
ActiveWorkbook.Close
FileNm = Dir
Loop

End Sub

 

[Harald Staff]

Whenever you ask for things that take some time (like opening/saving files,
remote controlling Word or things like that), put this line immediately
after:
DoEvents
simplified it means "wait and listen and think", leaving the operating
system in charge for a little while.

But even so, you have no guarantee that ActiveWorkbook is FileNm, it may at
times be another. Better to do some object oriented approach:

Dim WB as Workbook
'do while stuff
Set WB = Workbooks.Open(FileName:=FileNm)
DOEVENTS
'operations here
WB.Save
DoEvents
WB.Close

HTH. Best wishes Harald

 

[Dave O]

Harald-
Thanks for your response- I tried adding just the DoEvents line and
then tried the Set WB method. Now the "Execution interrupted" message
appears on the DoEvents line.

This code worked properly the other day. I can't think of what Excel
change I may have made that would drive this.

Any other thoughts?

 

[Harald Staff]

Find out if it's the first or the same workbook that causes the problem.
Would any of the workbooks have macros that run on opening ? They might
cause a conflict of some sort -or they may be the interrupted ones.

Best wishes Harald

 

[Dave O]

Weird. A reboot seems to have resolved this.

 

[Harald Staff]

You haven't restarted the computer between now and "the other day" ??
http://en.wikipedia.org/wiki/Memory_leak