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