Is worksheet open

[Fredrik Wahlgren]

Hi

I have built a user defined function which will reference a cell in an
external workbook. The idea is to make it easier to consolidate data into a
master sheet. The workbooks that I reference are created by another
application. Every month, a series of workbooks will be generated and the
idea is that the user only needs to tweak a few values in order to get the
data to the master sheet.

I may end up with something like this: '[05-000090-00_200512.xls]Subtask
#1'!$E$4
Before I evaluate this expression, I would like to know whether the workbook
05-000090-00_200512.xls is open

Since this is a UDF, I dont think I can do something like
Set wb = Application.Workbooks("05-000090-00_200512").Activate

How can I test whether the workbook is open?

/Fredrik

 

[Bob Phillips]

Fredrik,

Here is a simple function to test it

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

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Dave Peterson]

One way (with no thought about checking the existence of the worksheet!):

Option Explicit
Function myFunction() As Variant

Dim testWkbk As Workbook

Set testWkbk = Nothing
On Error Resume Next
Set testWkbk = Workbooks("test.xls")
On Error GoTo 0

If testWkbk Is Nothing Then
myFunction = CVErr(xlErrRef)
Else
myFunction = testWkbk.Worksheets("Subtask #1").Range("e1").Value
End If

End Function

John Walkenbach shows some techniques for retrieving values from a closed
workbook:
http://j-walk.com/ss/excel/eee/eee009.txt

Hi

I have built a user defined function which will reference a cell in an
external workbook. The idea is to make it easier to consolidate data into a
master sheet. The workbooks that I reference are created by another
application. Every month, a series of workbooks will be generated and the
idea is that the user only needs to tweak a few values in order to get the
data to the master sheet.

I may end up with something like this: '[05-000090-00_200512.xls]Subtask
#1'!$E$4
Before I evaluate this expression, I would like to know whether the workbook
05-000090-00_200512.xls is open

Since this is a UDF, I dont think I can do something like
Set wb = Application.Workbooks("05-000090-00_200512").Activate

How can I test whether the workbook is open?

/Fredrik

 

[Fredrik Wahlgren]

Bob and Dave, Thank you

/Fredrik