runtime error 1004
hi...
am opening the file through the GetOpenFilename method. I am facing an error in of the cases... Like say or example i try and open a file with the XYZ.xls...which is already open. the system generates a mess saying "reopening will cause any changes you made to be discarded. do you want to reopen XYZ.xls?" if i click yes...it works fine by reopening the file but when i click to No...Runtime error 1004 comes: "Method open of object workbooks failed " and its giving an error here in the code: Workbooks.Open Filename:=sFilename how do i do the error handling in this case. thanks a lot Monika |
runtime error 1004
Hi,
You can trap this error and look for it in an error handler. Please see this example: Sub OpenWorkBook() Dim varOpenFile As Variant Dim objWorkbook As Workbook On Error GoTo OpenWorkBook_Err ' Display open dialog. varOpenFile = Application.GetOpenFilename(MultiSelect:=False) ' If user cancels the open workbook dialog exit sub. If varOpenFile = False Then GoTo OpenWorkBook_Exit ' Open workbook. ' If err number 1004 occurs here it will be trapped in the error handler. Set objWorkbook = Workbooks.Open(varOpenFile) ' Etc.... OpenWorkBook_Exit: Exit Sub GetOpenWorkBooks_Err: Select Case Err.Number Case 1004 ' If user answers no to the dialog to open the file read-only just exit sub or display a warning to the user. MsgBox "The workbook you want to open is already opened. Please try again.", vbCritical, Application.Name Resume GetOpenWorkBook_Exit Case Else ' In case of any other error. MsgBox Err.Number & " " & Err.Source & " " & Err.Description , vbCritical, Application.Name Resume GetOpenWorkBook_Exit End Select End Sub HTH /Ulrik "monika" skrev i meddelandet ... hi... am opening the file through the GetOpenFilename method. I am facing an error in of the cases... Like say or example i try and open a file with the XYZ.xls...which is already open. the system generates a mess saying "reopening will cause any changes you made to be discarded. do you want to reopen XYZ.xls?" if i click yes...it works fine by reopening the file but when i click to No...Runtime error 1004 comes: "Method open of object workbooks failed " and its giving an error here in the code: Workbooks.Open Filename:=sFilename how do i do the error handling in this case. thanks a lot Monika |
runtime error 1004
A small typo went into in the code - please use this corrected version
instead: Sub OpenWorkBook() Dim varOpenFile As Variant Dim objWorkbook As Workbook On Error GoTo OpenWorkBook_Err ' Display open dialog. varOpenFile = Application.GetOpenFilename(MultiSelect:=False) ' If user cancels the open workbook dialog exit sub. If varOpenFile = False Then GoTo OpenWorkBook_Exit ' Open workbook. ' If err number 1004 occurs here it will be trapped in the error handler. Set objWorkbook = Workbooks.Open(varOpenFile) ' Etc.... OpenWorkBook_Exit: Exit Sub OpenWorkBooks_Err: Select Case Err.Number Case 1004 ' If user answers no to the dialog to open the file read-only just exit sub or display a warning to the user. MsgBox "The workbook you want to open is already opened. Please try again.", vbCritical, Application.Name Resume OpenWorkBook_Exit Case Else ' In case of any other error. MsgBox Err.Number & " " & Err.Source & " " & Err.Description , vbCritical, Application.Name Resume OpenWorkBook_Exit End Select End Sub HTH /Ulrik "Ulrik Gustafsson" wrote in message ... Hi, You can trap this error and look for it in an error handler. Please see this example: Sub OpenWorkBook() Dim varOpenFile As Variant Dim objWorkbook As Workbook On Error GoTo OpenWorkBook_Err ' Display open dialog. varOpenFile = Application.GetOpenFilename(MultiSelect:=False) ' If user cancels the open workbook dialog exit sub. If varOpenFile = False Then GoTo OpenWorkBook_Exit ' Open workbook. ' If err number 1004 occurs here it will be trapped in the error handler. Set objWorkbook = Workbooks.Open(varOpenFile) ' Etc.... OpenWorkBook_Exit: Exit Sub GetOpenWorkBooks_Err: Select Case Err.Number Case 1004 ' If user answers no to the dialog to open the file read-only just exit sub or display a warning to the user. MsgBox "The workbook you want to open is already opened. Please try again.", vbCritical, Application.Name Resume GetOpenWorkBook_Exit Case Else ' In case of any other error. MsgBox Err.Number & " " & Err.Source & " " & Err.Description , vbCritical, Application.Name Resume GetOpenWorkBook_Exit End Select End Sub HTH /Ulrik "monika" skrev i meddelandet ... hi... am opening the file through the GetOpenFilename method. I am facing an error in of the cases... Like say or example i try and open a file with the XYZ.xls...which is already open. the system generates a mess saying "reopening will cause any changes you made to be discarded. do you want to reopen XYZ.xls?" if i click yes...it works fine by reopening the file but when i click to No...Runtime error 1004 comes: "Method open of object workbooks failed " and its giving an error here in the code: Workbooks.Open Filename:=sFilename how do i do the error handling in this case. thanks a lot Monika |
runtime error 1004
thanks tremendously... this is exactly what i was looking for...and i tried
it works fine... infact this gave me a little idea as to how to manage the error handling. reg Monika "Ulrik Gustafsson" wrote in message ... A small typo went into in the code - please use this corrected version instead: Sub OpenWorkBook() Dim varOpenFile As Variant Dim objWorkbook As Workbook On Error GoTo OpenWorkBook_Err ' Display open dialog. varOpenFile = Application.GetOpenFilename(MultiSelect:=False) ' If user cancels the open workbook dialog exit sub. If varOpenFile = False Then GoTo OpenWorkBook_Exit ' Open workbook. ' If err number 1004 occurs here it will be trapped in the error handler. Set objWorkbook = Workbooks.Open(varOpenFile) ' Etc.... OpenWorkBook_Exit: Exit Sub OpenWorkBooks_Err: Select Case Err.Number Case 1004 ' If user answers no to the dialog to open the file read-only just exit sub or display a warning to the user. MsgBox "The workbook you want to open is already opened. Please try again.", vbCritical, Application.Name Resume OpenWorkBook_Exit Case Else ' In case of any other error. MsgBox Err.Number & " " & Err.Source & " " & Err.Description , vbCritical, Application.Name Resume OpenWorkBook_Exit End Select End Sub HTH /Ulrik "Ulrik Gustafsson" wrote in message ... Hi, You can trap this error and look for it in an error handler. Please see this example: Sub OpenWorkBook() Dim varOpenFile As Variant Dim objWorkbook As Workbook On Error GoTo OpenWorkBook_Err ' Display open dialog. varOpenFile = Application.GetOpenFilename(MultiSelect:=False) ' If user cancels the open workbook dialog exit sub. If varOpenFile = False Then GoTo OpenWorkBook_Exit ' Open workbook. ' If err number 1004 occurs here it will be trapped in the error handler. Set objWorkbook = Workbooks.Open(varOpenFile) ' Etc.... OpenWorkBook_Exit: Exit Sub GetOpenWorkBooks_Err: Select Case Err.Number Case 1004 ' If user answers no to the dialog to open the file read-only just exit sub or display a warning to the user. MsgBox "The workbook you want to open is already opened. Please try again.", vbCritical, Application.Name Resume GetOpenWorkBook_Exit Case Else ' In case of any other error. MsgBox Err.Number & " " & Err.Source & " " & Err.Description , vbCritical, Application.Name Resume GetOpenWorkBook_Exit End Select End Sub HTH /Ulrik "monika" skrev i meddelandet ... hi... am opening the file through the GetOpenFilename method. I am facing an error in of the cases... Like say or example i try and open a file with the XYZ.xls...which is already open. the system generates a mess saying "reopening will cause any changes you made to be discarded. do you want to reopen XYZ.xls?" if i click yes...it works fine by reopening the file but when i click to No...Runtime error 1004 comes: "Method open of object workbooks failed " and its giving an error here in the code: Workbooks.Open Filename:=sFilename how do i do the error handling in this case. thanks a lot Monika |
All times are GMT +1. The time now is 04:30 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com