the GetOpenFileName Method is, I think, what you are thinking of. Here is a
function I wrote for selecting a file ( I wrote it to be run from Access, so
it creates an Excel object; you can skip that if you are running it in Excel):
Public Function GetFile(Types As Byte, Optional FPath As String, Optional
Prompt As String) As String
' Function to open file selection dialog box, return selected file path
and name
' NOTE: Must include reference to the Microsoft Excel Object Library to
use its
' File dialog box as an object
'
' Types will have bits set according to the types of files being
searched for:
' (note: these were set by me, can be modified or extended)
' 00000001 (AllFiles,&H1) All Files
' 00000010 (TextFiles,&H2) for Text Files,*.txt
' 00000100 (PLMFiles,&H4) for PLM download files,*.rpt
' 00001000 (CSVFiles,&H8) for Comma Separated Values,*.csv
' 00010000 (ExcelFiles,&H10) for Excel Files,*.xls
' 00100000 (AccessFiles,&H20) for Access Files,*.mdb
' 01000000 (&H40) RESERVED
' 10000000 (&H80) RESERVED
' Any other choice, e.g. Types = 0, will give "All Files"
' User can "OR" these choices to get multiple file types
' FPath is the default file path
' Header is what will appear as the dialog box title
' Must declare an object variable as an Excel Application to allow the
use of
' the Excel file selection dialog
' (ONLY if not run in Excel, just use Application object if you are
already in Excel)
Dim AppXL As New Excel.Application
Dim FilText, FilTypes As String
Dim Heading As Variant
AppXL.DefaultFilePath = FPath
If Prompt = "" Then Heading = "SELECT FILE" Else Heading = Prompt
FilText = ""
FilTypes = ""
' Set the FileFilter based on the Types specified
If ((Types And &H20) = &H20) Then FilText = "ACCESS"
If ((Types And &H20) = &H20) Then FilTypes = "*.mdb"
If ((Types And &H10) = &H10) Then
If FilText = "" Then FilText = FilText & "EXCEL" Else _
FilText = FilText & ";EXCEL"
If FilTypes = "" Then FilTypes = "*.xls" Else _
FilTypes = FilTypes & ";*.xls"
End If
If ((Types And &H8) = &H8) Then
If FilText = "" Then FilText = FilText & "Comma-Separated Values"
Else _
FilText = FilText & ";Comma-Separated Values"
If FilTypes = "" Then FilTypes = "*.csv" Else _
FilTypes = FilTypes & ";*.csv"
End If
If ((Types And &H4) = &H4) Then
If FilText = "" Then FilText = FilText & "PLM Download Files" Else _
FilText = FilText & ";PLM Download Files"
If FilTypes = "" Then FilTypes = "*.rpt" Else _
FilTypes = FilTypes & ";*.rpt"
End If
If ((Types And &H2) = &H2) Then
If FilText = "" Then FilText = FilText & "Text Files" Else _
FilText = FilText & ";Text Files"
If FilTypes = "" Then FilTypes = "*.txt" Else _
FilTypes = FilTypes & ";*.txt"
End If
If FilTypes = "" Then FilText = "All Files"
If FilTypes = "" Then FilTypes = "*.*"
FilText = FilText & "," & FilTypes
GetFile = AppXL.GetOpenFilename(FilText, , Heading, False)
AppXL.Quit
Set AppXL = Nothing
End Function