Loop through all open workbooks except Personal.xls

[Costanza]

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

 

[GS]

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

 

[Clif McIrvin]

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

 

[Costanza]

Thanks a lot.

it worked fine

 

[Costanza]

Thanks a lot.

it worked .

Thanks also for taking the time to explain what I was doing wrong.

 

[Clif McIrvin]

Thanks a lot.

it worked .

Thanks also for taking the time to explain what I was doing wrong.