Programmatically Selecting Files in Excel for Windows and Excel for the Mac
Summary: Learn about the GetOpenFilename method in Microsoft Excel for Microsoft Window and Microsoft Excel for the Mac. Also see how to overcome limitations of this method for the Mac.
Applies to: Excel 2010 | Office 2010 | SharePoint Server 2010 | VBA
Published: December 2011
Provided by: Ron de Bruin, Excel MVP | Frank Rice, Microsoft Corporation
Contents
Introducing the GetOpenFileName Method
Details of the GetOpenFilename Method
Differences between the Windows and Mac Implementations
Using the GetOpenFilename Method in Windows
GetOpenFilename Method Workaround for Excel 2011 on a Mac
Running the Correct Macro in Windows or on a Mac
Conclusion
Additional Resources
Introducing the GetOpenFileName Method
Microsoft Excel for Mac 2011 enables you to analyze, organize, and manage all your data and lists. You can then easily save, edit, and share that information online. A common task for developers is to create code that programmatically enables the users to select one or more files in a dialog box. To do that, you can use the Application.GetOpenFilename method. This method displays the standard Open dialog box and gets a file name from the user without actually opening any files. Typically, the file name(s) are returned to a variable and then used later in the macro.
The GetOpenFilename method is used on computers running Excel for Microsoft Windows and Excel for the Macintosh (Mac). However, there are differences in the way that the method is implemented on each operating system. These differences are described in this article as well as a workaround for using the method on the Mac.
Details of the GetOpenFilename Method
As stated previously, the GetOpenFilename method displays the standard Open dialog box and retrieves a file name specified from a filtered list provided by the user. The following table describes the parameters of the method:
Name |
Required or Optional |
Data Type |
Description |
|---|---|---|---|
FileFilter |
Optional |
Variant |
A string specifying file filtering criteria. |
FilterIndex |
Optional |
Variant |
Specifies the index numbers of the default file filtering criteria, from 1 to the number of filters specified in FileFilter. If this argument is omitted or greater than the number of filters present, the first file filter is used. |
Title |
Optional |
Variant |
Specifies the title of the dialog box. If this argument is omitted, the title is Open. |
ButtonText |
Optional |
Variant |
Used by the Mac only. |
MultiSelect |
Optional |
Variant |
If set to True, multiple file names can be selected. A setting of False allows only one file name to be selected. The default value is False. |
The data passed in the FileFilter argument consists of pairs of file filter strings followed by the MS-DOS wildcard file filter specification, with each part and each pair separated by commas. For example, the following string specifies filters for text files: Text Files (*.txt),*.txt.
Differences between the Windows and Mac Implementations
There are differences in the way that the GetOpenFilename method is implemented in Microsoft Windows and in the Mac. As shown in the previous table, the ButtonText parameter is only available for the Mac. Additionally, the FileFilter and MultiSelect parameters are not available for the Mac. Without these parameters, the GetOpenFileName is not very useful for the Mac. The Apple Script workaround described later in this article enables you to get the same functionality.
Setting the folder that opens when you call GetOpenFilename method works differently on the Mac than on Windows. In Windows, you use ChDrive and ChDir to set this folder. These arguments will not work on the Mac. The Apple Script workaround uses a line of code to set this folder.
Note
If you use Microsoft Excel 2002 or higher for Windows, you can also use Application.FileDialog method. Additionally, the GetOpenFileName method will work in Microsoft Excel 97 and Microsoft Excel 2000.
The following section demonstrates how the GetOpenFilename method is used on a computer running Windows.
Using the GetOpenFilename Method in Windows
In the following example, you see an example of using the GetOpenFilename method on a computer running Windows. The code sample performs the following tasks:
Saves the current directory and change the directory to the folder you set in the MyPath variable.
Opens a browse dialog box with the folder specified by the MyPath variable as active folder and only show .xls files in this folder.
Selects one or more files because the MultiSelect argument is set to True.
Opens each selected file one at the time and displays a message box with the file name. You can replace the MsgBox line with your own code as desired.
Closes the file without saving. You can set the SaveChanges argument to True to save the file.
To add the GetOpenFilename method to Excel for Windows
Add a standard module to the Microsoft Visual Basic for Applications (VBA) project in your workbook. Type Alt+F11 to open the Visual Basic Editor, click Insert, and then click Module.
Paste or type the following macro into the module.
Sub Select_File_Or_Files_Windows()
Dim SaveDriveDir As String
Dim MyPath As String
Dim Fname As Variant
Dim N As Long
Dim FnameInLoop As String
Dim mybook As Workbook
' Save the current directory.
SaveDriveDir = CurDir
' Set the path to the folder that you want to open.
MyPath = Application.DefaultFilePath
' You can also use a fixed path.
'MyPath = "C:\Users\Ron de Bruin\Test"
' Change drive/directory to MyPath.
ChDrive MyPath
ChDir MyPath
' Open GetOpenFilename with the file filters.
Fname = Application.GetOpenFilename( _
FileFilter:="Excel 97-2003 Files (*.xls), *.xls", _
Title:="Select a file or files", _
MultiSelect:=True)
' Perform some action with the files you selected.
If IsArray(Fname) Then
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
For N = LBound(Fname) To UBound(Fname)
' Get only the file name and test to see if it is open.
FnameInLoop = Right(Fname(N), Len(Fname(N)) - InStrRev(Fname(N), Application.PathSeparator, , 1))
If bIsBookOpen(FnameInLoop) = False Then
Set mybook = Nothing
On Error Resume Next
Set mybook = Workbooks.Open(Fname(N))
On Error GoTo 0
If Not mybook Is Nothing Then
MsgBox "You opened this file : " & Fname(N) & vbNewLine & _
"And after you press OK, it will be closed" & vbNewLine & _
"without saving. You can replace this line with your own code."
mybook.Close SaveChanges:=False
End If
Else
MsgBox "We skipped this file : " & Fname(N) & " because it is already open."
End If
Next N
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End If
' Change drive/directory back to SaveDriveDir.
ChDrive SaveDriveDir
ChDir SaveDriveDir
End Sub
Function bIsBookOpen(ByRef szBookName As String) As Boolean
' Contributed by Rob Bovey
On Error Resume Next
bIsBookOpen = Not (Application.Workbooks(szBookName) Is Nothing)
End Function
When you want to add more filters, you can use the following code in the macro to filter on .xls and .csv files.
Fname = Application.GetOpenFilename( _
FileFilter:="XLS Files (*.xls),*.xls,CSV Files (*.csv),*.csv", _
Title:="Select a file or files", _
MultiSelect:=True)
Type Alt +Q to close the Visual Basic Editor.
Type Alt + F8 to open the macro dialog box and run the macro.
GetOpenFilename Method Workaround for Excel 2011 on a Mac
As described in the previous sections, using the GetOpenFileName method in Excel 2011 is not an optimal option because of its limitations. However, you can use a combination of VBA code and Apple Script to accomplish the same action. To do that, you must build an Apple Script string and run that with the VBA MacScript function as demonstrated next. The code sample performs the following tasks:
Opens a browse dialog box with the value of MyPath as the active folder and only displays .xls files in this folder.
Selects one or more files because the option for multiple selections is set to True in the Apple Script.
Opens each selected file, one at the time, and displays a message box with the file name. You can replace the MsgBox line with your own code to accomplish what you want in each file.
Closes the file without saving. You can set the SaveChanges argument to True to save the file.
To select one or more files in Excel for the Mac
To open the Visual Basic Editor, click Tools, click Macro, and then click Visual Basic Editor.
To insert a standard module, click Insert, and then click Module.
Paste or type the following macro and function into the module.
Sub Select_File_Or_Files_Mac()
Dim MyPath As String
Dim MyScript As String
Dim MyFiles As String
Dim MySplit As Variant
Dim N As Long
Dim Fname As String
Dim mybook As Workbook
On Error Resume Next
MyPath = MacScript("return (path to documents folder) as String")
'Or use MyPath = "Macintosh HD:Users:Ron:Desktop:TestFolder:"
' In the following statement, change true to false in the line "multiple
' selections allowed true" if you do not want to be able to select more
' than one file. Additionally, if you want to filter for multiple files, change
' {""com.microsoft.Excel.xls""} to
' {""com.microsoft.excel.xls"",""public.comma-separated-values-text""}
' if you want to filter on xls and csv files, for example.
MyScript = _
"set applescript's text item delimiters to "","" " & vbNewLine & _
"set theFiles to (choose file of type " & _
" {""com.microsoft.Excel.xls""} " & _
"with prompt ""Please select a file or files"" default location alias """ & _
MyPath & """ multiple selections allowed true) as string" & vbNewLine & _
"set applescript's text item delimiters to """" " & vbNewLine & _
"return theFiles"
MyFiles = MacScript(MyScript)
On Error GoTo 0
If MyFiles <> "" Then
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
MySplit = Split(MyFiles, ",")
For N = LBound(MySplit) To UBound(MySplit)
' Get the file name only and test to see if it is open.
Fname = Right(MySplit(N), Len(MySplit(N)) - InStrRev(MySplit(N), Application.PathSeparator, , 1))
If bIsBookOpen(Fname) = False Then
Set mybook = Nothing
On Error Resume Next
Set mybook = Workbooks.Open(MySplit(N))
On Error GoTo 0
If Not mybook Is Nothing Then
MsgBox "You open this file : " & MySplit(N) & vbNewLine & _
"And after you press OK it will be closed" & vbNewLine & _
"without saving, replace this line with your own code."
mybook.Close SaveChanges:=False
End If
Else
MsgBox "We skipped this file : " & MySplit(N) & " because it Is already open."
End If
Next N
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End If
End Sub
Function bIsBookOpen(ByRef szBookName As String) As Boolean
' Contributed by Rob Bovey
On Error Resume Next
bIsBookOpen = Not (Application.Workbooks(szBookName) Is Nothing)
End Function
Then press Cmd +Q or click Excel, and then click Close to close the Visual Basic Editor.
Click Tools, click Macro, and then click Macros to open the macro dialog box and run the macro.
Note
Another option to run the macro in Excel 2011 is to add the Developer tab to the ribbon. Start Excel and click Excel on the Menu bar. Click Preferences from the menu. When the Excel Preferences window opens, click Ribbon. When the Ribbon page opens, scroll down the Customize list and select Developer. Click OK to save the changes.
This code only filters on xls files. Other options include:
xlsx: org.openxmlformats.spreadsheetml.sheet
xlsm: org.openxmlformats.spreadsheetml.sheet.macroenabled
xlsb: com.microsoft.Excel.sheet.binary.macroenabled
xls: com.microsoft.Excel.xls
csv: public.comma-separated-values-text
docx: org.openxmlformats.wordprocessingml.document
docm: org.openxmlformats.wordprocessingml.document.macroenabled
doc: com.microsoft.word.doc
pptx: org.openxmlformats.presentationml.presentation
pptm: org.openxmlformats.presentationml.presentation.macroenabled
ppt: com.microsoft.powerpoint.ppt
Running the Correct Macro in Windows or on a Mac
If you have both Windows and Mac users and you want to programmatically search for files in each operating system, you can add both of the previous macros into your Excel project and then use the following macros to test for the specific operating system and then run the correct macro.
Sub WINorMAC()
' Test for the operating system.
If Not Application.OperatingSystem Like "*Mac*" Then
' Is Windows.
Call Select_File_Or_Files_Windows
Else
' Is a Mac and will test if running Excel 2011 or higher.
If Val(Application.Version) > 14 Then
Call Select_File_Or_Files_Mac
End If
End If
End Sub
Sub WINorMAC_2()
' Test the conditional compiler constants.
#If Win32 Or Win64 Then
' Is Windows.
Call Select_File_Or_Files_Windows
#Else
' Is a Mac and will test if running Excel 2011 or higher.
If Val(Application.Version) > 14 Then
Call Select_File_Or_Files_Mac
End If
#End If
End Sub
Conclusion
In this article, you learned about the GetOpenFilename method and then different ways it is implemented in Windows and Mac versions of Excel. You also saw a workaround to overcome the limitations of the method on the Mac. Given this information, you will be able to make reasonable decisions about which methods will work in your applications.
Additional Resources
Find more information on the topics discussed in this article at the following locations.