Testing for open workbook

  • Thread starter Thread starter nigelab
  • Start date Start date
N

nigelab

Hi.

I need to create vba code within a excel macro to detemine if a
workbook is already open. If it is, I want to continue with the macro.
If it isnt, I want to open the workbook and then continue with the
macro.

I am sure this must be pretty easy. I'm just struggling to work out
how to do it.

Can anyone give me some suitable code.

Many thanks in advance.

Nigel
 
Nigel;

Try this.

Function WorkbookOpen(WorkBookName As String) As Boolean
' returns TRUE if the workbook is open
WorkbookOpen = False
On Error GoTo WorkBookNotOpen
If Len(Application.WorkBooks(WorkBookName).Name) > 0 Then
WorkbookOpen = True
Exit Function
End If
WorkBookNotOpen:
End Function

Example:
If Not WorkbookOpen("MyWorkbookName.xls") Then
Workbooks.Open "MyWorkbookName.xls"
End If

Mark.
 
Hi Nigel

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

Sub test()
MsgBox IsOpen("Book1")
MsgBox IsOpen("MyPornArchive")
End Sub
 
I need to create vba code within a excel macro to detemine if a
workbook is already open.
Click to expand...

Please don't post the same question to multiple newsgroups. By the
time the two people who answered your question in this group had done
so, you already had two answers in the programming group. That means
that you wasted at least two people's time. If you MUST post to
multiple groups, please send a SINGLE cross-posted message so that
everyone can see the followups.
 
Another way ...

Dim oWB As Workbook

On Error Resume Next
Set oWB = Workbooks("Schedule.xls")
On Error GoTo 0
If Not oWB Is Nothing Then
MsgBox oWB.FullName & " is open"
End If



--

HTH

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