excelvbauserformfiledialog

Opennig excel file via Userform in Excel


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

Solution

  • 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