"Code execution has been interrupted" message

  • Thread starter Thread starter Dave O
  • Start date Start date
D

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
 
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
 
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?
 
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
 
Back
Top