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
 
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.
Click to expand...

This is a little rough, but it'll give you an idea of how to do it:

Sub TestForOpenFile()

Dim s_WbkName As String
Dim wbk As Workbook

'Note that the workbook name may or may not have
'an extension, depending on your Windows Explorer
'settings. I'd therefore test both.

'If the workbook's not open, an error will
'occur (which we suppress), and wbk will
'remain nothing.

On Error Resume Next
Set wbk = Application.Workbooks("TestWorkbook")
If wbk Is Nothing Then
Set wbk = Application.Workbooks("TestWorkbook.xls")
End If

'Change this to your file
s_WbkName = "C:\Documents and Settings\" _
& "Hank Scorpio\My Documents\Testworkbook.xls"

On Error GoTo 0

If wbk Is Nothing Then

'Make sure that the file is there
'before you try to open it.
If Dir(s_WbkName, _
vbNormal) = "" Then

MsgBox "The file does not exist."

Exit Sub

Else

Workbooks.Open s_WbkName

End If

End If

MsgBox "This is the rest of the macro."

End Sub
 
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.
Click to expand...

Try this:
======================================================================
Dim wb As Workbook
Dim haveFound As Boolean
Const sName As String = "fubar.xls"

haveFound = False
For Each wb In Workbooks
If wb.Name = sName Then
haveFound = True
Exit For
End If
Next wb
MsgBox haveFound
======================================================================

Another common method is to simply refer to the workbook in question by
its name and use the Err object to determine the success:
======================================================================
On Error Resume Next
Set wb = Workbooks(sName)
If Err <> 0 Then
haveFound = False
Else
haveFound = True
End If
On Error GoTo 0
MsgBox haveFound
======================================================================
I prefer the first method.
 
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
Click to expand...
I had the same problem last week. I found a simple way depending if you
want only one person in the workbook at the same time.

There is a property on the workbook ReadOnly. If you use this, you can
detect if someone else has taken a lock on the file.

So...

If SomeWorkbook.ReadOnly = True Then
SomeCodeHere
End If

Cheers,

Mark
 
Back
Top