Solved: IsFileOpen and WorkbookIsOpen giving differing results

[xluser2007]

Hi All,

I am currently running a macro to open workbooks, but by first checking whether the workbook is currently open.

In order to test, I have actually opened the tetsing workbook.

I am then using the following functions to test for it being OPEN:

By Bob's (XLD's) great KB entry:

[vba]Function IsFileOpen(FileName As String)
Dim iFilenum As Long
Dim iErr As Long

On Error Resume Next
iFilenum = FreeFile()
Open FileName For Input Lock Read As #iFilenum
Close iFilenum
iErr = Err
On Error GoTo 0

Select Case iErr
Case 0: IsFileOpen = False
Case 70: IsFileOpen = True
Case Else: Error iErr
End Select

End Function[/vba]
From John walkenbach's great site:

[vba]Private Function WorkbookIsOpen(wbname) As Boolean
' Returns TRUE if the workbook is open
Dim x As Workbook
On Error Resume Next
Set x = Workbooks(wbname)
If Err = 0 Then WorkbookIsOpen = True _
Else WorkbookIsOpen = False
End Function
[/vba]
The former returns a TRUE, which is correct, the latter returns a FALSE which is incorrect. Could anyone please explain why this may be occurring and not the latter function saying TRUE?

It should be noted that the file I'm testing is an Excel workbook open in the same instance as the workbook from which I am testing it.

Any help appreciated.

[GTO]

Try:

[vba]
Sub callit()
Dim myPath As String
myPath = ThisWorkbook.Path & "\"

'works
MsgBox WorkbookIsOpen("2009-04-03 155520.xls")

'does not
MsgBox WorkbookIsOpen(myPath & "2009-04-03 155520.xls")
End Sub
[/vba]

You probably had the path included?

Mark

[xluser2007]

Try:
You probably had the path included?

Mark

Great spot Mark, that was the issue.

Thanks !

[chrismc]

Just had to say that the function you included for checking if a workbook is already open is great.