Do you know what’s worse than having a bunch of data in an Excel workbook that really should be in a relational database? Having a bunch of data in multiple Excel workbooks that really should be in a relational database. It’s the Excel-workbook-as-a-record model and it’s how we currently store our quotes.
Each quote is a separate Excel file and all the files are stored in a folder. I need some information out of these files, so I have to create my own table. I set a reference to the Microsoft Scripting Runtime dll so that I can use the FileSystemObject.
Now I can loop through all the files in the folder.
Dim fso As Scripting.FileSystemObject
Dim dtOldest As Date
Dim oFile As Scripting.File
Dim sPath As String
Dim wb As Workbook
Set fso = New Scripting.FileSystemObject
dtOldest = Date – 30
sPath = “S:AIMUSASalesQuotesAIM Quotes”
For Each oFile In fso.GetFolder(sPath).Files
If UCase(Right$(oFile.Name, 3)) = “XLS” Then
If oFile.DateCreated > dtOldest Then
I only want Excel files, and get them by looking at the last three characters of the file name. I also only want those files that were created in the last month, or so. This was the primary reason why I didn’t use Application.FileSearch. FileSearch has a LastModified property, but it doesn’t seem very flexible. I could use msoLastModifiedLastMonth, and that would have worked well today, but it would not have worked so well on, say, August 15th. I’m also not really that interested in the date it was last modified and I didn’t see any facility for creation date in FileSearch.
The down side to using the FileSystemObject is that I end up looping through 2,500 files. I’m not sure how to limit the number it loops through.
Here’s the WriteQuotes sub, although not really that interesting:
Dim wb As Workbook
Dim rStart As Range
Dim rQuoteParts As Range
Dim rCell As Range
Dim lRow As Long
Set wb = Workbooks.Open(oFile.Path)
Set rStart = wshQuotes.Range(“A65536”).End(xlUp).Offset(1, 0)
Set rQuoteParts = wb.Sheets(1).Range(“A21”, wb.Sheets(1).Range(“A45”).End(xlUp))
lRow = 0
For Each rCell In rQuoteParts.Cells
rStart.Offset(lRow, 0).Value = wb.Sheets(1).Range(“C9”).Value
rStart.Offset(lRow, 1).Value = oFile.DateCreated
rStart.Offset(lRow, 2).Value = rCell.Offset(0, 1).Value
rStart.Offset(lRow, 3).Value = rCell.Offset(0, 7).Value
lRow = lRow + 1
Note that the Path property of the Scripting.File object includes the filename, which is different than the Path property of the Excel.Workbook object.