Architecture of an Excel application -

after 10 years of programming find myself daunting task of creating first excel application in excel 2007. have programmed in vba before on ms access not technical challenge me it's real change of "paradigm" dare say.

now have implement excel app talks sqlserver (on dedicated database create), typical crud stuff cannot read on book (excel bible, excel power programming, etc...) how supposed structure app.

• can give names columns , use them database column when sending data sqlserver

• when retrieve data what's expected of spreadsheet app example retrieve id, description (hiding id in column , showing description) or should use description , store denormalized data in sqlserver tables, making them equivalent of server side excel sheet?

• if want use normalized data lookuptable (id, country) should store id, country information in range , if so, how force user pick value range (id, country) without using proper combobox?

• when retrieve data sqlserver should model adodb.recordset (for example calling view or stored procedure) , copy sheet making sure order of fields in recordset same in sheet or there better way?

i'm sure there's many people understand situation out there because had been in shoes, please me make jump me understand spreadsheet app world. pointers web resources welcome too.

thanks.

i've written couple of applications similar 1 you've described , while can't pretend offer best practice can comment based on personal experiences.

• can give names columns , use them database column when sending data sqlserver

sure why not, can add column names first row of excel sheet , use cell protection features prevent tampering end user. need associate columns of data in excel underlying fields in sql server somehow , way any.

• if want use normalized data lookuptable (id, country) should store id, country information in range , if so, how force user pick value range (id, country) without using proper combobox?

you can create individual (hidden) sheets each lookup table , enforce selection using code (based on macro recording cleaned up)

     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 

this give combo box in cell, error message on failed validation , title cell.

• when retrieve data what's expected of spreadsheet app example retrieve id, description (hiding id in column , showing description) or should use description , store denormalized data in sqlserver tables, making them equivalent of server side excel sheet?

it work either way. i’d decide based on complexity of data. if you’re needing build lots of validation code in vba i’d tempted pull denormalised data staging tables in sql server , denormalise using stored procs before moving data main tables. ymmv.

• when retrieve data sqlserver should model adodb.recordset (for example calling view or stored procedure) , copy sheet making sure order of fields in recordset same in sheet or there better way?

i'd avoid using copy , paste uses system wide clipboard , odd things can start happening if you're using pc @ sametime. range object in excel has copyfromrecordset method can use push data ado worksheet. it's lot faster iterating through recordset assigning manually. downside you'll have create header columns beforehand.