checking if workbook is open before accessing

[David Goodall]

Hi,
I've developed a Userform which adds data to several workbooks depending on
the choice made in combobox. The macro is as follows:

Dim appExcel As Excel.Application
Dim lastrow As Excel.Range
Dim Choice as Workbook

Set appExcel = Application

appExcel.DisplayAlerts = False
appExcel.ScreenUpdating = False

consultant = cbConsultant.Text

If consultant = "Persons name" then
Set Choice = appExcel.Workbooks.Open(FileName:="C:\test1.xls")
ElseIf consultant = "Person2" Then
Set etc.....
end if
Set lastrow = Choice.Worksheets("sheet1").Range("a65536").End(xlUp)
etc......

This works fine but my problem is that if another user is in test.xls the
program
stops and I get a file save as dialog.

I would like to build some form of error message so that the user is told
who is currently using test1.xls and once the other user has exited
test1.xls the macro then continues.

I have noticed the on Error and Resume functions but I'm not sure how to
implement them.

Many thanks

David

 

[Jean-Paul Viel]

Hi

Dim appExcel As Excel.Application
Dim lastrow As Excel.Range
Dim Choice As Workbook

On Error GoTo ErrorStop

Set appExcel = Application

appExcel.DisplayAlerts = False
appExcel.ScreenUpdating = False

consultant = cbConsultant.Text

If consultant = "Persons name" Then
Set Choice = appExcel.Workbooks.Open(Filename:="C:\test1.xls")
ElseIf consultant = "Person2" Then
'Set etc.....
End If
Set lastrow = Choice.Worksheets("sheet1").Range("a65536").End(xlUp)
'etc......
Exit Sub
ErrorStop:

MsgBox "Your Info"