GetOpenFilename

Posted on by Dick Kusleika

The GetOpenFilename method of the Application object is an easy way to allow the user of your program to select a file. It displays the standard open file dialog, but you can make some minor modifications to it. The syntax from help is:

FileFilter is a string that determines what will be in the “Files of type” box of the dialog. You can specify as many file types as you want. If you omit this argument, the user will have one choice, namely All Files (*.*). To specify certain file types, the string has to be pairs of file filters separated by commas. For instance,

will put two filters in the “Files of type” box because there are two pairs of filters. The first string of the pair is supposed to be descriptive and can be pretty much anything you want. The second string of the pair determines how VBA filters the files. You can omit the first string altogether if you like, but you still must include the comma. To include multiple file types in the same filter, specify multiple filters in the second string and separate them with a semi-colon.

Here there is just one pair and the second string of the pair shows multiple filters. All files ending with dlt OR omb will be shown.

FilterIndex determines which of your FileFilters will show by default. If you omit, or screw up, this argument, it shows the first one.

Title is the one (Windows) customization you can make to the dialog. If you’re trolling for a specific type of file, you can title the dialog “Open Monthly Progress Report” or some such phrase.

If you use a Mac, you can customize the button with your own text.

MultiSelect determines whether the user can select more than one file. If this is False, the variable you use to hold the file name should be a String data type. If it’s True, the variable needs to be a Variant because it will return an array of filenames that were selected – even if it’s only one file.

GetOpenFilename returns the full path and name of the file(s) selected. It doesn’t actually open the file, you have to do that yourself. This makes it a very versatile tool. You can use it to have the user select a file that you don’t intend to open, but that you need to identify in your program. Unfortunately for us Windows users, the Button will still say Open.

Here are two examples: a single select and a multi-select. In the first example, the sub checks to see if the file name is False. If it is, that means the user clicked the Cancel button. In the second example, the sub determines if the variable is an array. If it isn’t, that means the user clicked the Cancel button. Otherwise, the sub loops through the array and opens the files.

