Search code examples
vbaopenfiledialogsolidworks

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


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?


Solution

  • 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