Problem with handling on error when file is already open

[Guest]

I have tried to make a event handler to view a warning message, but before it
displayed, this system message pops up first: "<Filename> is already open.
Reopening will cause any changes you made to be discarded. Do you want to
reopen <filename>?"

How can I avoid the system message and only display my own message?

Regards

Frank Krogh

_________________________

Option Explicit
Sub ChangeDateFormat()

Dim strFileName As Variant
Dim wkbk As Workbook
Dim showMsg As String

strFileName = Application.GetOpenFilename("Report (*.xls),*.xls")
'Application.ScreenUpdating = False
If strFileName <> False Then
On Error GoTo ErrorHandler ' Enable error-handling routine.
Set wkbk = Workbooks.Open(Filename:=strFileName)
With wkbk.Worksheets(1)
' Do something
End With
End If
Exit Sub ' Exit to avoid handler.
ErrorHandler: ' Error-handling routine.
showMsg = MsgBox("File is already open. Please close and try again",
vbExclamation, "File already open")
Exit Sub

End Sub

 

[Die_Another_Day]

Frank, "Set" the workbook to your variable before opening, then test to
see if it is open.
On Error Resume Next
Set wkbk = Workbooks(strFileName)
If Not wkbk Is Nothing Then
'Trigger Msg
Else
'Not Open
End If

HTH

Charles Chickering

 

[Guest]

Thank you for the solution. It worked fine.

Frank, "Set" the workbook to your variable before opening, then test to
see if it is open.
On Error Resume Next
Set wkbk = Workbooks(strFileName)
If Not wkbk Is Nothing Then
'Trigger Msg
Else
'Not Open
End If

HTH

Charles Chickering

 

[NickHK]

Frank,
Or if you that someone else has the file open, you can try to open it
Exclusive first.
If that fails, you know it is already open.

Dim FileNum as long
dim MyFile as string
dim WB as workbook

myfile="C:\Whatever.xls"
Filenum=freefile
on error resume next
Open MyFile For Input Read Lock As FileNum

Select case err.number
case 0
'OK, so close and open normally
close filenum
Set WB=Workbooks.open(myfile)
case ?? 'Forget the number fpor a locked file, but you can test

case else
'Decide what to do
end select

Or maybe you can call a function in this
http://www.dr-hoiby.com/WhoLockMe/index.php

Haven't used it so can't tell you much.


NickHK