Excel as a Really Bad Database

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.

tools references excel scripting runtime

Now I can loop through all the files in the folder.

Sub GetQuotes()
   
    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:

Sub WriteQuotes(oFile As Scripting.File)
   
    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.

Posted in Uncategorized

13 thoughts on “Excel as a Really Bad Database

  1. 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

  2. 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” ?

  3. 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.

  4. 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

  5. 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.

  6. 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.

  7. 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?

Leave a Reply

Your email address will not be published. Required fields are marked *