Check if Excel workbook is open in Access (1 Viewer)

[Andre Bruin]

Hi,

I'm importing data from an Excel file via VBA Access. Every 5 seconds Access checks in a folder if an Excel sheet is available.
Now I want that Access first checks if the Excel file is open already and if the file is open, it should be ignored.

thanks in advance,

rg,
André

 

[arnelgp]

if somewhere in your code you already instanciated an excel.application object, you can test if the workbook is open.

dim xlWB as excel.workbook
for each xlWB in xlApp.WorkBooks
if xlWB.Name = "name of excel file"
' workbook is open
end if
next
set xlwb=nothing

 

[Andre Bruin]

I get an error with 'Dim xlWB As excel.workbook'

"The datatype is not defined"

 

[The_Doc_Man]

If the data type is not defined, you have a bad reference to the Excel library.

 

[dbay]

Here is what I use to check if a file is open or not.
Just pass it the Path to the file. If it is Open it will return False.

Public Function Check_Open_File(ByVal STR_Path As String) As Boolean

    Dim INT_Number As Integer

On Error GoTo err_proc
    
    INT_Number = FreeFile()
    Open STR_Path For Input Lock Read As #INT_Number
    Close INT_Number
    
    Check_Open_File = True
    
exit_proc:
On Error Resume Next
    Exit Function

err_proc:
    Check_Open_File = False
    Select Case Err.Number
        Case 53
            'MsgBox ("" & STR_Path & "" & Chr(13) & "" & Chr(13) & "File not Found!"), vbExclamation, "Missing File Error"
        Case 70
            'MsgBox ("" & STR_Path & "" & Chr(13) & "" & Chr(13) & "File is Open!"), vbExclamation, "Open File Error"
    End Select
    Resume exit_proc
    
End Function