Hi,
I have a workbook that is a template for charting data selected from another
workbook. The other workbook might be one of several different ones so I do
not want to link to any one specific workbook.

I have a macro that pulls a subsection of data from the other workbook into
the charting workbook.

When the macro begins I want test to see if the other workbook open is
already open. If it is then the macro proceeds and pulls int he data. It
the workbook is not open then I want that workbook to open.

How do I test to see if a workbook is open in the application? If the
result could be a boolean then I could use and IF statement to do the test
and decide whether to open the worksheet.
Thanks.

--
Mark Mesarch
School of Natural Resources
University of Nebraska-Lincoln

Hi Mark

Function IsOpen(sName As String) As Boolean
On Error Resume Next
IsOpen = Len(Workbooks(sName).Name)
End Function

Sub test()
MsgBox IsOpen("Book2.xls")
End Sub

HTH. Best wishes Harald

"MMesarch" skrev i melding
...
Hi,
I have a workbook that is a template for charting data selected from
another
workbook. The other workbook might be one of several different ones so I
do
not want to link to any one specific workbook.

I have a macro that pulls a subsection of data from the other workbook
into
the charting workbook.

When the macro begins I want test to see if the other workbook open is
already open. If it is then the macro proceeds and pulls int he data. It
the workbook is not open then I want that workbook to open.

How do I test to see if a workbook is open in the application? If the
result could be a boolean then I could use and IF statement to do the test
and decide whether to open the worksheet.
Thanks.

--
Mark Mesarch
School of Natural Resources
University of Nebraska-Lincoln

Hi,
You could just set a workbook variable with error checking as Harald proposed.
If you also need to check on the path to differentiate the case when a book
with same name is already open but from a different path (in which case you
won't be able to open your book unless you first close that one), you can use
the following macro:
'-----------------------------------------------
' -If book is not open then returns 0
' -if book is open then returns 1
' (if path not specified in Bookname, does not differentiate on path)
' -if a book is not open but a book with same name but another path is
open then 2
' (in this last case: won't be able to open the book)
Function IsBookOpen(BookName As String) As Long
Dim wkb As Workbook
Dim sep As String 'path separator
Dim path As String 'book path
Dim name As String ' book filename
Dim pos As Long 'position of last 'sep' in bookname

sep = Application.PathSeparator

'if BookName contains pathSeparator then extract path and name
If BookName Like ("*" & sep & "*") Then
pos = InStrRev(BookName, sep)
path = Left(BookName, pos)
name = Right(BookName, Len(BookName) - pos)
Else
path = ""
name = BookName
End If

On Error Resume Next
Set wkb = Workbooks(name)
If Err < 0 Then 'not open
IsBookOpen = 0
Else 'open
If path = "" Then 'open and since path not specified, it assumes it
is the one.
IsBookOpen = 1
ElseIf UCase(wkb.path & sep) = UCase(path) Then 'and same bookName
& path
IsBookOpen = 1
Else 'and same bookname but
different path
IsBookOpen = 2
End If
End If
End Function
'----------------------------------------------------------
Regards,
Sébastien
<http://www.ondemandanalysis.com


"MMesarch" wrote:

Hi,
I have a workbook that is a template for charting data selected from another
workbook. The other workbook might be one of several different ones so I do
not want to link to any one specific workbook.

I have a macro that pulls a subsection of data from the other workbook into
the charting workbook.

When the macro begins I want test to see if the other workbook open is
already open. If it is then the macro proceeds and pulls int he data. It
the workbook is not open then I want that workbook to open.

How do I test to see if a workbook is open in the application? If the
result could be a boolean then I could use and IF statement to do the test
and decide whether to open the worksheet.
Thanks.

--
Mark Mesarch
School of Natural Resources
University of Nebraska-Lincoln

"sebastienm" skrev i melding
...

If you also need to check on the path to differentiate the case when a
book
with same name is already open but from a different path (...)

Good point, Sébastien. Thanks.

Best wishes Harald

Harald and Sebastien, Thanks that did the trick
--
Mark Mesarch
School of Natural Resources
University of Nebraska-Lincoln


"Harald Staff" wrote:

Hi Mark

Function IsOpen(sName As String) As Boolean
On Error Resume Next
IsOpen = Len(Workbooks(sName).Name)
End Function

Sub test()
MsgBox IsOpen("Book2.xls")
End Sub

HTH. Best wishes Harald

"MMesarch" skrev i melding
...
Hi,
I have a workbook that is a template for charting data selected from
another
workbook. The other workbook might be one of several different ones so I
do
not want to link to any one specific workbook.

I have a macro that pulls a subsection of data from the other workbook
into
the charting workbook.

When the macro begins I want test to see if the other workbook open is
already open. If it is then the macro proceeds and pulls int he data. It
the workbook is not open then I want that workbook to open.

How do I test to see if a workbook is open in the application? If the
result could be a boolean then I could use and IF statement to do the test
and decide whether to open the worksheet.
Thanks.

--
Mark Mesarch
School of Natural Resources
University of Nebraska-Lincoln