24 thoughts on “GetOpenFilename”

  1. Re: the FileFilter argument, here’s something from my notes (probably not my words):

    The help states, “In Windows, this string 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. Each separate pair is listed in the Files of type drop-down list box.” It gives an Example for text files and Excel add-ins but doesn’t elaborate. MDSN doesn’t seem to have a list and I can’t find anything in the registry. So here’s short list of two:

    “MS Access Databases (*.mdb), *.mdb”
    “Text Files (*.txt), *.txt”

    Any idea where a definitive list may be found?

    Jamie.

    –

  2. Bob Phillips posted a most useful version using Win32 APIs that allows the use of wildcards e.g.

    With cFileOpen
    .FileName = “Ex*.xls”
    .FileType = “Excel Files”
    .DialogTitle = “Class File Browser Demo”
    .MultiFile = “N”

    The full code can be found in the Usenet archive:

    http://groups.google.com/groups?selm=%23TRlxm%23JEHA.2808%40TK2MSFTNGP11.phx.gbl

    It’s a rare case where a typo in a comment is crucial but the line should read:

    ‘Class file – named clsGetOpenFileName

    Jamie.

    –

  3. “Any idea where a definitive list may be found?”

    List of what? Do you want the file filters that Excel uses when you File>Open? Or do you want a list of all possible file filters that can be used? The former I couldn’t find. The latter is undefined. The first part of the string can be anything you want. The second part seems to have to be and astricks, and period, and at least one letter or number. * doesn’t work, *. doesn’t work, and anything other than * one the left the of period doesn’t work. There doesn’t seem to be a limit on the lenght after the period. This works:

    Application.GetOpenFilename(“Gobbledegook, *.abcdefghijklmnopqrstuvwxyz”)

  4. A huge list of file extensions:
    http://filext.com/
    but note that extensions are not reserved. You can save any file with any extension, and many apps use the same. Do a search for .frm to see this.

  5. > This works:
    >
    > Application.GetOpenFilename(“Gobbledegook, *.abcdefghijklmnopqrstuvwxyz”)

    Dick, Thanks for clarifying. I had mistakenly thought the entire string had to verbatim what e.g. Excel uses. I’m now going to correct my notes! Jamie.

  6. Using GetOpenFileName how do you make it display file details rather than just the list.

  7. How can I use wildcards with the *file name* of the filedialog command and then return the file name *with* wildcards, instead of manually selecting multiple files?

    Eg. in the filedialog box the user enters *.doc to select all word documents; the string “*.doc” shall be returned to the sub (instead of “.doc, .doc, …”). Unfortunately, this command only allows selecting existing files without the use of any wildcards.

    Any ideas how to solve this? Thanks in advance!

  8. I have a GetOpenFilename with MultiSelect:=True which works perfectly if it is the only module in my Excel work book. But If I copy the exact same code (CRTL/A – CTRL/C) to an empty module (CTRL/V) in a file which has a heap of other code then it refuses to do the multislect but will only return a single file. Any bright ideas as to what may be going on in the depths of VBA ?

  9. After a deal more searching and several experiments I find the the following code using FileDialog seems to work reliably.

    Function GetMultiFileNames(Cmt_names() As String) As Long
    ‘ Test Multifile
    Dim first_file, last_file, cur_file, Fcount, i As Long
    ‘ This works in a similar way to Application.GetOpenFilename() but with out the random bug
    ‘ Open the file dialog
    With Application.FileDialog(msoFileDialogFilePicker)
    .AllowMultiSelect = True ‘ allow multi file selection
    .Filters.Clear ‘ clear existing filters
    .Filters.Add “CMT or BOM Files”, “*.xls”, 1 ‘ position 1
    .Filters.Add “All Files”, “*.*”, 2 ‘ position 2
    .FilterIndex = 1 ‘ use the first as default
    .Title = “Files to Load” ‘ at top
    .ButtonName = “Load” ‘ on save/open button
    If .Show = True Then
    ‘ Display paths of each file selected
    Fcount = .SelectedItems.Count
    ReDim Cmt_names(Fcount)
    For i = 1 To .SelectedItems.Count
    Cmt_names(i) = .SelectedItems(i) ‘ get each file name
    Debug.Print “GetMultiFileNames”, Fcount, i, Cmt_names(i)
    Next i
    Else
    Fcount = 0 ‘ Cancelled
    End If
    End With
    GetMultiFileNames = Fcount
    End Function

    Happy coding

  10. im having problems with the new Office 2003 Filedialog functionality.

    If I ctrl-click to choose a number of files, the returned list does not match the order in which I clicked on them.

    This used to work in Office 2000/97.

    Any thoughts?

    troy

  11. Hi,
    i’m working on macintosh and i’m wondering why the command multiselect provokes an error when it is set to TRUE whereas it works well when it is set to FALSE

    Thanks in advance

  13. I want to add a filter for a file type WITHOUT an extention.
    For example, “results”, which is a result file of another program, is needed for my code, but I cannot open it because it has no ext. as .txt, .csv etc.
    Is there any way to get it, since filter “All files,*.*” will only show files with extentions?
    Thanks in advance.

  14. Is there a further explanation of the Function above? what is the CMT_names input expected?

  15. I have the openfiles working fine, I just want to be able to filter the files in the initial view.

    We have circa 2200 datasheets in the library at the moment, continually growing. These are all sorted by manufacturer and category etc, but take for example the S200 series breakers – I have 200 datasheets in this directory. When I give the user the dialog to open the file, I want to show them just the files containing 202-K3 in the filename, so they should only get one, maybe 2 files, not 200.

    If I am doing this manually, when the open files dialog comes up, I type in *202-K3*, and it shows me only the files I want. I want a way to have the GetOpenFilename dialog open up with the filter *202-K3* already active.

    Is this possible?

  16. Can you help to start the GetOpenFileName start at a specific shared folder over a TCP-IP network?

  17. I am wondering if there is a similar way of using the wildcard & openfilename in conjunction to open a partially known file name. As an example :

    File is named for example, C:Results2feb07archiving_results_175246.csv
    (with the final 5 digit extension being the time the file was saved).

    I am trying to find a way to open the file via vba script but I would like to do it without the code knowing what the time stamp is ie. as long as it starts (C:Results2feb07archiving_results_) and ends in (.csv). So using an unknown string to plug the gap.

    I have been racking my brain for ages over this so any help would be greatly appreciated. Is it even possible ?

  18. Paul,
    Like you I would like to be able to use a standard MS-DOS wildcard like “A*.xls” or “PROJ*.*”.
    The GetOpenFilename works fine if the file prefix is “*” (e.g. “*.xls”) but if you change this at all it seems to go haywire.

    Here’s a kludge that works for me. It uses the filesearch property to get a list of files, then it presents them to the user one at a time. No doubt it could be improved –

    ===================================================================
    TempString1 = “Proj209*.*”
    CR_Filename = “”

    With Application.FileSearch
    .NewSearch
    .LookIn = CreditDir
    .Filename = TempString1
    If .Execute(SortBy:=msoSortByFileName, SortOrder:=msoSortOrderAscending) > 0 Then
    For xxCounter = 1 To .FoundFiles.Count
    TempString2 = “File # ” & xxCounter & ” of ” & .FoundFiles.Count _
    & Chr(13) & .FoundFiles(xxCounter)
    If MsgBox(TempString2, vbYesNo) = vbYes Then
    CR_Filename = .FoundFiles(xxCounter)
    Exit For
    End If
    Next xxCounter
    Else
    MsgBox “There were no files found.”
    End If
    End With

  21. hi,

    I would like to write a VBA code that will open the file for me from a given directory but before doing so, it will ask me which Folder and later which document I want to see, and I will have to insert the name of the folder,

    Hence except of the reular GetOpen code – how should i design that code

    Thank you for any help in advance,

    j

  23. Thank you soooooooooo much for the OpenMultipleFiles example! Tremendously helpful to me. I couldn’t figure how to use the Multiselect:=True, but now it’s working – awesome thanks!

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.