VBA - If file is open then , else

[george hart]

Hi

The code below opens a file and does some stuff -works fine. However, I want to check whether the file is open first. If it is, then I need a message box to advise "File is open by "Name", ty again in two minutes.

Workbooks.Open Filename:= _
"C:\Documents and Settings\HartG\My Documents\Projects\Sickness\Ops Employee LookUp.xls"
Windows("Absence Macro-Catering.xls").Activate

Any ideas most apreciated

 

[Weaver]

Put this function in your module

Function isfileopen(ByVal fstr As String) As Boolean
    On Error Resume Next
    isfileopen = Len(Workbooks(fstr).Name) > 0
End Function

If the file is open, you get true, otherwise it'll return false

Don't use the full file path, but you will need to include the .xls, although you could probably change this.

HTH

 

[george hart]

Great help, many thanks