Testing if a file is open

[Guest]

Hi,

Can someone provide some direction on how to test if an Excel file is
currently open....not worried about whether it is active, just currently open.

I'm requesting the information from VBA for Word 2000. I've tried the
following:

if Excel.application.workbooks(vFileName).open = true
....
End if

It isn't working!

Any ideas would be very much appreciated.

Thanks,
Jille

 

[Chip Pearson]

Try either of the following functions.

This is used if you pass only the name (e.g., "Book2.xls") to the function,
with no file folder/path information.
Function IsWorkbookOpen(WBName As String) As Boolean
On Error Resume Next
IsWorkbookOpen = CBool(Len(Excel.Application.Workbooks(WBName).Name))
End Function

This is used for either just the name (e.g., "Book2.xls") or the full file
name (e.g., "C:\Test\Book2.xls").
Function IsWorkbookOpen2(FileName As String) As Boolean
Dim WB As Excel.Workbook
For Each WB In Excel.Application.Workbooks
If (StrComp(WB.Name, FileName, vbTextCompare) = 0) Or _
(StrComp(WB.FullName, FileName, vbTextCompare) = 0) Then
IsWorkbookOpen2 = True
Exit Function
End If
Next WB
End Function


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
(email address is on the web site)

 

[Tom Ogilvy]

Dim bk as Excel.Workbook

sPath = "C:\MyFolder\"
vFileName = "MyBook.xls"

On error Resume Next
set bk = Excel.Application.Workbooks(vFileName)
On Error goto 0
if not bk is nothing then
msgbox bk.Name & " is open"
else
set bk = Excel.Application.Workbooks.Open(sPath & vFileName)
end if