Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364

    Unanswered: 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

    GW
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Turn your logic inside out. Create the macro, and use the macro to retrieve the data into Excel then format it.

    -PatP

  3. #3
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364

    Wink

    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
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •