Since function openWorkbook 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
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
'**********************************************************************************************************
' Name: openWorkbook
' Author: mielk | 2014-02-26
'
' Comment: Function to open the Excel file with the given filepath in the specified Excel
' application instance.
'
' Parameters:
' filePath The path of a file to be opened.
' readOnly Optional parameter of Boolean type.
' It determines if the file should be open in read-only mode.
' Default value for this parameter is False, so file is open in write-read mode.
' excelInstance Excel instance to be used when opening the specified file.
' createIfNotExists Optional parameter of Boolean type.
' It determines if the file should be created if it not exists yet.
'
'
' Returns:
' Excel.Workbook Reference to the Excel file with the specified filepath.
'
'
' Exceptions:
' PathNotFoundException Thrown if the given path has not been found.
'
' --- Changes log -----------------------------------------------------------------------------------------
' 2014-02-26 mielk Function created.
'**********************************************************************************************************
Public Function openWorkbook(filepath As String, Optional readOnly As Boolean = False, _
Optional excelInstance As Excel.Application, _
Optional createIfNotExists As Boolean = False) As Excel.Workbook
Const METHOD_NAME As String = "openWorkbook"
'------------------------------------------------------------------------------------------------------
Dim xls As Excel.Application
Dim bAlerts As Boolean
'------------------------------------------------------------------------------------------------------
'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 '|
'----------------------------------------------------------------------------------------------------|
'Switch off the alerts when opening workbook. Alerts settings will be restored at the end of --------|
'this function. '|
With xls '|
bAlerts = .DisplayAlerts '|
.DisplayAlerts = False '|
End With '|
'----------------------------------------------------------------------------------------------------|
If isFileOpen(filepath, xls) Then
Set openWorkbook = xls.Workbooks(getFileName(filepath))
Else
If fileExists(filepath) Then
Set openWorkbook = xls.Workbooks.Open(filepath, readOnly:=readOnly, UpdateLinks:=False)
End If
End If
'Restoring [DisplayAlerts] setting. -----------------------------------------------------------------|
'It is not critical, so if any error occurred just skip it and continue. '|
On Error Resume Next '|
xls.DisplayAlerts = bAlerts '|
On Error GoTo 0 '|
'----------------------------------------------------------------------------------------------------|
End Function