Test if a workbook is open already

[Kieran1028]

Follow-up on my last thread (which was answered, thus the new thread
but same project...)

At the beginning of my code, I use getopenfilename and then open
workbook. I want a check (if/then?) between the getopenfilename an
the open command, that will check to see if the filename entered i
already open, if it is, skip the open command and continue with th
rest of the code. Make sense? This is what I have:

Sub get1degdata()
Dim fname As Variant
Dim Wkbk As Workbook
Dim wksht As Worksheet
fname = Application.GetOpenFilename("Excel files(*.xls),*.xls")
Set Wkbk = Workbooks.Open(fname)
Wkbk.Activate
MsgBox fname

 

[Lenny_821]

This is what I use;

Dim wbk As Workbook
Dim old_book As Workbook
Set old_book = ActiveWorkbook
On Error Resume Next
Set wbk = Workbooks("Test.xls")
On Error GoTo 0
If wbk Is Nothing Then
Workbooks.Open Filename:= _
"C:\Documents\test.xls"
old_book.Activate
Exit Sub
End If

Lenny

 

[Chip Pearson]

Try something like the following:

Dim WB As Workbook
Dim IsWorkbookOpen As Boolean
For Each WB In Workbooks
If WB.FullName = FName Then
IsWorkbookOpen = True
Exit For
End If
Next WB

If IsWorkbookOpen = False Then
Set WB = Workbooks.Open(FName)
End If



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Kieran1028]

Lenny,

But if you have several open workbooks, how would you test if just ONE
of them was the name entered in getopenfilename? I think your method
assumes that oldbook is the only other open book, right?

 

[Ron de Bruin]

You need Chip's function also

Function IsWorkbookOpen(WBName As String) As Boolean
On Error Resume Next
IsWorkbookOpen = CBool(Len(Workbooks(WBName).Name))
End Function

 

[Ron de Bruin]

Oops

Forget it<g>

 

[Chip Pearson]

That works only with the workbook name, not the full file name,
as the OP needed.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Kieran1028]

Chip, your code worked (again).

Although I did need to add the line
WB.Activate
After the ISWorkbookOpen = True line.

Thanks (again)!