-1

I want to review all the Excel files inside a folder to listbox, noting that the Excel includes more than one version. I used the following code and it didn't work

   Dim di As New IO.DirectoryInfo("D:\EXCEL")
        Dim aryFi As IO.FileInfo() = di.GetFiles("Excel Worksheets|*.xls; *.xlsx; *.xlsm")
        Dim fi As IO.FileInfo
        For Each fi In aryFi
            ListBox1.Items.Add((fi))
        Next
fast2021
  • 115
  • 1
  • 9

1 Answers1

0

Modified slightly from this example: https://stackoverflow.com/a/3527717/1920035

Private Function GetExcelFiles(ByVal directoryInfo As IO.DirectoryInfo) As IO.FileInfo()
    If (directoryInfo Is Nothing) Then
        Throw New ArgumentNullException(NameOf(directoryInfo))
    End If

    Dim files As IEnumerable(Of IO.FileInfo) = directoryInfo.GetFiles()
    Dim excelFileExtensions() As String = {".xlsx", ".xlsm", ".xlsb", ".xltm", ".xlam", ".xls", ".xla", ".xlb", ".xlc", ".xld", ".xlk", ".xll", ".xlm", ".xlt", ".xlv", ".xlw"}
    return files.Where(Function(file) excelFileExtensions.Contains(file.Extension))
End Function

What this does is:

  1. Get all files from the directory info
  2. Declare a collection of file extensions to check against
  3. Return only the files where the extension exists in the file extensions collection

Important to note - It may be worthwhile moving the excelFileExtensions so that it is a private readonly variable at a higher scope. It isn't much, but if you're running this a lot then it will make a difference.

David
  • 5,877
  • 3
  • 23
  • 40
  • Two points where this answer could perhaps be improved: 1) The return type does not match the declared type of the function (make sure to set [`Option Strict On`](https://stackoverflow.com/a/29985039/1115360) as the default for new projects). 2) It would probably be best to make the filename extension check case-insensitive, e.g. `Return files.Where(Function(file) excelFileExtensions.Contains(file.Extension, StringComparer.InvariantCultureIgnoreCase))`. – Andrew Morton Sep 05 '20 at 13:02
  • @AndrewMorton - I always code with Option Strict on, but for simple examples like this I generally free-type it. So I wouldn't be surprised at all if there turned out to be a typo, invalid typing, etc. in there. – David Sep 06 '20 at 16:20