VBA Open an Excel file - Dialog box changes

Question

I am running an excel vba script, where in I am trying to open a dialog box to select an excel file and open that excel file. I try to give the path of the folder so that the end user can go directly to the folder and select the file that he wants.

But, it works fine for the first time but when the next time it runs it opens the folder where in the end user has selected the file last time.

Here goes my code,

thisYear = Year(Date)


'change the display name of the open file dialog
    Application.FileDialog(msoFileDialogOpen).Title = _
    "Select Input Report"

 'Remove all other filters
 Application.FileDialog(msoFileDialogOpen).Filters.Clear

 'Add a custom filter
 Call Application.FileDialog(msoFileDialogOpen).Filters.Add( _
     "Excel Files Only", "*.xls*")

     'Select the start folder
     Application.FileDialog(msoFileDialogOpen _
     ).InitialFileName = "\\driveA\Reports\" & thisYear & ""

file = Application.FileDialog(msoFileDialogOpen).Show 

Application.FileDialog(msoFileDialogOpen).Execute

How to work around this?

According to https://msdn.microsoft.com/en-us/library/office/ff862446.aspx, you need to use .show to display the dialog, and if you're using msoFileDialogOpen, you should use .execute to open the file(s). Maybe this could explain your issue. — Rich Holton, Apr 26 '17 at 00:13
@RichHolton I have updated my code. Can you please tell me where I am going wrong. — Student of the Digital World, Apr 26 '17 at 00:32
@RichHolton Ya, Thanks and I will update if i found any answer as well. — Student of the Digital World, Apr 26 '17 at 00:38

David Zemens · Accepted Answer · 2017-04-26T01:10:49.440

2

It's better to work with an object variable rather than repeatedly calling on Application.FileDialog because each call to Application.FileDialog is likely treated as a new instance of that class, which probably explains your issues. This is a hypothesis I haven't tested yet and I'm not 100% but it seems reasonable.

Try instead:

Dim fdlg as FileDialog
Set fdlg = Application.FileDialog(msoFileDialogOpen)
'change the display name of the open file dialog
fdlg.Title = "Select Input Report"
'Remove all other filters
fdlg.Filters.Clear
'Add a custom filter
fdlg.Filters.Add "Excel Files Only", "*.xls*"
'Select the start folder
fdlg.InitialFileName = "\\driveA\Reports\" & thisYear & ""
'Display to user:
fdlg.Show 

'Ensure selection:
If fdlg.SelectedItems.Count <> 0 Then
'Captures the filename chosen:
file = fdlg.SelectedItems(1)

'Then, you probably want to open it:
Set wb = Workbooks.Open(file)

Else
    'no file is selected, add error-handling or inform user, exit sub early, etc.
End If

edited Apr 26 '17 at 01:10

answered Apr 26 '17 at 00:47

David Zemens

53,033
11
81
130

2

your code works for me..What I meant was, when the first time I run the macro it picks up the path that was mentioned in the code and goes into the particular folder. Lets call this folder as Folder A. Inside the Folder A, the user has three folders : Folder 1, Folder 2 and Folder 3. if the user picks up the file from lets say Folder 2 and when the next time this macro is run. It takes the end user straight to the Folder 2 and not Folder A and allow him to choose the folder that he wants. – Student of the Digital World Apr 26 '17 at 01:00
1

OK thanks for clarifying. That definitely sounds strange and unexpected! – David Zemens Apr 26 '17 at 01:10
No Problem,Thanks :) – Student of the Digital World Apr 26 '17 at 01:14

VBA Open an Excel file - Dialog box changes

1 Answers1