Excel GetOpenFileName

Question

Hi All,

I've just found a great macro of Siddharth Rout (https://tutel.me/c/programming/questions/10658715/vba+dialogue+filefilter+partial+file+name) which does exactly what I need. However the invoked GetOpenFileName dialog doesn't fill in the File name field at the bottom of the dialog window (only the file filter field next to it is filled in).

Can you tell me how to amend the macro to fill in the File name field like in the other example below where the content of the sDesc variable is displayed in the File name field.

Function FileOpenDefault(InitialFilename As String, _

  Optional sDesc As String = "Excel (*.xls)", _

  Optional sFilter As String = "*.xls", _

  Optional sTitle As String = "File Open") As String

  With Application.FileDialog(msoFileDialogOpen)

    .ButtonName = "&Open"

    .InitialFilename = InitialFilename

    .Filters.Clear

    .Filters.Add sDesc, sFilter, 1

    .Title = sTitle   '"File Open"

    .AllowMultiSelect = False

    If .Show = -1 Then FileOpenDefault = .SelectedItems(1)

  End With

End Function

Thanks,

Stefi

Answer 1

Hi again Stefi,

I edited my previous post. This post because otherwise you do not get an email advice re the edit.

Answer 2

If I am interpreting your question correctly in that you want the file name to automatically appear in the File Name field of the dialog then the InitialFileName needs to be after the filter setting otherwise it gets cleared with the Filter.Clear.

Code edited since initial posting. I had altered the filters to xlsm in lieu of xls for testing but have now reverted to the original posted code example.

Function FileOpenDefault(InitialFilename As String, _

   Optional sDesc As String = "Excel (*.xls)", _

   Optional sFilter As String = "*.xls", _

   Optional sTitle As String = "File Open") As String

   With Application.FileDialog(msoFileDialogOpen)

     .ButtonName = "&Open"

     .Filters.Clear

     .Filters.Add sDesc, sFilter, 1

     .InitialFilename = InitialFilename     'Move this line to here

     .Title = sTitle   '"File Open"

     .AllowMultiSelect = False

     If .Show = -1 Then FileOpenDefault = .SelectedItems(1)

   End With

 End Function