Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop through all open workbooks except Personal.xls
Hello,
I'm really inexperienced in VBA and I need to execute a sub procedure in all open workbooks except in Personal.xls, but I'm having trouble coding this. What am I doing Wrong? Or maybe there's an easier way to do this Here it is what I have: Sub nome_cenario() Dim wkb As Workbook For Each wkb In Workbooks If wkb.name = "PERSONAL.xls" Then Next wkb Else Call Myprocedure Next wkb End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop through all open workbooks except Personal.xls
Try...
Sub nome_cenario() Dim wkb As Workbook For Each wkb In Application.Workbooks If Not UCase$(wkb.Name) = "PERSONAL.XLS" Then Call Myproceure Next 'wkb End Sub -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop through all open workbooks except Personal.xls
"Costanza" wrote in message
... Hello, I'm really inexperienced in VBA and I need to execute a sub procedure in all open workbooks except in Personal.xls, but I'm having trouble coding this. Your idea is pretty similar to how I do that. GS showed you a slightly different method; his use of the UCASE function is a good way to avoid potential problems with upper case and lower case variations of the same name in your IF test. You were having trouble because 1) you left out the END IF and 2) you broke the FOR ... NEXT structure the way you had it coded. Also, I suggest using indentation to improve readability: Sub nome_cenario() Dim wkb As Workbook For Each wkb In Workbooks If wkb.Name = "PERSONAL.xls" Then Else Call Myprocedure End If Next wkb End Sub I would not normally code an empty IF ... THEN clause like that; though I have done it on occasion- my reasoning being that the structure helped the "self documentation" of the code, even though I suspect it hurts execution efficiency somewhat. Another way to code this would be: Sub nome_cenario() Dim wkb As Workbook For Each wkb In Workbooks If NOT wkb.Name = "PERSONAL.xls" Then Call Myprocedure End If Next wkb End Sub -- Clif McIrvin (clare reads his mail with moe, nomail feeds the bit bucket :-) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop through all open workbooks except Personal.xls
Thanks a lot.
it worked fine On 5 Maio, 20:18, GS wrote: Try... Sub nome_cenario() * Dim wkb As Workbook * For Each wkb In Application.Workbooks * * If Not UCase$(wkb.Name) = "PERSONAL.XLS" Then Call Myproceure * Next 'wkb End Sub -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop through all open workbooks except Personal.xls
Thanks a lot.
it worked . Thanks also for taking the time to explain what I was doing wrong. On 5 Maio, 21:07, "Clif McIrvin" wrote: "Costanza" wrote in message ... Hello, I'm really inexperienced in VBA and I need to execute a sub procedure in all open workbooks except in Personal.xls, but I'm having trouble coding this. Your idea is pretty similar to how I do that. GS showed you a slightly different method; his use of the UCASE function is a good way to avoid potential problems with upper case and lower case variations of the same name in your IF test. You were having trouble because 1) you left out the END IF and 2) you broke the FOR ... NEXT structure the way you had it coded. Also, I suggest using indentation to improve readability: Sub nome_cenario() Dim wkb As Workbook For Each wkb In Workbooks * * If wkb.Name = "PERSONAL.xls" Then * * Else * * * * Call Myprocedure * * End If Next wkb End Sub I would not normally code an empty IF ... THEN clause like that; though I have done it on occasion- my reasoning being that the structure helped the "self documentation" of the code, even though I suspect it hurts execution efficiency somewhat. Another way to code this would be: Sub nome_cenario() Dim wkb As Workbook For Each wkb In Workbooks * * If NOT wkb.Name = "PERSONAL.xls" Then * * * * Call Myprocedure * * End If Next wkb End Sub -- Clif McIrvin (clare reads his mail with moe, nomail feeds the bit bucket :-) |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Loop through all open workbooks except Personal.xls
"Costanza" wrote in message
... Thanks a lot. it worked . Thanks also for taking the time to explain what I was doing wrong. ----------- You're welcome. I learned much of what I know about VBA in these newsgroups ... don't be afraid to ask questions! -- Clif McIrvin (clare reads his mail with moe, nomail feeds the bit bucket :-) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Personal macros not available when workbooks.open | Excel Programming | |||
Window_Open in personal.xls run each time I open subsequent workbooks. | Excel Programming | |||
Loop Through All Open Workbooks | Excel Programming | |||
Loop through all Open workbooks | Excel Programming | |||
Loop through open workbooks | Excel Programming |