Home > Software engineering >  How to restrict Application.FileDialog() to let the user only select 1 file of a particular type? (E
How to restrict Application.FileDialog() to let the user only select 1 file of a particular type? (E

Time:11-23

I have this macro in my workbook (which works perfectly) -

Dim filename As String

' select file
With Application.FileDialog(msoFileDialogFilePicker)
    If .Show <> -1 Then Exit Sub
    filename = .SelectedItems(1)
End With

Now, the above code allows the user to select multiple files, of any type. How do I modify it, so it only lets the user select a single file of a particular type (.xml)? Kindly guide... Thanks! :)


Edit: I found this code on the article linked by @Tim Williams in the comments of this post [Thanks Tim :)] -

With Application.FileDialog(msoFileDialogFilePicker)
.Filters.Add = "XML Files |*.xml"
.AllowMultiSelect = False
.Title = "Select XML file"

    If .Show <> -1 Then Exit Sub
    filename = .SelectedItems(1)
End With

But I am getting this error - Compilation Error : Argument not optional and .Add is getting highlighted... I tried to search for the error reason, but couldn't find relevant results... What should I do? Kindly guide...

CodePudding user response:

https://docs.microsoft.com/en-us/office/vba/api/excel.application.filedialog has a good overview, and https://wellsr.com/vba/2018/excel/vba-select-files-with-msoFileDialogFilePicker/ has more on using msoFileDialogFilePicker

Specifically you want to set:

    .AllowMultiSelect = False               'allow only 1 file to be selected
    .Filters.Add "XML files", "*.xml", 1    'select only files with "xml" extension
  • Related