GetOpenFilename and GetSaveAsFilename are useful dialogs to use within Excel VBA procedures when your procedure needs to know the filename of a file to open or save. I have encapsulated each into functions that add to their functionality, and make then simpler to use in my procedures.

Note that both GetXxxxFilename functions return file names. They do not open or save any files, merely provide a mechanism to ask the user for the names of the files that the calling procedure will process later.

The GetOpenFilename and GetSaveAsFilename functions as I use them are given by the following syntax; these arguments are optional, and I’ve left out optional arguments that I don’t use. I define a variant named vTemp to accept the return value of the function.

vTemp = Application.GetOpenFilename( _    FileFilter:=sFilter, Title:=sTitle, MultiSelect:=bMulti) vTemp = Application.GetSaveAsFilename( _    FileFilter:=sFilter, InitialFileName:=sName, Title:=sTitle)

Arguments

FileFilter is one or more file type filters. Here are a few examples:

One type of file:
All Files (*.*),*.*

Two types of files:
Text Files (*.txt), *.txt, Add-In Files (*.xla), *.xla

One type of file with multiple allowed extensions:
Visual Basic Files (*.bas; *.txt),*.bas;*.txt

The default filter is All Files (*.*),*.*.

Title is the text that will appear in the title bar of the dialog. The default is “Open” or “Save As”.

strong>MultiSelect allows the user to select multiple files to open in the dialog, if True.

InitialFileName is the suggested file name displayed in the Save As dialog. If you specify a file with an extension that is not included in the active filter, the name appears in double quotes, and I’ve never learned how to remove these quotes.

Function Returns

If the user cancels either dialog, the returned value is a Boolean, False. I test TypeName(vTemp) to make sure the user did not cancel, before I try to do anything with the returned file names.

If the user approves GetSaveAsFilename or GetOpenFilename with MultiSelect False, the returned value is a String, the file path and name of the single indicated file.

If the user approves GetOpenFilename with MultiSelect True, the returned value is Variant(), a Variant Array, with the file path and name of all selected files. This is a one-element array if a single file is selected.

The Enhanced Functions

The enhanced code is shown below. Both functions encapsulate the steps that keep track of the previous current directory, and restore it at the end of the function. The SaveAs function tests whether the filename specified by the user matches the name of an existing file, and if so, it asks whether the existing file should be overwritten.

A few file name functions from File Name Functions are used in these GetXxxFilename functions.

' example usage Sub TestGetOpenFileName()   Dim vFile As Variant   vFile = GOFN("C:\Temp", "Select a file to process", _      "Data Files (*.csv;*.txt),*.csv;*.txt", False)   If TypeName(vFile) = "Boolean" Then    MsgBox "User selected no file to process."  Else    MsgBox "User wants to process '" & vFile & "'"  End If End Sub ' example usage Sub TestGetSaveAsFileName()   Dim vFile As Variant   vFile = GSAFN("C:\Temp", "Save Workbook As", _      "MyWorkbook.xls", "Excel Workbooks (*.xls), *.xls")   If TypeName(vFile) = "Boolean" Then    MsgBox "User declined to save the file."  Else    MsgBox "User wants to save the file as '" & vFile & "'"  End If End Sub '' - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - '' GetOpenFileName Replacement Function'' Jon Peltier http://peltiertech.com/WordPress/ Function GOFN(Optional sPath As String, Optional sTitle As String, _    Optional sFilter As String, Optional bMulti As Boolean) As Variant   Dim vTemp As Variant  Dim sHomeDir As String   ' save current directory  sHomeDir = CurDir  If Len(sPath) > 1 Then    ' change to new directory if it is specified    ChDrive sPath    ChDir sPath  End If   vTemp = Application.GetOpenFilename( _      FileFilter:=sFilter, Title:=sTitle, MultiSelect:=bMulti)   If TypeName(vTemp) = "Boolean" Then    ' canceled by user    ' check for Boolean in calling procedure>  End If   GOFN = vTemp   ' restore current directory  ChDrive sHomeDir  ChDir sHomeDirEnd Function '' - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - '' GetSaveAsFileName Replacement Function'' Jon Peltier http://peltiertech.com/WordPress/ Function GSAFN(Optional sPath As String, Optional sTitle As String, _    Optional sName As String, Optional sFilter As String) As Variant   Dim vTemp As Variant  Dim sHomeDir As String  Dim lYNC As Long  Dim sMessage As String   ' save current directory  sHomeDir = CurDir  If Len(sPath) > 1 Then    ' change to new directory if it is specified    ChDrive sPath    ChDir sPath  End If   Do    ' loop to check for duplicate file name    vTemp = Application.GetSaveAsFilename( _        FileFilter:=sFilter, InitialFileName:=sName, Title:=sTitle)     If TypeName(vTemp) = "Boolean" Then      ' canceled by user      ' check for Boolean in calling procedure      Exit Do    End If     If Not FileExists(vTemp) Then Exit Do     sMessage = "A file named '" & FullNameToFileName(CStr(vTemp))    sMessage = sMessage & "' exists in the directory     " & vbNewLine    sMessage = sMessage & "'" & FullNameToPath(CStr(vTemp)) & "'.     "    sMessage = sMessage & vbNewLine & vbNewLine    sMessage = sMessage & "Do you want to OVERWRITE the existing file?     "     lYNC = MsgBox(sMessage, vbExclamation + vbYesNoCancel, "Duplicate File Name")    Select Case lYNC      Case vbYes        ' overwrite existing file with desired name        Exit Do      Case vbNo        ' user does not want to overwrite        ' repeat loop, check again for duplicate      Case vbCancel        ' canceled by user        ' check for Boolean in calling procedure        vTemp = False        Exit Do    End Select  Loop   GSAFN = vTemp   ' restore current directory  ChDrive sHomeDir  ChDir sHomeDirEnd Function

Related Posts:

• File Name Functions
• Robust VBA Save-As Technique
• Enhanced Export Chart Procedure
• Validation Functions
• Apply Chart Formatting to Other Charts
• UDF to Calculate an Arbitrary Formula
• Export Chart as Image File
• Build an Excel Add-In 5 – Tie the Code Together
• Build an Excel Add-In 4 – Create the Dialog
• Announcing the Box and Whisker Chart Utility