Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2003

    Unanswered: DTS to Excel- HEDER REQUIRED-- Allan please help

    Hello Gurus,
    I have created a query to create the Excel report. Everything is working fine. My client wants a HEADING In this excel spread sheet eg:"DAILY REPORT bla.." . My questions are

    1. Is there any easy way to do it ?
    2 Do I need to write custom script?

  2. #2
    Join Date
    Jul 2003
    The Dark Planet
    Can you tell me how did you create a excel report using a query ... I am stuck on the problem.

  3. #3
    Join Date
    Aug 2003
    Andover, MA
    I wrestled for a while with pushing data into Excel using various methods, all of which had some sort of problem. I finally opted for a solution I think can't be beat...

    I create a simple Excel Template for each report. It includes a Macro than runs the database query and self-populates, then applies any additional formatting required. Since this is all from within Excel, it works very smoothly, and I have access to all functionality.

    To create the basic Macro, turn on the Macro Recorder. Perform a database query (Tools/Import External Data/New Database Query, then follow the wizard). It will use MSQuery to retrieve the data. If you want to run a Stored Procedure, simply start the wizard, click Cancel, and when prompted to edit the query, click yes. Close the table selector, and click the SQL button to edit the query. You can enter "EXEC myStoredProcedure" or whatever there, and it will run. Simply closing MSQuery will return the data to Excel.

    After that, perform the formatting you want, then stop the Macro Recorder. Open the VB editor, and you should see a Macro with all the basic code you need, ready for tweaking.

    If you need to collect parameters for the report, create a simply UserForm. I have several reports that propt the user for the month and year this way using two comboboxes.

    Here's the best part -- I give the report to the end user, and they can run it anytime they want, no more coming to me! I can control permissions on the DB side. I like to do this be specifying NT Authentication when creating the query and simply adding.removing object permissions as needed. SQL authentication will work as well, just that anyone with the template can use it.

Posting Permissions

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