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
WriteQuotes oFile
End If
End If
Next oFile
End Sub
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
Next rCell
wb.Close False
End Sub
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.
Hi Dick –
I have the same basic complaint, and I’m going to retire before I ever get around to learning and shifting a monthly chore into Access.
Actually, your WriteQuotes sub is interesting. And what’s interesting about it is that you open and close some portion of 2500 files. I would have used (and do use for my monthly chore) J-Walk’s approach to getting values from a closed file. Works very nicely inside a loop to pull out about a 1000 values. The spreadsheets it sucks from are calculation-intensive and I find this saves me some time.
…Michael
The portion is about 30, so the whole schmear takes <5 seconds. Here’s the j-walk method if anyone’s interested:
http://j-walk.com/ss/excel/tips/tip82.htm
The FileSystemObject just seems way too cumbersome. This is simpler to do with console commands.
for /F “tokens=1,5? %a in (‘dir x:fooar*.xls /TC ^|
findstr /b /r /c:”[0-9]”‘) do @echo %a %b > %TEMP%flst.txt
which writes a list of creation dates and filenames for .xls files in x:fooar to a file in the TEMP directory. Then filter out the records within the desired date range, and use the filtered records to identify the workbooks to process.
BTW, shouldn’t you check that the last *4* chars are “.XLS” ?
Looks like I forgot to mention importing the text file in TEMP.
Chuck it into a database and build a simple extraction spreadsheet for when they want to review it in Excel.
I refuse to try to solve this problem in this way I have tried and it only creates more problems for yourself, you are only encouraging the luddites to believe they might have been correct.
Dick,
You dont need to open a file to read or write. Just use ADO
You dont have to to depend on the XL4 macro command either its relatively slower.
If data is stored in a structured way it doest matter whether it is stored in an Access file or an Excel file or in a Text file
The key thing is structured storage.
Sam
I think my point is “You Can, but you Shouldn’t”.
Today it is only 30 files, tomorrow it will be all of them, as soon as “they” (the luddites and philistines) have seen what data is available. The day after that someone will save one of the files as Excel 97 and create some bizzare problem with a french installation and you will end up with VRAI and FAUX in the columns in place of TRUE and FALSE, and then the day after that some one will insert a column in the file having managed to open it up in some convoluted way. What if you want to add columns? do you extend all of the files by one column? What if there is an older version withouth this field in the headings? Will your ADO command still work without the header row still work? All of this has happened to me already. Bite the bullet and get the data from the central location with data types and indexes and scalability.
The entire reason for my existence in my current job is a workbook (actually, many) that is absolutely screaming to be a database (FoxPro, preferably, if I ever get the time to convert everything).
I do use John’s XL4 macro in a few places but I find it easier (and quicker) to just import my main “database” into a sheet on each of my dependent workbooks when necessary. Not pretty, but it works (for me).
Question…..
In the latest and greatest Excel version (which I may never have a use for), did MS ever get the VLookup function on a closed workbook to ever match the incredible speed (compared to Excel) that Lotus does in 123)?
That feature alone would be enough to make me an upgrade convert, eliminate my need for my inefficient “import” process, get rid of my scattered XL4 macros (and thank you, John. That XL4 routine does come in handy) and maybe stop me from wishing that I had started my whole project in FoxPro.
Dick,
Why not simply use ADO to write and read records to a single Excel file (which can remain closed when you read or write records)? Am I missing something?
Colin, et al: I have many fish to fry, and this is a small one. That’s why I didn’t invest more than 5 minutes in it.
to solve this problem take a look at this:
http://boardwalktech.com
There are better ways to Read/Write XLS/CSV/HTML/XLSX files than Excel Automation (http://www.gemboxsoftware.com/GBSpreadsheet.htm). You can use our Free Spreadsheet .NET Component (http://www.gemboxsoftware.com/GBSpreadsheetFree.htm) even in your commercial applications.
Spire.XLS can do this well, I recommanded it, which support excel 97 ~ 2010 without office ole automation, very easy to use and performance hight.
http://www.e-iceblue.com/Introduce/excel-for-net-introduce.html