May 11, 2005 at 5:04 am
I have an excel file. It contains several pivot tables which are updated from worksheets containing tables from SQL Server.
How can I make the code in SQL server run and populate several worksheets with the resulting tables of the SQL code autmatically?
Cheers
If riding in a plane is flying, then riding in a boat must be swimming. To experience the element, get out of the vehicle. Skydive!
May 11, 2005 at 4:27 pm
You can create a DTS job that does this. That would be my preferred method: it's bad to do DML on things outside of SQL Server.
And if you poke around here, someone posted somewhere a function that can be used to reference an EXCEL worksheet from a SQL statement (using ODBC/Jet driver)--however, I don't know if that will let you output to that Excel file.
May 12, 2005 at 6:20 am
You can use an ODBC driver to connect your Excel spreadsheet to SQL Server. In excel then you would click on Data-Get External Data - Create New Query. Select your server from the Choose Data Source box, then click ok. This will take you into Microsoft Query. You can build your query there by selecting the tables and columns you want OR go straight to the SQL box by clicking SQL on the toolbar and type the query directly there. Then click on file return to Excel.
You can also set up user-defined parameters in excel and link it to the criteria fields in MS Query by using the bracket symbols (they work like a wildcard) []. Back in Excel you then point the parameters to a field in Excel (has to be formatted as text). You can then set up a macro that when run will update the query and update your pivot tables. Hope this helps!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply