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
76
77
78
79
80
81
82
83
84
'**********************************************************************************************************
' Name: selectFile
' Author: mielk | 2014-02-27
'
' Comment: Function allows a user to select one or more files.
' This is a wrapping function for the Excel built-in function GetOpenFilename.
' The difference between this function and built-in function [GetOpenFilename]:
' * Function [selectFile] allows to specify the default filepath.
' * Function [selectFile] always False if user cancelled the operation and
' array of strings otherwise, while function [GetOpenFilename] returns
' False if user cancelled the operation, value of String type if a single
' file is selected and array of strings if multiple files are selected.
'
' Parameters:
' defaultPath Optional parameter of String type. It defines what folder the dialog box for
' selecting file will be initialized with.
' Default value for this parameter is empty String.
' title Optional parameter of String type. It defines what title the dialog box for
' selecting file will be initialized with.
' Default value for this parameter is "Select file(s)".
' fileType Optional parameter of String type. It defines what file types user can
' select in the dialog box.
' Default value for this parameter is "Excel files, *.xls; *.xlsm" - that means user
' can select only files with [xls] or [xlsm] extension.
' multiSelect Optional parametr of Boolean type. It defines if user can select more than one
' file.
' Default value for this parameter is True.
'
'
' Returns:
' Variant Function can return two different values:
' * False (Boolean) - if user cancelled selecting files by clicking [Cancel] or by
' closing [Select file...] pop-up.
' * Array of Strings - if user selected at least one file.
' Other than with Excel built-in function GetOpenFilename which return a String
' value for a single file, this function always returns array of strings - even if
' the result contains only one file - in such case result array has only one item,
' but it is still an array.
'
'
' --- Changes log -----------------------------------------------------------------------------------------
' 2014-02-27 mielk Function created.
'**********************************************************************************************************
Public Function selectFile(Optional defaultPath As String, Optional title As String = "Select file(s)", _
Optional fileType As String = "Excel files, *.xls; *.xlsm", _
Optional multiSelect As Boolean = True) As Variant
Const METHOD_NAME As String = "selectFile"
'------------------------------------------------------------------------------------------------------
Dim drive As String * 1
'------------------------------------------------------------------------------------------------------
'Try to change default path used as a start point in [Select file ...] pop-up. If this path ---------|
'doesn't exist, the function ignores it. '|
drive = VBA.Left(defaultPath, 1) '|
'|
'This process is not crucial, so if it fails just ignore it. ------------------------------------| '|
On Error Resume Next '| '|
Call VBA.ChDrive(drive) '| '|
Call VBA.ChDir(defaultPath) '| '|
On Error GoTo 0 '| '|
'------------------------------------------------------------------------------------------------| '|
'|
'----------------------------------------------------------------------------------------------------|
'Use Excel built-in function [GetOpenFilename] with parameters defined by user. ---------------------|
selectFile = Excel.Application.GetOpenFilename(filefilter:=fileType, title:=title, _
multiSelect:=multiSelect) '|
'----------------------------------------------------------------------------------------------------|
'Analyze result returned by the operation above. If the result is of a String type, it means --------|
'user had selected only one file. In such case it needs to be converted into String array with '|
'a single element. '|
If VBA.VarType(selectFile) = vbString Then '|
Dim arr(1 To 1) As String '|
arr(1) = selectFile '|
selectFile = arr '|
End If '|
'----------------------------------------------------------------------------------------------------|
End Function