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.

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

--
Rick (MVP - Excel)



<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.

'/==========================================/
' 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



"(E-Mail Removed)" wrote:

> 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.
> .
>

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

On Thu, 29 Apr 2010 14:20:50 -0400, "Rick Rothstein"
<(E-Mail Removed)> wrote:

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

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!



On Thu, 29 Apr 2010 11:56:06 -0700, Gary Brown <junk_at_kinneson_dot_com>
wrote:

>'/==========================================/
>' 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
>'/==========================================/

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!



On Thu, 29 Apr 2010 11:56:06 -0700, Gary Brown <junk_at_kinneson_dot_com>
wrote:

>'/==========================================/
>' 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
>'/==========================================/

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
Logicwurks, LLC


"Gary Brown" wrote:

> '/==========================================/
> ' 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
>
>
>
> "(E-Mail Removed)" wrote:
>
> > 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.
> > .
> >

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

--
Rich Locus
Logicwurks, LLC


"Gary Brown" wrote:

> '/==========================================/
> ' 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
>
>
>
> "(E-Mail Removed)" wrote:
>
> > 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.
> > .
> >