runtime error 1004

  • Thread starter Thread starter monika
  • Start date Start date
M

monika

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
 
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
 
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
 
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
 
Back
Top