Can I display an open file dialog from a macro

[Guest]

I would like to have a macro display one of the standard open file dialogs.
When the user selects a file and clicks "Open" (or whatever) I would like
to have the file name returned to the macro so it can use it.

Thanks so much for any help.

 

[Rick Rothstein]

You can use Application.GetOpenFilename, but you should look this up in the
help files as there are some optional parameters you can specify.

 

[Gary Brown]

'/==========================================/
' Sub Purpose: Examples of using the FileDialog
' msoFileDialogOpen = 1
' msoFileDialogSaveAs = 2
' msoFileDialogFilePicker = 3
' msoFileDialogFolderPicker = 4
' Note: ONLY difference between
' 'msoFileDialogFilePicker' and 'msoFileDialogOpen'
' is that FilePicker button says 'OK' while
' FileDialogOpen button says 'Open'
'
Public Sub FileDialogExamples()
'
Dim strPick As String

On Error GoTo err_Sub

'- - - - - - - - - - - - - - - - - - - -
'Allow user to select file(s)
With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = False 'allow only one file to be chosen
.InitialFileName = Left(ActiveWorkbook.FullName, _
Len(ActiveWorkbook.FullName) - Len(ActiveWorkbook.name))
.Title = "This is a test of File Picking..."
.Filters.Clear
.Filters.Add "Excel Files", "*.xls", 1
.Filters.Add "Text Files", "*.csv, *.txt, *.prn", 2
.Filters.Add "ALL Files", "*.*", .Filters.Count + 1
.FilterIndex = 1
If .Show = False Then
GoTo exit_Sub
End If
strPick = .SelectedItems(1)
End With
MsgBox strPick

'- - - - - - - - - - - - - - - - - - - -
'Allow user to Open file(s)
With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = False 'allow only one file to be chosen
.InitialFileName = Left(ActiveWorkbook.FullName, _
Len(ActiveWorkbook.FullName) - Len(ActiveWorkbook.name))
.Title = "This is a test of File Selecting..."
.Filters.Clear
.Filters.Add "Excel Files", "*.xls", 1
.Filters.Add "Text Files", "*.csv, *.txt, *.prn", 2
.Filters.Add "ALL Files", "*.*", .Filters.Count + 1
.FilterIndex = 1
.Show
strPick = .SelectedItems(1)
End With
MsgBox strPick

'- - - - - - - - - - - - - - - - - - - -
'Allow user to select folder(s)
With Application.FileDialog(msoFileDialogFolderPicker)
.AllowMultiSelect = False 'not applicable w/folders
.InitialFileName = Left(ActiveWorkbook.FullName, _
Len(ActiveWorkbook.FullName) - Len(ActiveWorkbook.name))
.Title = "This is a test of Folder Picking..."
.Show
strPick = .SelectedItems(1)
End With
MsgBox strPick

'- - - - - - - - - - - - - - - - - - - -
'Allow user to save current file
With Application.FileDialog(msoFileDialogSaveAs)
.Title = "This is a test of File Save As..."
.InitialFileName = Application.ActiveWorkbook.name
.Show
strPick = .SelectedItems(1)
End With
MsgBox strPick

'- - - - - - - - - - - - - - - - - - - -

exit_Sub:
On Error Resume Next
Exit Sub

err_Sub:
Debug.Print "Error: " & Err.Number & " - (" & _
Err.Description & _
") - Sub: FileDialogExamples - " & Now()
GoTo exit_Sub
End Sub
'/==========================================/

--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown

 

[Guest]

Thanks for your reply. I tried it and it worked great.

 

[Guest]

Thanks for your reply. I tried Rick's suggestion before I saw yours. His
works great. I will definitely keep yours on file to study and use in the
future.

Thanks again!

 

[Guest]

Thanks for your reply. I tried Rick's suggestion before I saw yours. His
works great. I will definitely keep yours on file to study and use in the
future.

Thanks again!

 

[Rich Locus]

Gary:

I like your post for opening files... the code is much more compact than
what I have been using in the past. I ran the VBA and it certainly presented
the Open File Dialogue Box. However, it didn't actually open the file. I
could not see any difference between the Picker and the File Dialogue Open.

Suggestions?

 

[Rich Locus]

Gary:

I added one minor addition to your Open Dialogue box to actually open the
file. Your code works great.

Public Sub FileDialogExamples()
'
Dim strPick As String
Dim strWorkbookOnlyName As String
Dim strWorkbookFullPathAndName As String

On Error GoTo err_Sub

'- - - - - - - - - - - - - - - - - - - -
'Allow user to Open file(s)
With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = False 'allow only one file to be chosen
.InitialFileName = Left(ActiveWorkbook.FullName, _
Len(ActiveWorkbook.FullName) - Len(ActiveWorkbook.Name))
.Title = "This is a test of File Selecting..."
.Filters.Clear
.Filters.Add "Excel Files", "*.xls", 1
.Filters.Add "Text Files", "*.csv, *.txt, *.prn", 2
.Filters.Add "ALL Files", "*.*", .Filters.Count + 1
.FilterIndex = 1
.Show
strPick = .SelectedItems(1)
End With
' MsgBox strPick

Workbooks.Open strPick
strWorkbookFullPathAndName = ActiveWorkbook.FullName
strWorkbookOnlyName = ActiveWorkbook.Name