Test if a workbook is open already
 

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

Test if a workbook is open already
 

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

Kieran1028 Wrote:
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
.
.
.
Thanks,
Kiera

--
Lenny_82
-----------------------------------------------------------------------
Lenny_821's Profile: http://www.excelforum.com/member.php...fo&userid=1517
View this thread: http://www.excelforum.com/showthread.php?threadid=31944

Test if a workbook is open already
 

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" wrote in
message ...

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 a
workbook. I want a check (if/then?) between the
getopenfilename and
the open command, that will check to see if the filename
entered is
already open, if it is, skip the open command and continue with
the
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

Test if a workbook is open already
 

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?


--
Kieran1028
------------------------------------------------------------------------
Kieran1028's Profile: http://www.excelforum.com/member.php...o&userid=15678
View this thread: http://www.excelforum.com/showthread...hreadid=319442

Test if a workbook is open already
 

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



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Chip Pearson" wrote in message ...
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" wrote in message ...

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 a
workbook. I want a check (if/then?) between the getopenfilename and
the open command, that will check to see if the filename entered is
already open, if it is, skip the open command and continue with the
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

Test if a workbook is open already
 

Oops

Forget it<g

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Ron de Bruin" wrote in message ...
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



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Chip Pearson" wrote in message ...
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" wrote in message ...

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 a
workbook. I want a check (if/then?) between the getopenfilename and
the open command, that will check to see if the filename entered is
already open, if it is, skip the open command and continue with the
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

Test if a workbook is open already
 

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


"Ron de Bruin" wrote in message
...
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



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Chip Pearson" wrote in message
...
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" wrote
in message ...

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 a
workbook. I want a check (if/then?) between the
getopenfilename and
the open command, that will check to see if the filename
entered is
already open, if it is, skip the open command and continue
with the
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

Test if a workbook is open already
 

Chip, your code worked (again).

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

Thanks (again)!


--
Kieran1028
------------------------------------------------------------------------
Kieran1028's Profile: http://www.excelforum.com/member.php...o&userid=15678
View this thread: http://www.excelforum.com/showthread...hreadid=319442