Test if Excel Workbook is open

  • Thread starter Thread starter pmartin1960
  • Start date Start date
P

pmartin1960

I am familiar with Excel VBA and now I wish to repeat some of my coding
of Excel from Access.

In Excel, I run the following code to test if a workbook is open:

Public Function WorkbookIsOpen(ByVal sWbkNameA As String) As Boolean
Dim wbkTemp As Workbook

On Error Resume Next
Set wbkTemp = Workbooks(sWbkNameA) ' Returns an error if not open
WorkbookIsOpen = (Err.Number = 0)
On Error GoTo 0
End Function

This code has always worked fine for me in Excel, but is failing when I
run it from Access. I have tried qualifying Workbook as
Excel.Workbook, and have added Excel to the Reference Library. When I
run the above code, if the relevant workbook is open, Err.Number should
be 0 but returns 9. Any suggestions appreciated.

Paul Martin
Melbourne, Australia
 
In addition to what Arvin mentions, you also need

Set objXL = New Excel.Application

and change

Set wbkTemp = Workbooks(sWbkNameA)

to

Set wbkTemp = objXL.Workbooks(sWbkNameA)

Your declaration of

Dim wbkTemp As Workbook

would be better as

Dim wbkTemp As Excel.Workbook

(even though I don't believe any other library includes an object type named
Workbook)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)
 
As Doug Steele just pointed out to me, my answer was incomplete. The code
should read:

Dim objXL As Excel.Application
Set objXL = New Excel.Application
or
Dim objXL As Object
Set objXL = CreateObject("Excel.Application")

besides dim'ing the variable, you need to set it.

Thanks Doug,
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access
 
Thanks for your input, guys.

I had done all of the above suggestions, and when I came into work this
morning, it worked fine. I don't know why it wasn't working on Friday.

Thanks again

Paul
 
Back
Top