Check if a spreadsheet is open from access

[victorialj]

Hi there,

I have been trying several methods of checking if a specific spreadsheet is open in excel before continuing to run. However, whichever one I choose, it continually returns false when it is in fact open, and then fails to close that spreadsheet and that instance of Excel.

The function I am using is:
(with Dim xls As Excel.Application and Dim FileName As String declared in the procedure I call the function from).

Public Function WorkBookIsOpen(FileName, xls) As Boolean

Dim x As Workbook
With xls
On Error Resume Next
Set x = Workbooks(FileName)
If Err = 0 Then WorkBookIsOpen = True Else WorkBookIsOpen = False

End With

End Function

Does anyone know why this is happening? OR any better suggestions?

Thanks
Victoria

 

[rubbernilly]

It might be as simple as replacing this:
Set x = Workbooks(FileName)
with this:
Set x = .Workbooks(FileName)

If not, another possibility...could you iterate the Workbooks Collection and test the name?

Public Function WorkBookIsOpen(FileName, xls) As Boolean

Dim x As Workbook
Dim a as integer

With xls
for a = 0 to ubound(.workbooks())
   debug.print .workbooks(a).name
next a
End With

End Function

If you don't get anything to print, you aren't really getting to the workbooks collection.