Is that an Excel file I see?
November 2, 2009 @ 8:00 AM by JP • 1 views • No Comments »
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).
' 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:
' 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
I'm just an average guy who writes VBA code for a living. This is my personal blog. Excel and Outlook are my thing, with a sprinkle of Access and Word here and there. Follow this space if you want to learn more about VBA. Keep Reading »
Previous Post: On Being a Responsible Outlook user
Next Post: Free Book Giveaway
Speak Your Mind
Tell us what you're thinking...and oh, if you want a pic to show with your comment, go get a gravatar!
Certain comments (including first-time comments) are subject to moderation and will not appear immediately. Please view the Comment Policy for more information. To post VBA code in your comment, use tags like this: [cc lang='vb']Code goes here[/cc].