Pick multiple file extensions in msoFileDialogFolderPicker

[anish.ms]

Dear Experts,
Request your guidance on below.

Following is part of the code to pick files from a selected folder and copy the selected columns from the sheet into a new workbook. How do I include CSV files as well in addition to *.xl*?


With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "Please select a folder"
.Show
.AllowMultiSelect = False
If .SelectedItems.Count = 0 Then
MsgBox "You did not select a folder."
Exit Sub
End If
strDirContainingFiles = .SelectedItems(1) & "\"
End With

strFile = Dir(strDirContainingFiles & "\*.xl*")
Do While Len(strFile) > 0
colFileNames.Add Item:=strFile
strFile = Dir
Loop

If colFileNames.Count = 0 Then
MsgBox "There are no excel files in this folder."
Exit Sub
Else
MsgBox "There are " & colFileNames.Count & " excel files in this folder."
End If

[Aussiebear]

Have you tried


strFile = Dir(strDirContainingFiles & "\*.xl*" & "\*.csv")

[p45cal]

Several ways, here's two:
Either loop twice through the folder:
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "Please select a folder"
.Show
.AllowMultiSelect = False
If .SelectedItems.Count = 0 Then
MsgBox "You did not select a folder."
Exit Sub
End If
strDirContainingFiles = .SelectedItems(1) & "\"
End With
strFile = Dir(strDirContainingFiles & "*.xl*")
Do While Len(strFile) > 0
colFileNames.Add Item:=strFile
strFile = Dir
Loop
strFile = Dir(strDirContainingFiles & "*.csv")
Do While Len(strFile) > 0
colFileNames.Add Item:=strFile
strFile = Dir
Loop

If colFileNames.Count = 0 Then
MsgBox "There are no excel/csv files in this folder."
Exit Sub
Else
MsgBox "There are " & colFileNames.Count & " excel/csv files in this folder."
End If

or examine the extension of all the files:
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "Please select a folder"
.Show
.AllowMultiSelect = False
If .SelectedItems.Count = 0 Then
MsgBox "You did not select a folder."
Exit Sub
End If
strDirContainingFiles = .SelectedItems(1) & "\"
End With
strFile = Dir(strDirContainingFiles)
Do While Len(strFile) > 0
Z = LCase(Mid(strFile, InStrRev(strFile, ".") + 1))
DoEvents 'might not be needed (was only included because I encountered a 'not responding' hang during development and couldn't break out of the code. Code has since been changed.)
If Z Like "xl*" Or Z = "csv" Then
colFileNames.Add Item:=strFile
End If
strFile = Dir
Loop

If colFileNames.Count = 0 Then
MsgBox "There are no excel files in this folder."
Exit Sub
Else
MsgBox "There are " & colFileNames.Count & " excel/csv files in this folder."
End If
There are a few subtle changes you might not spot, so copy/paste the entire codes above.

[anish.ms]

Thanks

[anish.ms]

Thanks p45cal (http://www.vbaexpress.com/forum/member.php?3494-p45cal) for the detailed explanation!

[anish.ms]

strFile = Dir(strDirContainingFiles & "\*.xl*" & "\*.csv")

This doesn't work.

[Aussiebear]

What about P45cal's suggestions?

[anish.ms]

That is working fine. I adopted his first method of looping twice