Open workbook if not open

[Djblois]

I want to change this code to work only if it is not open already

[VBA]Workbooks.Open Filename:="H:\@temp\Daniel B\Reference\Atalanta Codes.xls"[/VBA]

[mvidas]

Djblois,

When I need to do this, I use a workbook variable to test it:[vba]'Workbooks.Open "H:\@temp\Daniel B\Reference\Atalanta Codes.xls"
Dim WB As Workbook
On Error Resume Next
Set WB = Workbooks("Atalanta Codes.xls")
On Error GoTo 0
If WB Is Nothing Then
Set WB = Workbooks.Open("H:\@temp\Daniel B\Reference\Atalanta Codes.xls")
End If[/vba]And afterwards, you can refer to that book simple by using WB, so it is a win-win in my opinion.
Matt

[Djblois]

Matt thank you Now I want to take it one step further.
When the macro is done working with the file I want it to close it if it was originally not open or I want it to remail open if it was open already.

[mvidas]

Sure thing, you can create a boolean variable (takes up almost no memory) to store whether it was open or not:[vba]Dim WB As Workbook, WasWBOpen As Boolean
On Error Resume Next
Set WB = Workbooks("Atalanta Codes.xls")
WasWBOpen = True
On Error GoTo 0
If WB Is Nothing Then
Set WB = Workbooks.Open("H:\@temp\Daniel B\Reference\Atalanta Codes.xls")
WasWBOpen = False
End If
'your code
If WasWBOpen = False Then
WB.Close
End If[/vba]Matt

[Zack Barresse]

Why use a variable and waste resources like that??

I use this ..

[vba]Function IsWbOpen(wbName as string) as boolean
on error resume next
IsWbOpen = Len(workbooks(wbName).name)
End function[/vba]

[mvidas]

I just figured he had a WB variable already in the other, I use a similar function to what you have there (but one that returns a workbook variable, either the already opened one or the newly opened one).

I think its funny you're talking about wasting resources with a workbook variable here just before suggesting Public variables in the other thread

[Zack Barresse]

Hmm, well if the shoe fits.. I think the big reason I don't use anything except the function like I posted to check if a workbook is open, is because I can't justify doing it any other way than with the two lines.. everything else just seems like a waste now. Go figure.

[mdmackillop]

Now if only a few more million Americans were as careful with the world's limited resources...