Opennig excel file via Userform in Excel

Question

I wanna rewrite code below to see Excel files (.xlsm) in FileDialog filters

Function write file's(in this case only folder's) path to textbox in userForm

    Private Sub OpenExplorer_Click()
Dim diaFolder As Office.FileDialog
Dim selected As Boolean

Set diaFolder = Application.FileDialog(msoFileDialogFolderPicker)
diaFolder.AllowMultiSelect = False
selected = diaFolder.Show

If selected Then
TextBox1.Text = diaFolder.SelectedItems(1)
End If

Set diaFolder = Nothing

End Sub

but I get an error when i try adding filters to see excel or all files:

Rune-time error '5':

Invalid procedure call or argument

Below is my attempt at rewriting. After debugging .Filters.Add "all files is highlighted

Set diaFolder = Application.FileDialog(msoFileDialogFolderPicker)

    diaFolder.AllowMultiSelect = False
    
    With diaFolder
    .Title = "Prosze wybrać plik."
    .Filters.Clear
    .Filters.Add "all files", "."
    .Filters.Add "Excel file", ".xlsm"
    End With

selected = diaFolder.Show

https://stackoverflow.com/questions/45332735/vba-excel-filedialog-to-set-reset-filters — Siddharth Rout, Mar 05 '23 at 07:31

score 2 · Accepted Answer · answered Mar 05 '23 at 07:31

Sub SelectExcelFile()
  Dim file_path$
  With Application.FileDialog(msoFileDialogFilePicker)
    .AllowMultiSelect = False
    With .Filters
      .Clear '//Clear all filters
      .Add "Select Excel file", "*.xlsm" '//Allow only XLSM files to be visible
    End With
    '// If user selected file, write it to file_path, otherwise exit procedure
    If .Show() Then file_path = .SelectedItems(1) Else Exit Sub
  End With
End Sub

Opennig excel file via Userform in Excel

1 Answers1