Export (0) Print
Expand All

Programmatically Selecting Files in Excel for Windows and Excel for the Mac

Office 2010

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:    Microsoft Excel 97 | Microsoft Excel 2000 | Microsoft Excel 2002 | Microsoft Excel 2003 | Microsoft Office Excel 2007 | Microsoft Excel 2010 | Microsoft Excel 2011 for the Mac

Published:   December 2011

Provided by:   Ron de Bruin, Excel MVP | Frank Rice, Microsoft Corporation

Contents

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.

noteNote:

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:

  1. Saves the current directory and change the directory to the folder you set in the MyPath variable.

  2. Opens a browse dialog box with the folder specified by the MyPath variable as active folder and only show .xls files in this folder.

  3. Selects one or more files because the MultiSelect argument is set to True.

  4. 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.

  5. 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

  1. 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.

  2. 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)
    
  3. Type Alt +Q to close the Visual Basic Editor.

  4. 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:

  1. Opens a browse dialog box with the value of MyPath as the active folder and only displays .xls files in this folder.

  2. Selects one or more files because the option for multiple selections is set to True in the Apple Script.

  3. 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.

  4. 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

  1. To open the Visual Basic Editor, click Tools, click Macro, and then click Visual Basic Editor.

  2. To insert a standard module, click Insert, and then click Module.

  3. 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
    
  4. Then press Cmd +Q or click Excel, and then click Close to close the Visual Basic Editor.

  5. Click Tools, click Macro, and then click Macros to open the macro dialog box and run the macro.

    noteNote:

    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.

Community Additions

ADD
Show:
© 2014 Microsoft