Could somebody open the window? 1

[Fallenwing]

I have a simple question that I just can't figure out. I want my code to say the following:

If workbook So and So is open then close it

But I can't figure out how to get the if statement(with open) to work(the closing part is easy). Help this poor meager soul! Thanks.

 

[Bowers74]

This just might help,

If Workbooks(&quot;SoAndSo.xls&quot;).Name <> &quot;&quot; then Workbooks(&quot;SoAndSo&quot;).Close

Happy Friday!



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[JPMontreal]

hi,

Create this macro
Sub CloseSo()

Dim wbSo As Workbook

For Each wbSo In Workbooks
If wbSo.Name = "So and So" Then wbSo.Close
Next
End Sub

Jean-Paul
Montreal
jp@solutionsvba.com
mtljp2@sympatico.ca

 

[Fallenwing]

Thanks to you guys for your help. JP, your solution did the trick, but why is there not a more straightforward way to do this?

 

[Bowers74]

Yes,

MINE


Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[JPMontreal]

Hi

The system has no idea of how many books are open at the same time, that’s why you must scan the collection of all open books.

Hope that it answers your question FallenWing.

Thank for your Star.


Jean-Paul
Montreal
jp@solutionsvba.com
mtljp2@sympatico.ca

 

[Bowers74]

Why search through all of the open workbooks, if you know the name of the workbook already?

Try my code this way (if it "looks" better to you).

Sub Close_SoAndSo()
If Workbooks(&quot;SoAndSo.xls&quot;).Name <> &quot;&quot; Then 
    Workbooks(&quot;SoAndSo&quot;).Close
End If

You could even do this:

Sub Close_SoAndSo()
On Error Resume Next
Workbooks("SoAndSo.xls").Close
On Error GoTo 0
End Sub

Either way, you don't have to search through all of your open workbooks to close a specific workbook.



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[TonyJollans]

Hi Mike,

Your final bit of code is good but your others will all fail with 'invalid subscript' (or out of range or whatever the message is) because you can't reference SoAndSo if it's not there. You need an error trap - and when you've got one why bother checking the name and so we're back to your final bit of code which is the way I would do it (except I probably wouldn't bother resetting the error trap because the end sub would do it for me.

Enjoy,
Tony