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").Select 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 Sheets("P&L").Select ActiveSheet.Select ActiveSheet.Copy Befo=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 Range(z).Select ActiveCell.Offset(1, 0).Select Loop Workbooks(w).Activate Sheets(ww).Select Application.ScreenUpdating = True msg = "Do not pass go, do not collect $200" MsgBox msg & Chr(13) & "until you save this file!" End Sub |
VBA - Do Loop or SaveChanges = false
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 :-) "Dan" wrote: 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").Select 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 Sheets("P&L").Select ActiveSheet.Select ActiveSheet.Copy Befo=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 Range(z).Select ActiveCell.Offset(1, 0).Select Loop Workbooks(w).Activate Sheets(ww).Select Application.ScreenUpdating = True msg = "Do not pass go, do not collect $200" MsgBox msg & Chr(13) & "until you save this file!" End Sub |
All times are GMT +1. The time now is 07:48 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com