Microsoft Excel as a Simple Content Storage Mechanism

By Deane Barker on July 3, 2003

I was doing something for my church last night when I came face-to-face with ADO’s great support for extracting data from Excel files. So I got to about Excel as a content storage system?

Let’s face facts, offices love Excel. Go to any office, and I promise you they’re storing something in Excel (they’ll probably tell you it’s a “database”). And this is not data with formulas or anything – they’re just storing it there because it’s easy to work with and an actual database program like Access is too much trouble. Lay people just “get” spreadsheets. They make sense.

So, whip up an Excel spreadsheet with a worksheet for each “object” and a column for each “property” so it’s an open system, and let them add rows as they wish. The column-and-row format is native and intuitive in Excel, it can be formatted so that there’s word-wrap within cells, you can protect areas like the top row (the column headings) so they can only edit the right cells, it has a built-in spell checker, etc. Sure, data-typing wouldn’t be that advanced, but most lightweight content management apps can live with that.

Using VBA and an FTP component (here’s a free one), you could write a macro that would FTP the file somewhere. The user opens the file, edits what they like, then clicks a toolbar button and the file is automatically FTPed to their Web hosting account, where it just overwrites the previous version of the file. (Write a COM add-in and this could be triggered by just clicking Save.) The user would essentially swap the entire database out whenever they made a change, but basic Excel files aren’t that big anyway.

Simplistic? Sure. Scalable? Nope. Easy to set-up and work with? Very. There are a lot of scenarios where this would be exactly the right thing.

Comments (2)

Matt says:

Deane, you have too much time to just... think. haha

Deane says:

I’m wondering now how you would do keys. An index page would just dump everything from a worksheet with a link to a “content page.” But what do you pass in the URL? Normally you’d pass a primary key, but...

I know Chris will come up with something.