VBA Select file from dialog box (only specific file extensions)-CodePudding

I am trying to write a code where when clicking a commandbutton - it shows a dialog box browser for files and the user can only select ".xlsx" file. WHat I have so far is:

Sub CommandButton_Browse_Click()
    
    Call Set_FileBrowser
    
    'TextBox_OutputDirectory
    If Not SelectedFile Is Nothing Then
        Me.TextBox_InputDirectory.Text = SelectedFile.Self.Path
    End If
    
End Sub

Sub Set_FileBrowser()
    
    Set shellApp = CreateObject("Shell.Application")
    Set SelectedFile = shellApp.BrowseForFolder(0, "Select a file", 16384)
    
End Sub

It shows the file browser, but it has no filters regarding file format. What's teh correct approach and solution to this problem?

CodePudding user response：

Solution for Solidworks

Adapted from the documentation:

Function SelectXlFile() as String
    Dim DialogTitle As String
    Dim InitialFileName As String
    Dim FileFilter As String
    Dim OpenOptions As Long
    Dim ConfigName As String
    Dim DisplayName As String
    Dim fileDisplayState As String

    'Example for a single file type:
    DialogTitle = "Select an Excel file."
    FileFilter = "Excel Files (*.xlsx)|*.xlsx"

    'Example for multiple file types:
    'DialogTitle = "Select an Excel or Word file."
    'FileFilter = "Excel Files (*.xlsx; *.xlsm)|*.xlsx; *xlsm|Word Files (*.docx)|*.docx"

    SelectXlFile = Application.SldWorks.GetOpenFileName2(DialogTitle, InitialFileName, _
                       FileFilter, OpenOptions, ConfigName, DisplayName, fileDisplayState)
End Function

Sub CommandButton_Browse_Click()
    'TextBox_OutputDirectory
    Me.TextBox_InputDirectory.Text = SelectXlFile()
End Sub

Solution for MS-Office:

Function SelectXlFile() as String
    SelectXlFile = Application.GetOpenFilename( _
                       FileFilter:="Excel Files (*.xlsx),*.xlsx", _
                       Title:="Select a file.", _
                       MultiSelect:=False)
End Function

Sub CommandButton_Browse_Click()
    'TextBox_OutputDirectory
    Me.TextBox_InputDirectory.Text = SelectXlFile()
End Sub