Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with handling on error when file is already open
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with handling on error when file is already open
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 Frank wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with handling on error when file is already open
Thank you for the solution. It worked fine.
"Die_Another_Day" wrote: 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 Frank wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with handling on error when file is already open
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 "Frank" ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error Handling - Check a file isn't already open before overwritin | Excel Discussion (Misc queries) | |||
Error handling problem | Excel Discussion (Misc queries) | |||
workbooks.open and error handling | Excel Programming | |||
Error Handling problem | Excel Programming | |||
Error Handling Problem | Excel Programming |