VBA - Do Loop or SaveChanges = false



Does Excel 2003's DO Until LOOP have a buffer?
Using VBA, I am opening a master file and then loop through 77 more files to
copy a sheet to the master file. All works without Excel throwing a dialog
asking to save changes until the 73 file. I can move the 73rd file up in to
the loop and Excel does not throw the dialog. This tells me there is nothing
wrong with the file. Any help would be appreciated.

This is the code:
Sub Combine_Files()
Application.DisplayAlerts = False
Application.ScreenUpdating = False

w = ActiveCell.Value
ww = ActiveCell.Offset(0, -1).Value
a = Range("period").Value
F = Range("formatting").Value
vv = Application.Sheets.Parent.Name

dir_select = "c:\peoplesoft nvision reports\2009\oney group\" & a & "\"
Workbooks.Open Filename:=dir_select & w

Sheets("P&L").Name = ww

Windows("oney files to sups").Activate
ActiveCell.Offset(1, 0).Select

Do Until ActiveCell.Value = ""
z = ActiveCell.Address
x = ActiveCell.Value
xx = ActiveCell.Offset(0, -1).Value
cc = ActiveCell.Offset(0, -2).Value

dir_select = "c:\peoplesoft nvision reports\2009\oney group\" & a & "\"
Workbooks.Open Filename:=dir_select & x


ActiveSheet.Copy Before:=Workbooks(w).Sheets(ww)
ActiveWorkbook.Sheets("P&L").Tab.ColorIndex = cc
Sheets("P&L").Name = xx

If F = "LIZ" Then
ActiveSheet.Outline.ShowLevels RowLevels:=1
End If

Workbooks(x).Close SaveChanges:=False

Windows("oney files to sups.xls").Activate

ActiveCell.Offset(1, 0).Select


Application.ScreenUpdating = True

msg = "Do not pass go, do not collect $200"
MsgBox msg & Chr(13) & "until you save this file!"
End Sub


I don't think so... Ultimately all software applications have physical limits
based on your software configuration and system memory but looping through 77
files is well within the limits of most of the present day systems.

Does it still throw the warning after 73 files even if you move the offendng
file up?

Just to complete your task, you may save after processing each file and find
out the cause later :)

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question