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

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

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 :-)

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

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 :-)

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 :-)