Problem opening a file

[AmyTaylor]

Hi everyone, i have a piece of basic code that calls a workbook

Workbooks.Open Filename:="\\server1\files\amy\Activity.xls"

This works perfectly, until I navigate away from Activity.xls back t
the main sheet workbook called Main.xls.
What happens is that when I am back in Main.xls and run the above cod
to go back to Activity.xls it create a run-time error 1004 as the fil
is already open.

Does anyone know if I can add some code to see if the file is open, an
it it is activate it, otherwise open the file afresh?

Thanks for your help.

Amy x

 

[dominicb]

Good morning AmyTaylor

The code below will test if a named file is open:

Sub CheckFile()
On Error Resume Next
Set a = Workbooks("\\server1\files\amy\Activity.xls")
If Err = 0 Then
Windows("\\server1\files\amy\Activity.xls").Activate
End If
End Sub

If the file is already open then it will be made the active sheet. If
not, nothing happens.

HTH

DominicB

 

[AmyTaylor]

Thanks Dominic, but this isnt exactly what I want. If the file isnt ope
then I want it to open it, otherwise it just activates the sheet as i
will be already open.

Thanks for your help.
Amx

 

[dominicb]

Hi Amy

I know, I know. I seem to have a real problem reading the last fe
words of a post. Please excuse my silliness and try this instead.

Sub CheckFile()
On Error Resume Next
Set a = Workbooks("\\server1\files\amy\Activity.xls")
If Err = 0 Then
Windows("\\server1\files\amy\Activity.xls").Activate
Exit Sub
End If
Workbooks.Open Filename:="\\server1\files\amy\Activity.xls"
End Sub

Is that any better?

HTH

Dominic

 

[AmyTaylor]

Dominic, thank you again ! x

But.....it always opens the file afresh, and doesnt just activate it i
the file is already open - this really isnt a problem but if a user i
accessing it across the network on a slow line it could cause
problem.
Any ideas ?
Thanks
Amy x