Select filenames using VBA in Microsoft Excel

by  About
       

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


Leave a Reply

Your email address will not be published. Required fields are marked *


eight + = 15

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>