Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each ... Next loop - need to reference the loop variable
I have a for-each loop which loops through worksheets. Due to an error
in Excel, and because I am copying a lot of sheets, I need to save, close, and open the workbook several times from within the for-each loop to prevent Excel from crashing (I'm basically using the workaround proposed he http://support.microsoft.com/default...4&Product=xlw). I'm using Excel 2000. This resets the reference to my worksheet in the for-each loop, and I'm not sure how to save that information so I can 'reassign' the reference after re-opening the workbook (within the loop). I tried saving the name of the worksheet and then using a Set statement on the loop variable (shtLL) to retrieve that particular worksheet, but that didn't work. When it got to the "next" statement it acted as though it had completed the for-each loop. Here's some demonstrative code: Dim shtLL As Worksheet Dim wkbkLL As Workbook Dim tempName as String For Each shtLL In wkbkLL.Worksheets tempName = shtLL.name wkbkLL.Close savechanges:=True Set wkbkLL = Nothing Set wkbkLL = Application.Workbooks.Open(wkbkLLPath) set shtLL = wkbkLL.Worksheets(tempName) Next Thanks for any help you can give! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each ... Next loop - need to reference the loop variable
If you have to loop through all of the sheets, maybe save the number of
worksheets to a variable before going into the loop, then use a For/Next loop. Of course, I don't know the destination of the copies you are making - this would only work if the destination is at the end of the workbook (otherwise Worksheets(i) will refer to the wrong sheet). Anyway, code along these lines this worked fine for me x = WkBk.Worksheets.Count For i = 1 to x WkBk.Worksheets(i).Copy After:= WkBK.Sheets(WkBk.Sheets.Count) If i mod 10 = 0 then WkBk.Save WkBk.Close Set WkBk = Nothing Set WkBk = Workbooks.Open (Filename) End If Next i " wrote: I have a for-each loop which loops through worksheets. Due to an error in Excel, and because I am copying a lot of sheets, I need to save, close, and open the workbook several times from within the for-each loop to prevent Excel from crashing (I'm basically using the workaround proposed he http://support.microsoft.com/default...4&Product=xlw). I'm using Excel 2000. This resets the reference to my worksheet in the for-each loop, and I'm not sure how to save that information so I can 'reassign' the reference after re-opening the workbook (within the loop). I tried saving the name of the worksheet and then using a Set statement on the loop variable (shtLL) to retrieve that particular worksheet, but that didn't work. When it got to the "next" statement it acted as though it had completed the for-each loop. Here's some demonstrative code: Dim shtLL As Worksheet Dim wkbkLL As Workbook Dim tempName as String For Each shtLL In wkbkLL.Worksheets tempName = shtLL.name wkbkLL.Close savechanges:=True Set wkbkLL = Nothing Set wkbkLL = Application.Workbooks.Open(wkbkLLPath) set shtLL = wkbkLL.Worksheets(tempName) Next Thanks for any help you can give! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each ... Next loop - need to reference the loop variable
Thanks very much for your assistance. Copying a sheet to the end seems
to take a bit longer, but at least I can get it to work. Danny JMB wrote: If you have to loop through all of the sheets, maybe save the number of worksheets to a variable before going into the loop, then use a For/Next loop. Of course, I don't know the destination of the copies you are making - this would only work if the destination is at the end of the workbook (otherwise Worksheets(i) will refer to the wrong sheet). Anyway, code along these lines this worked fine for me x = WkBk.Worksheets.Count For i = 1 to x WkBk.Worksheets(i).Copy After:= WkBK.Sheets(WkBk.Sheets.Count) If i mod 10 = 0 then WkBk.Save WkBk.Close Set WkBk = Nothing Set WkBk = Workbooks.Open (Filename) End If Next i " wrote: I have a for-each loop which loops through worksheets. Due to an error in Excel, and because I am copying a lot of sheets, I need to save, close, and open the workbook several times from within the for-each loop to prevent Excel from crashing (I'm basically using the workaround proposed he http://support.microsoft.com/default...4&Product=xlw). I'm using Excel 2000. This resets the reference to my worksheet in the for-each loop, and I'm not sure how to save that information so I can 'reassign' the reference after re-opening the workbook (within the loop). I tried saving the name of the worksheet and then using a Set statement on the loop variable (shtLL) to retrieve that particular worksheet, but that didn't work. When it got to the "next" statement it acted as though it had completed the for-each loop. Here's some demonstrative code: Dim shtLL As Worksheet Dim wkbkLL As Workbook Dim tempName as String For Each shtLL In wkbkLL.Worksheets tempName = shtLL.name wkbkLL.Close savechanges:=True Set wkbkLL = Nothing Set wkbkLL = Application.Workbooks.Open(wkbkLLPath) set shtLL = wkbkLL.Worksheets(tempName) Next Thanks for any help you can give! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each ... Next loop - need to reference the loop variable
I don't know the technical reasons, but I suspect the problem is with the
For/Each loop object variable being destroyed. I saw you tried to re-establish the link, but I'm skeptical it works like that. Many times I use For/Each to loop through a range (example below) and use the same object variable (without destroying it first - probably a bad practice). But even though rngCell is already assigned to a cell, the second for loop will still work okay. Therefore, I think VBA must keep its own counter and not rely on what the existing value is of the object variable used as a counter. And, once the variable is destroyed (by closing the workbook), maybe VBA's internal counter is also destroyed? For Each rngCell in Myrange1 ... Next rngCell For Each rngCell in MyRange2 ..... Next rngCell "Daniel" wrote: Thanks very much for your assistance. Copying a sheet to the end seems to take a bit longer, but at least I can get it to work. Danny JMB wrote: If you have to loop through all of the sheets, maybe save the number of worksheets to a variable before going into the loop, then use a For/Next loop. Of course, I don't know the destination of the copies you are making - this would only work if the destination is at the end of the workbook (otherwise Worksheets(i) will refer to the wrong sheet). Anyway, code along these lines this worked fine for me x = WkBk.Worksheets.Count For i = 1 to x WkBk.Worksheets(i).Copy After:= WkBK.Sheets(WkBk.Sheets.Count) If i mod 10 = 0 then WkBk.Save WkBk.Close Set WkBk = Nothing Set WkBk = Workbooks.Open (Filename) End If Next i " wrote: I have a for-each loop which loops through worksheets. Due to an error in Excel, and because I am copying a lot of sheets, I need to save, close, and open the workbook several times from within the for-each loop to prevent Excel from crashing (I'm basically using the workaround proposed he http://support.microsoft.com/default...4&Product=xlw). I'm using Excel 2000. This resets the reference to my worksheet in the for-each loop, and I'm not sure how to save that information so I can 'reassign' the reference after re-opening the workbook (within the loop). I tried saving the name of the worksheet and then using a Set statement on the loop variable (shtLL) to retrieve that particular worksheet, but that didn't work. When it got to the "next" statement it acted as though it had completed the for-each loop. Here's some demonstrative code: Dim shtLL As Worksheet Dim wkbkLL As Workbook Dim tempName as String For Each shtLL In wkbkLL.Worksheets tempName = shtLL.name wkbkLL.Close savechanges:=True Set wkbkLL = Nothing Set wkbkLL = Application.Workbooks.Open(wkbkLLPath) set shtLL = wkbkLL.Worksheets(tempName) Next Thanks for any help you can give! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
For Each ... Next loop - need to reference the loop variable
I assume VB treats this type of loop in such a way that:
Set rngCell = Myrange1(index) is carried out for each loop and index is incremented each time. Perhaps index is a pointer to (an element in) an array? Anyway, I guess the issue is that index gets destroyed as well, and this is probably because Myrange1 gets destroyed when you close the workbook (within the loop). I am sure there is a way to reassign stuff properly, if you really knew what you were doing. JMB wrote: I don't know the technical reasons, but I suspect the problem is with the For/Each loop object variable being destroyed. I saw you tried to re-establish the link, but I'm skeptical it works like that. Many times I use For/Each to loop through a range (example below) and use the same object variable (without destroying it first - probably a bad practice). But even though rngCell is already assigned to a cell, the second for loop will still work okay. Therefore, I think VBA must keep its own counter and not rely on what the existing value is of the object variable used as a counter. And, once the variable is destroyed (by closing the workbook), maybe VBA's internal counter is also destroyed? For Each rngCell in Myrange1 ... Next rngCell For Each rngCell in MyRange2 ..... Next rngCell "Daniel" wrote: Thanks very much for your assistance. Copying a sheet to the end seems to take a bit longer, but at least I can get it to work. Danny JMB wrote: If you have to loop through all of the sheets, maybe save the number of worksheets to a variable before going into the loop, then use a For/Next loop. Of course, I don't know the destination of the copies you are making - this would only work if the destination is at the end of the workbook (otherwise Worksheets(i) will refer to the wrong sheet). Anyway, code along these lines this worked fine for me x = WkBk.Worksheets.Count For i = 1 to x WkBk.Worksheets(i).Copy After:= WkBK.Sheets(WkBk.Sheets.Count) If i mod 10 = 0 then WkBk.Save WkBk.Close Set WkBk = Nothing Set WkBk = Workbooks.Open (Filename) End If Next i " wrote: I have a for-each loop which loops through worksheets. Due to an error in Excel, and because I am copying a lot of sheets, I need to save, close, and open the workbook several times from within the for-each loop to prevent Excel from crashing (I'm basically using the workaround proposed he http://support.microsoft.com/default...4&Product=xlw). I'm using Excel 2000. This resets the reference to my worksheet in the for-each loop, and I'm not sure how to save that information so I can 'reassign' the reference after re-opening the workbook (within the loop). I tried saving the name of the worksheet and then using a Set statement on the loop variable (shtLL) to retrieve that particular worksheet, but that didn't work. When it got to the "next" statement it acted as though it had completed the for-each loop. Here's some demonstrative code: Dim shtLL As Worksheet Dim wkbkLL As Workbook Dim tempName as String For Each shtLL In wkbkLL.Worksheets tempName = shtLL.name wkbkLL.Close savechanges:=True Set wkbkLL = Nothing Set wkbkLL = Application.Workbooks.Open(wkbkLLPath) set shtLL = wkbkLL.Worksheets(tempName) Next Thanks for any help you can give! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Loop with variable name? | Excel Discussion (Misc queries) | |||
use a variable to name an object in a loop | Excel Programming | |||
Loop with variable | Excel Programming | |||
Advancing outer Loop Based on criteria of inner loop | Excel Programming | |||
Excel VBA Loop & Variable Reference | Excel Programming |