Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Can I display an open file dialog from a macro
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 142
Default 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.
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,934
Default 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.

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 126
Default 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.
.

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 142
Default 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.

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 142
Default 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
'/==========================================/

Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 142
Default 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
'/==========================================/

Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 74
Default 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.
.

Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 74
Default 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.
.

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Open Dialog Box to return Selected File Path and Not Open it. DMS Excel Programming 4 January 26th 10 12:46 PM
Display message upon file open even with macro security set to high [email protected] Excel Discussion (Misc queries) 1 November 11th 06 02:43 PM
How do I display a dialog box that pops up when you first open exc Erin Excel Discussion (Misc queries) 4 June 22nd 05 07:27 PM
control of dialog macro dialog box. on open Gerry Abbott Excel Programming 0 July 22nd 04 05:41 PM
Open file Dialog box in Macro Joshua Excel Programming 6 April 4th 04 01:44 AM


All times are GMT +1. The time now is 06:04 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement