DTS Export to Excel (How to Format Results in Excel)

Hi All

I've been googling this for a while now and can't seem to find any elegant answers.

I'm looking for an automated way to present a FORMATED Excel Spreadsheet to the Customer from a stored procedure output.

Can anyone advise me the best method of doing this - should I / can I assign an Excel Template to the DTS Task output ?

His mind is set on Excel and the formatting is basic and easy to write in a Macro which I've done, but this requires human interaction to finish the task (Automated Run Once on opening etc).

In an ideal world an individual would send an email to the Server with two formated parameters (@.FromDate & @.ToDate) and would be emailed back a ready formatted S/Sheet. But I believe he would be willing to just select the relevant SpreadSheet for the Daily / Weekly / Monthly periods dumped.

Thanks

GWTurn your logic inside out. Create the macro, and use the macro to retrieve the data into Excel then format it.

-PatP|||Thanks for the suggestion Pat

I eventually got MSQuery installed & got some simple External Data into a sheet in Excel - Formatted another sheet and the past linked the Data from source sheet to the new sheet which seemed to work really good.

Then I tried altering the External Data Call to include the @.FromDate & @.ToDate.

O worlds of pain - got as far as this err :-

[Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification

There's no cast/convert in the SP so I assume it's the dreaded ODBC Driver again.

O why O Why did you guys insist on formatting your dates funny m/d/y instead of the correct way which is British English d/m/y (Flame Flame - tehe) which I assume maybe the problem.

I'll battle on - any suggestions gratefuly received.

GW