runtime 1004 error

[monika]

I am trying to open a workbook which is already open and i
get the runt-time error '1004' that 'the document with the
name 'XYZ.xls' is already open. You cannot open two
documents with the same name, even if the documents are in
different folders. To eopn the second document, either
close the document that's currently open, or rename one of
the documents.'

hwo can i trap this error and display my own customized
error, instead of my macro stopping inbetween

thanka in advance.
monika

 

[Bob Phillips]

Hi Monika,

Here is the function I use

Function IsWbOpen(FileName As String) As Boolean
On Error Resume Next
IsWbOpen = CBool(Len(Workbooks(FileName).Name))
End Function


Call like this

myFile = "bob.xls")
If IsWbOpen(myFile) Then
MsgBox "The file " & myFile & " is already open"
Ende If

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Frank Kabel]

Hi
try something like the following:

sub foo()
dim fname as string
dim path as string
Dim WBook As Workbook

path = "C:\"
fname = "filename.xls"
On Error Resume Next
Set WBook = Workbooks(fname)
On Error GoTo 0
If WBook Is Nothing Then
Workbooks.Open Filename:= path & fname
Set WBook = Workbooks(filename)
else
msgbox "Workbook was open"
End If
end Sub