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 to the main sheet workbook called Main.xls.
What happens is that when I am back in Main.xls and run the above code to go back to Activity.xls it create a run-time error 1004 as the file is already open.

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

Thanks for your help.

Amy xx

[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 open then I want it to open it, otherwise it just activates the sheet as it will be already open.

Thanks for your help.
Amx x

[dominicb]

Hi Amy

I know, I know. I seem to have a real problem reading the last few 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

DominicB

[AmyTaylor]

Dominic, thank you again ! x

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