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