Testing for open workbook
 

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



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Testing for open workbook
 

On Sat, 1 Nov 2003 03:43:08 -0500, nigelab
wrote:

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.

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



---------------------------------------------------------
Hank Scorpio
scorpionet who hates spam is at iprimus.com.au (You know what to do.)
* Please keep all replies in this Newsgroup. Thanks! *

Testing for open workbook
 

On Sat, 1 Nov 2003 03:43:08 -0500, nigelab
wrote in
microsoft.public.excel.programming:

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.

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.

--
Michael Bednarek, Brisbane, Australia
http://mcmbednarek.tripod.com/ "POST NO BILLS"

Testing for open workbook
 

On Sat, 01 Nov 2003 03:43:08 -0500, nigelab wrote:

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



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/
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

Testing for open workbook
 

Thanks to all.


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/