CODEWITHSUNDEEP
vbaexcelms-wordoffice-2010
user4974730
user4974730

Reputation:

Optimize msoFileDialogOpen

I am trying to make this msoFileDialogOpen allow the user to select multiple files. Is there a better way to do this:

Public Sub Function3_FileExplorer()
    ' Start File Explorer to select file containing data (simple GUI, much
    ' easier than coding vFileName)
    vuserChoiceDataFileNumber = InputBox("Enter the number of files you want to select.")
    With Application.FileDialog(msoFileDialogOpen)
        Select Case IsNumeric(vuserChoiceDataFileNumber)
            Case True
                If VarType(vuserChoiceDataFileNumber) = 2 Or 3 Then
                    iuserChoiceDataFileNumber = CInt(vuserChoiceDataFileNumber)
                End If
            Case False
                MsgBox (vuserChoiceDataFileNumber & " is not an integer.")
                .AllowMultiSelect = False
        End Select
        .Show
    End With
    Exit Sub
    On Error GoTo ErrorHandler
    .AllowMultiSelect = True
    ErrorHandler:
    MsgBox "Error detected" & vbNewLine & "Error" & Err.Number & ": " & _
        Err.Description, vbCritical, "Error Handler: Error " & Err.Number
    MsgBox "If you want to force the program to run, go to the line below and " & _
        "insert a ' mark to comment the line out." & vbNewLine & _
        "On Error GoTo ErrorHandler", vbCritical, "Error Handler: Error " & Err.Number
End Sub

Upvotes: 1

Views: 246

Answers (1)

Rachel Hettinger
Rachel Hettinger

Reputation: 8442

Yes, you can make this much simpler by not asking the user how many files they want to open--just let them select as many as they want.

Public Sub Function3_FileExplorer()
    '   Start File Explorer to select file containing data (simple GUI, much easier than coding vFileName)
    With Application.FileDialog(msoFileDialogOpen)
        .AllowMultiSelect = True
        .FilterIndex = 2
        If .Show Then
            Dim file As Variant
            For Each file In .SelectedItems
                ' do something with the file, for example, open it:
                Application.Workbooks.Open (file)
            Next file
        End If
    End With
End Sub

Upvotes: 1

Related Questions