Is that an Excel file I see?

November 2, 2009 by JP · 282 words · Leave a Comment

microscope

The GetFilesList function loops through a given folder, compares the file extension of each file against the given file extension, and returns a list of matching filenames.

Here's an intermediate function which can be called within the Do loop to check if the current filename has an appropriate Excel extension (i.e. instead of doing the comparison directly inside the GetFilesList function).

Function IsExcelFileType(fileName As String)
' returns True is file extension starts with 'xl', i.e. xla, xlam, xlsx, etc
  IsExcelFileType = (UCase$(Left$(GetFileType(fileName), 2)) = "XL")
End Function

Function GetFileType(fileName As String) As String
' get file extension
  GetFileType = Mid$(fileName, InStrRev(fileName, ".") + 1, Len(fileName))
End Function

The function is hard-coded for Excel files, but my assumption is that this is what most people are looping for anyway. So, assuming that you are looking for all Excel files in a folder, here is the updated GetFilesList function:

Function GetFilesList(ByVal folder As String, ByVal fileType As String) As String()
' build list of files in a folder folder
Dim fileslist() As String
Dim i As Long
Dim currentWorkbook As String

currentWorkbook = Dir(folder)

Do While Len(currentWorkbook) > 0
  If IsExcelFileType(currentWorkbook) Then
    ReDim Preserve fileslist(i)
    fileslist(i) = currentWorkbook
    i = i + 1
  End If

  currentWorkbook = Dir
Loop
GetFilesList = fileslist

End Function