ExcelBanter - Can I display an open file dialog from a macro

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Can I display an open file dialog from a macro (https://www.excelbanter.com/excel-programming/442161-can-i-display-open-file-dialog-macro.html)

Can I display an open file dialog from a macro

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.

Can I display an open file dialog from a macro

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)

wrote in message
...
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.

Can I display an open file dialog from a macro

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

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

Can I display an open file dialog from a macro

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

On Thu, 29 Apr 2010 14:20:50 -0400, "Rick Rothstein"
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.

Can I display an open file dialog from a macro

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

Can I display an open file dialog from a macro

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

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

Can I display an open file dialog from a macro

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

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