An alternative approach to just doing CSV export (which is fast and more cross platform/app) is to use web queries (.iqy files that point to fairly HTML) or data queries (.dqy extension but point to the DB)
- Supports refreshing data / live data model
- Web based integration
- Very easy to do
- Works with all versions since excel 97
- They support linking back into you app via urls (IQY only) so we had cells that said edit that took you back to the web app.
- Auto sorting, pivoting is a doddle
- No support for different sheets in the same book (you can work around this by having a master workbook that aggregates from multiple web queries sheets.) CSV has the same problem.
- There are some methods to write back but they all seemed a bit sucky to me (you can create forms that push the data back from specific cells but it’s pretty ugle UI wise). I have always ended up writing a tiny bit of VBA that just does a POST to some url (embed this in your master workbook etc) or just an edit link that goes back to the web app
- Some annoying pop ups (See below)
- With DQY you expose username password and database details to user but you get full SQL support / joins etc.
- Make sure you set Cache-Control headers ( I use either “public” or “private, must-revalidate” )
WEB 1 http://server/money.html?query=blah+blah+blah&sortBy=market_value
XLODBC 1 DRIVER=SQL Server;SERVER=server\blah;UID=regulatory;PWD=l33t;DATABASE=showMeTheMoney SELECT * FROM trades ORDER BY market_value DESC
Disable Excel Pop.reg
Windows Registry Editor Version 5.00 [HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Excel\Options] "QuerySecurity"=dword:00000002