Check if excel workbook is open

[gixerp]

How can I check if an excel workbook is open?



I have a Frontend DB with linked tables to a Backend DB. This Backend DB also has a connection to an excel spreadsheet.
On opening, the Frontend DB opens to a default form with various navigation buttons etc, one of which is to "Add a New Asset".
Myself and another user (in another part of the country) have used this several times without any problems. . . until today.
I was working on the excel workbook when the other user tried to Add a new Asset. After pressing the add new asset button the new form opened. . . but then froze. . everything.
The only way to continue was to go into task manager and close Access completely.

On investigation I found that it was because I had the excel workbook open.

I therefore need to add something that checks to see if that (and one or two other) workbook/s is open. . . and tell the user to either close it, or wait till the other user has finished using it.

Being a definite beginner, I have looked for a solution online, but most of the answers were in excel forums and not Access (is Excel & Access vba the same?). . . and didn't really cover my problem anyway.

Any help is much appreciated.

Thanks

[rpeare]

excel and access vba are similar, but the way you reference things within them are not, but that is likely not going to affect the solution you found. if you post the code you found someone can likely pick it apart and adapt it for access.

[ssanfu]

I agree with rpeare....post your code.

In the meantime, you might look at Ken Snell's site http://www.accessmvp.com/kdsnell/EXCEL_MainPage.htm

If you click on the "Exporting" option, scroll down to the header "Write Data From a Recordset into an EXCEL Worksheet using Automation (VBA)", then look for the section of code that has a comment of " ' Establish an EXCEL application object"
There is a check to see if a workbook is open:

<snip>
' Establish an EXCEL application object
On Error Resume Next
Set xlx = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
      Set xlx = CreateObject("Excel.Application")
      blnEXCEL = True
End If
Err.Clear
On Error GoTo 0
<snip>