Is file open


Since function isFileOpen uses some non-built VBA functions, they also must be included in your code for the function to work properly.

Otherwise the following error will occur: Compile error: Sub or Function not defined.

Required functions are listed below. You can get to each function's source code by clicking its name:

When adding the functions above to your VBA project, make sure you haven't done it before. If there are two different public functions with the same name in a single VBA project, the following compilation error is thrown: Compile error: Ambiguous name detected: function_name.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
'**********************************************************************************************************
' Name:                 isFileOpen
' Author:               mielk | 2014-02-26
'
' Comment:              Function checks if the given file is open in the specified Excel instance.
'
' Parameters:
'   filePath            The path of a file to be checked.
'   excelInstance       Excel instance to be searched through for the given file.
'                       If this parameter is ommitted, the current Excel instance is used.
'
' Returns:
'   Boolean             True - if the given Excel file is open in the specified Excel instance.
'                       False - otherwise.
'
'
' --- Changes log -----------------------------------------------------------------------------------------
' 2014-02-26        mielk       Function created.
'**********************************************************************************************************
Public Function isFileOpen(filepath As String, Optional excelInstance As Excel.Application) As Boolean
    Const METHOD_NAME As String = "isFileOpen"
    '------------------------------------------------------------------------------------------------------
    Dim xls As Excel.Application
    Dim fileName As String:             fileName = getFileName(filepath)
    Dim wkb As Excel.Workbook
    '------------------------------------------------------------------------------------------------------


    'Find out what Excel application instance should be used for checking. ------------------------------|
    If excelInstance Is Nothing Then                                                                    '|
        Set xls = Excel.ThisWorkbook.Application                                                        '|
    Else                                                                                                '|
        Set xls = excelInstance                                                                         '|
    End If                                                                                              '|
    '----------------------------------------------------------------------------------------------------|


    'Try to get the reference to the specified file from the given Excel instance. If nothing -----------|
    'is assigned, it means there is no such file open in this Excel instance.                           '|
    On Error Resume Next                                                                                '|
    Set wkb = xls.Workbooks(fileName)                                                                   '|
    On Error GoTo 0                                                                                     '|
    '----------------------------------------------------------------------------------------------------|


    '----------------------------------------------------------------------------------------------------|
    If wkb Is Nothing Then                                                                              '|
        isFileOpen = False                                                                              '|
    Else                                                                                                '|
        isFileOpen = compareString(wkb.FullName, filepath)                                              '|
    End If                                                                                              '|
    '----------------------------------------------------------------------------------------------------|


End Function