» Select filenames using VBA in Microsoft Excel

VBA macro tip contributed by Erlandsen Data Consulting offering Microsoft Excel Application development, template customization, support and training solutions The macro below shows how you can let the user select a single filename:

Sub OpenOneFile()
Dim fn As Variant
    fn = Application.GetOpenFilename("Excel-files,*.xls", _
        1, "Select One File To Open", , False)
    If TypeName(fn) = "Boolean" Then Exit Sub 
    ' the user didn't select a file
    Debug.Print "Selected file: " & fn
    Workbooks.Open fn
End Sub

The macro below shows how you can let the user select multiple filenames:

Sub OpenMultipleFiles()
Dim fn As Variant, f As Integer
    fn = Application.GetOpenFilename("Excel-files,*.xls", _
        1, "Select One Or More Files To Open", , True)
    If TypeName(fn) = "Boolean" Then Exit Sub
    For f = 1 To UBound(fn)
        Debug.Print "Selected file #" & f & ": " & fn(f)
        Workbooks.Open fn(f)
        MsgBox ActiveWorkbook.Name, , "Active Workbook Name:"
        ActiveWorkbook.Close False 
        ' close the active workbook without saving any changes
    Next f
End Sub

The macro below shows how you can let the user select a filename for saving a workbook:

Sub SaveOneFile()
Dim fn As Variant
    fn = Application.GetSaveAsFilename("MyFileName.xls", _
        "Excel files,*.xls", 1, "Select your folder and filename")
    If TypeName(fn) = "Boolean" Then Exit Sub
    ActiveWorkbook.SaveAs fn
End Sub