workbook opening

[kevin carter]

i have a workbook containing a lot of data (workbook 1)
what i want to do is call this workbook(workbook 1) from another
workbook.(workbook 2)
problem is the workbook ( workbook 1)containing the data could be open
is it possible to check from the second workbook(workbook 2) in VB code if
the first workbook(workbook 1) is open
and if it is do nothing if it closed allow second workbook to open it


thanks



kevin

 

[Frank Kabel]

Hi
try something like the following:

Sub Public WB_Open(filename as string)
Dim WBook As Workbook
On Error Resume Next
Set WBook = Workbooks(filename)
On Error GoTo 0
If WBook Is Nothing Then
Workbooks.Open Filename:= filename
Set WBook = Workbooks(filename)
End If
end Sub

 

[Bob Phillips]

Hi Kevin,

Here is a simple function to check it

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


To run it, use code like

If Not IsWbOpen("Sip 2004.xls") Then
Workbooks.Open FileName:="C:\myTest\SIP 2004.xls"
End If

Note the different filename formats

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[kev carter]

thanks both

Hi Kevin,

Here is a simple function to check it

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


To run it, use code like

If Not IsWbOpen("Sip 2004.xls") Then
Workbooks.Open FileName:="C:\myTest\SIP 2004.xls"
End If

Note the different filename formats

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)