Checking if a Workbook is Already Open

This one doesn't require you to make a function

For i = 1 To Workbooks.Count
If Workbooks(i).Name = "File_name" Then
i = Workbooks.Count + 2
End If
Next i
If i = Workbooks.Count + 3 Then
Msgbox("File_name is already open")
End If

Hi Richard,

I resolved this issue a little while ago. with the code below which seems to work well. It checks one of 4 files and opens a pop up box to say it is open by me which further on in the script stops the macro.

I always like looking at how other people handle problems mine are normally rather cumbersome, the do the job but there is normally a slicker way!!
thanks
Andrew

' GetMachineName
Range("A31").Select
ActiveCell.FormulaR1C1 = "=LEFT(R[-1]C,5)"

'file_name = Range("a1")
machine_name = Range("a31")

If machine_name = "MC01" Then
Dim Ret1
Ret1 = IsWorkBookOpen("R:MC01.xls")
If Ret1 = True Then
MsgBox "MC01 log file is open. The Macro will stop running please contact Andrew"
Exit Sub
End If
ElseIf machine_name = "MC02" Then
Dim Ret2
Ret2 = IsWorkBookOpen("R:MC02.xls")
If Ret2 = True Then
MsgBox "MC02 log file is open. The Macro will stop running please contact Andrew"
Exit Sub

If machine_name = "MC03" Then
Dim Ret3
Ret1 = IsWorkBookOpen("R:MC03.xls")
If Ret3 = True Then
MsgBox "MRC03 log file is open. The Macro will stop running please contact Andrew"
Exit Sub
End If
ElseIf machine_name = "MRC04" Then
Dim Ret4
Ret4 = IsWorkBookOpen("R:MC04.xls")
If Ret2 = True Then
MsgBox "MC04 log file is open. The Macro will stop running please contact Andrew"
Exit Sub
End If
End If
End If
End If

Checking if a file is open is very useful - but the above method relies on redirecting the error handler; which could be considered intrusive.
I offer the following:
Public Function AlreadyOpen2(sFname As String) As Boolean
' Scan the open workbooks to see if sFname is already open
' True = The file filename is already open
Dim wkb As Workbook
AlreadyOpen2 = False 'To force the default result
For Each wkb In Workbooks
If wkb.Name = sFname Then AlreadyOpen2 = True
Next wkb

End Function

What do you think?
Richard

Hi Allen, When I saw this tip I thought, Excellent this is just what I need. As the Engineer I often get caught out looking at the data produced in a spreadsheet when one of my Operators is trying to add more data in via a macro. the macro actually opens the file and if it is already open (by me) then it errors. I would love a way of checking the file is shut right at the start of the macro so that the operator can abort the macro and ring me up and ask me to shut the spreadsheet down.
Is there any way of adapting this code to do this.
Thanks

Andrew