Architecture of an Excel application

Question

after 10 years of programming I find myself with the daunting task of creating my first Excel application in Excel 2007. I have programmed in VBA before on MS Access so this is not really a technical challenge for me but it's a real change of "paradigm" I dare say.

Now I have to implement an Excel app that talks to SQLServer (on a dedicated database that I create), typical CRUD stuff but what I cannot read on any book (Excel Bible, Excel Power Programming, etc...) is how am I supposed to structure the app.

• Can I give names to my columns and use them as a database column when sending the data back to SQLServer

• When I retrieve back the data what's expected of a spreadsheet app that I for example retrieve ID, Description (hiding the ID in a column and showing the Description) or should I just use the Description for everything and store denormalized data in my SQLServer tables, making them the equivalent of a server side Excel sheet?

• If I want to use normalized data like for LookupTable (ID, Country) should I store the ID, Country information in a Range and if so, how do I force the user to pick a value from that Range (ID, Country) without using a proper Combobox?

• When I retrieve the data from SQLServer should I model it into an ADODB.Recordset (for example by calling a view or a stored procedure) and copy it to the Sheet making sure the order of fields in the Recordset is the same as in the Sheet or is there a better way?

I'm sure there's many people that understand my situation out there because they had been in my shoes, please help me make the jump that will help me understand the spreadsheet app world. Pointers to web resources are very welcome too.

Thanks.

Answer 1

I've written a couple of applications similar to the one you've described and while I can't pretend to offer best practice I can comment based on my personal experiences.

• Can I give names to my columns and use them as a database column when sending the data back to SQLServer

Sure why not, you can add the column names as the first row of an excel sheet and use the cell protection features to prevent tampering by the end user. You need to associate the columns of data in excel with the underlying fields in SQL Server somehow and this is as good a way as any.

• If I want to use normalized data like for LookupTable (ID, Country) should I store the ID, Country information in a Range and if so, how do I force the user to pick a value from that Range (ID, Country) without using a proper Combobox?

You can create individual (hidden) sheets for each lookup table and enforce selection using code something like this (based on a macro recording so this could be cleaned up)

    With Sheet1.Range("E3").Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:="=B2:B5"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = "A sample"
        .ErrorTitle = "An error"
        .InputMessage = "Input message"
        .ErrorMessage = "Error message"
        .ShowInput = True
        .ShowError = True
    End With

This will give you a combo box in cell, an error message on failed validation and a title for the cell.

• When I retrieve back the data what's expected of a spreadsheet app that I for example retrieve ID, Description (hiding the ID in a column and showing the Description) or should I just use the Description for everything and store denormalized data in my SQLServer tables, making them the equivalent of a server side Excel sheet?

It would work either way. I’d probably decide based on the complexity of the data. If you’re needing to build lots of validation code in VBA I’d be tempted to pull denormalised data into staging tables in SQL Server and then denormalise using stored procs before moving the data to your main tables. YMMV.

• When I retrieve the data from SQLServer should I model it into an ADODB.Recordset (for example by calling a view or a stored procedure) and copy it to the Sheet making sure the order of fields in the Recordset is the same as in the Sheet or is there a better way?

I'd avoid using copy and paste as it uses a system wide clipboard and odd things can start happening if you're using the PC at the sametime. The range object in excel has a CopyFromRecordset method that you can use to push the data from ADO to a worksheet. It's a lot faster than iterating through the recordset assigning manually. The only downside is that you'll have to create the header columns yourself beforehand.