PDA

View Full Version : Open workbook if not open



Djblois
06-30-2006, 07:45 AM
I want to change this code to work only if it is not open already

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

mvidas
06-30-2006, 07:54 AM
Djblois,

When I need to do this, I use a workbook variable to test it:'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 IfAnd afterwards, you can refer to that book simple by using WB, so it is a win-win in my opinion.
Matt

Djblois
06-30-2006, 08:04 AM
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
06-30-2006, 08:06 AM
Sure thing, you can create a boolean variable (takes up almost no memory) to store whether it was open or not: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 IfMatt

Zack Barresse
06-30-2006, 10:22 AM
Why use a variable and waste resources like that??

I use this ..

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

mvidas
06-30-2006, 10:31 AM
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
06-30-2006, 10:59 AM
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
07-01-2006, 01:28 AM
Now if only a few more million Americans were as careful with the world's limited resources... :devil2: