SQL Server to Excel Question

[MarillionFan]

Right. Got 20 reports in Business Objects, which I have converted to SQL Statements.

Want to populate a spreadsheet with 20 formatted reports on a daily basis automatically as part of a DTS function run fom a scheduled Job. The Openrowset using the OLE DB Jet Engine is proving a pain in the arse as you cannot delete the data within the sheet, you have to define the table output and everything comes out as Varchar.

Short of writing an VB Script from scratch , my more preferred method of an ADO Macro in Excel calling the 20 SQL Statements from my reports table in the main DB, what are my options bearing in mind the client is too tight to pay for a reporting scheduler.

Anyone?

[Spacecadet]

run the sql nightly via a job in SQL Server to populate some staging tables

write a short VBA in excel to pull the data from the staging tables. The SQL Server user the ADO connection from VBA uses only has select access to the staging tables.

Add a refresh button to the worksheet so the user can refresh when they like, or add something to refresh the data whenever the spreadsheet is opened.

PM me if you need example VBA scripts

[MarillionFan]

Yep, thats what Im doing. I had hoped to do everything in DTS but having read up on the jet engine I cannot do a delete plus it 'cleverly' changes everything to text thus screwing up the refresh macro in Excel.

I have created a table called sqlReports that contains the sql statemets I want to run and a destination workbook, sheet & cell.

I have four workbooks to do like this and forty statements in total, so Im going to write a generic macro to pull the data using the a select from the sqlReports table. I know I have a connection as it's DTS that is kicking off the Excel Macro.

Seems long winded to me but hey ho, should have used 2005.

Cheers

[Spacecadet]

The SSIS in 2005 isn't any cleverer, i've had exactly the same problem when needing to generate Excel spreadsheets, there's no ability to format or do anything except for just dumping the data in.

At the initial pre-contract consultation stage I actually advised against 2005 as after listening to their requirements i thought it was a waste of money. Day 1 of the contract, they show me the spanking new 2005 Database server! Good for me though, it gets added to the CV now

[arrowone]

It's quite a while since I did this, but I recall that I created an empty fomratted template. Then in DTS vbscript copy the template overwriting the old spreadsheet and then populated the worksheets. From memory get the headers in the template correct and then make sure you format each cell in the blank row below the headers. I populated the template with live data, made sure the first row formatted correctly, then delete all the data leaving the headers in place.

[MarillionFan]

Hi arrow.

Not dissimilar to what I have done, except the data didnt format on the push, so did a pull kicked off by a push.