Best way to script pulling values from SQL db, place in specific cells in spreadsheet, then get calculated values back out of excel into SQL
Posted on 2011-03-07
I've been tasked with a project to get data from a web form (done), place said data into an excel spreadsheet with extremely complex formulas set up (either directly or after being placed into a SQL database -- not done), get the resulting information from the excel spreadsheet back into another Table (not done), and then spit the result back from the SQL table into PHP (not done, but not a problem). This all needs to be done w/o intervention on the server.
Server is a Win 2k3 server running IIS 6, and will have Excel installed. Test machine is my Win7 laptop with IIS 7 and Excel installed. I have the connection to the DB set up and working properly.
I'm looking for code examples that could be used to accomplish this task. I'm not asking you to write my code for me (how would I learn from that?), but would love to see some snippets to get from A to C (SQL to Excel to SQL).
I'm not looking for Macros inside of Excel necessarily, but if this is the only way to get what I need, and it can all be scripted to launch automatically, I'm ok with it.