Get Open or Save-As Filename

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

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 https://peltiertech.com/ 
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 sHomeDir
End Function 

'' GetSaveAsFileName Replacement Function
'' Jon Peltier https://peltiertech.com/ 
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 sHomeDir
End Function