Checking if a file is open before attempting to open it

[Talat]

Hi,

I have the following short code to auto open a macro file when teh main fiel is is opened.

-----------------------------------------------------------------
Sub Auto_Open()
'
' Auto_Open Macro


Workbooks.Open Filename:="G:\PUBS\PP-MS\INVOICES\SALEMAC.xlm", Notify:= _
False
ActiveWindow.Visible = False
End Sub

---------------------------------------------------

I want to change this code so that it frst checks if teh file is already open, and only open it if it is not. Can anyone help with it?

Thanks.

Talât

[Hack]

If it is already open does it throw an error?

[Talat]

Yes.

It first warns that the file is open and re-opening may cause problems. On reply to say do not open displays Microsoft Visual basic error Box

Runtime error :1004
Method 'Open' of object -Workbooks' failed

[Hack]

Then simply trap for the error

Sub Auto_Open()
On Error GoTo ErrTrap
'
' Auto_Open Macro


Workbooks.Open Filename:="G:\PUBS\PP-MS\INVOICES\SALEMAC.xlm", Notify:= _
False
ActiveWindow.Visible = False
Exit Sub
ErrTrap:
If Err.Number = 1004 Then
   Msgbox "Workbook already open"
Else
   'a different error occured - unlikely but possible - so display it
   Msgbox Err.Number & " " & Err.Description
End If
End Sub

[Talat]

Thanks Hack.

That's perfect. Work fine!

Much obliged.

T.

[Hack]

Error Trapping is something that should be used throughout your application.

Run time errors that pop your users directly back to the desktop tend to become very, very annoying and can be prevented with error traps.