Check whether workbook, being accessed within MS Access via VBA, is open

Question

I am opening a workbook from Access via VBA using the following code. I need to check whether that workbook is already open: if already open then do nothing, otherwise open it with CreateObject method. Code is as below:

Dim oAppa As Object
Dim filea As String
filea = CurrentProject.Path & "\List.xlsm"
Set oAppa = CreateObject("Excel.Application")
oAppa.Visible = True
Set owb_a = oAppa.workbooks.Open(filea, ReadOnly:=True)

Here while opening it, I need to check whether it's already opened. Please help me out, Thanks in advance.

see my answer here: http://stackoverflow.com/questions/22150619/how-to-get-data-from-an-already-opened-excel-workbook-with-a-word-macro/22150839#22150839 — Dmitry Pavliv, Mar 07 '14 at 08:39
Hi simoco,Thanks for quick reply, But I need to check if the workbook with same name is open already then dont do anything, dont create new instance. with Getobject its creating instance and oAppa does not stay to Nothing — Swapnil Chavan, Mar 07 '14 at 08:58

score 0 · Answer 1 · edited May 23 '17 at 12:10

You could loop through all open workbooks, but this would take a while...

A simple solution, taken from https://stackoverflow.com/a/9373914/2829009

Option Explicit

Sub Sample()
    Dim Ret

    Ret = IsWorkBookOpen("C:\myWork.xlsx")

    If Ret = True Then
        MsgBox "File is open"
    Else
        MsgBox "File is Closed"
    End If
End Sub

Function IsWorkBookOpen(FileName As String)
    Dim ff As Long, ErrNo As Long

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

    Select Case ErrNo
    Case 0:    IsWorkBookOpen = False
    Case 70:   IsWorkBookOpen = True
    Case Else: Error ErrNo
    End Select
End Function

score 0 · Answer 2 · answered Mar 07 '14 at 15:24

I just answered a near identical question about Word. The answer would be the same (or very similar):

' Handle Error In-Line
On Error Resume Next
Set objExcel = GetObject(, "Excel.Application")

If Err.Number = 429 Then
    'If we got an error, that means there was no Excel Instance
    Set objExcel = CreateObject("Excel.Application")
Else
    Msgbox ("You're going to need to close any open Excel docs before you can perform this function.", vbOK)
    Exit Sub
End If

'Reset Error Handler
On Error GoTo 0

Of course, you can tweak what you want to do if the instance already exists, rather than just exiting the Sub.

Check whether workbook, being accessed within MS Access via VBA, is open

2 Answers2