Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Open File(s) using the GetOpenFilename dialog

VBA How To

Open File(s) using the GetOpenFilename dialog

by  Bowers74  Posted    (Edited  )
The following two procedures allow you to select a file (or multiple files) to open by using the GetOpenFilename dialog box:

To open a single file
Code:
Sub OpenSingleFile()
Dim Filter As String, Title As String
Dim FilterIndex As Integer
Dim Filename As Variant
[color green]' File filters[/color]
Filter = "Excel Files (*.xls),*.xls," & _
        "Text Files (*.txt),*.txt," & _
        "All Files (*.*),*.*"
[color green]' Default Filter to *.*[/color]
FilterIndex = 3
[color green]' Set Dialog Caption[/color]
Title = "Select a File to Open"
[color green yellow]' Select Start Drive & Path[/color]
ChDrive ("E")
ChDir ("E:\Chapters\chap14")
With Application
    [color green]' Set File Name to selected File[/color]
    Filename = .GetOpenFilename(Filter, FilterIndex, Title)
    [color green yellow]' Reset Start Drive/Path[/color]
    ChDrive (Left(.DefaultFilePath, 1))
    ChDir (.DefaultFilePath)
End With
[color green]' Exit on Cancel[/color]
If Filename = False Then
    MsgBox "No file was selected."
    Exit Sub
End If
' Open File
Workbooks.Open Filename
MsgBox Filename, vbInformation, "File Opened" [color green]' This can be removed[/color]
End Sub

To open multiple files (or a single file)

Code:
Sub OpenMultipleFiles()
Dim Filter As String, Title As String, msg As String
Dim i As Integer, FilterIndex As Integer
Dim Filename As Variant
[color green]' File filters[/color]
Filter = "Excel Files (*.xls),*.xls," & _
        "Text Files (*.txt),*.txt," & _
        "All Files (*.*),*.*"
[color green]'   Default filter to *.*[/color]
    FilterIndex = 3
[color green]' Set Dialog Caption[/color]
Title = "Select File(s) to Open"
[color green yellow]' Select Start Drive & Path[/color]
ChDrive ("E")
ChDir ("E:\Chapters\chap14")
With Application
    [color green]' Set File Name Array to selected Files (allow multiple)[/color]
    Filename = .GetOpenFilename(Filter, FilterIndex, Title, , True)
    [color green yellow]' Reset Start Drive/Path[/color]
    ChDrive (Left(.DefaultFilePath, 1))
    ChDir (.DefaultFilePath)
End With
[color green]' Exit on Cancel[/color]
If Not IsArray(Filename) Then
    MsgBox "No file was selected."
    Exit Sub
End If
[color green]' Open Files[/color]
For i = LBound(Filename) To UBound(Filename)
    msg = msg & Filename(i) & vbCrLf [color green]' This can be removed[/color]
    Workbooks.Open Filename(i)
Next i
MsgBox msg, vbInformation, "Files Opened"[color green]' This can be removed[/color]
End Sub

I hope that you find this helpful! [thumbsup2]

Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top