Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 5 of 5

Thread: Create Dynamic Worksheet Template And Store Resulting Structure In A Sql Table

  1. #1
    Join Date
    22nd November 2004
    Location
    Perth - Australia
    Posts
    74

    Create Dynamic Worksheet Template And Store Resulting Structure In A Sql Table

    Hello,

    I am creating a reporting system that needs to be flexible for reporting purposes. The system creates a number of reports based on data stored in SQL server. What I am trying to achieve is the following:


    1. Show a list of all report template worksheets in one workbook
    2. Allow users to modify the design of the templates (merging cells, rearranging columns, etc)
    3. Store the structure of the report template in a SQL table
    4. Generate the report based on the new structure and SQL data on demand


    I can achieve points 1 and 4, and am not sure if points 2 and 3 are achieveable. Currently, I achieve point 1 by formatting worksheets using VBA modules.

    Is it possible to create a template structure which can be strored in SQL tables? If so, how is it done?
    Cheers,

    Matthew

  2. #2
    Join Date
    15th March 2007
    Location
    LONDON, UK
    Posts
    1,376

    Re: Create Dynamic Worksheet Template And Store Resulting Structure In A Sql Table

    you can't store the structure of a template in an 'SQL' table.

    What you could do how ever is have the user change the template BUT make sure they have named ranges?

    Try keeping this task simple. It's alot more complicated than you think.

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    22nd November 2004
    Location
    Perth - Australia
    Posts
    74

    Re: Create Dynamic Worksheet Template And Store Resulting Structure In A Sql Table

    Thanks for your response Craig,

    As you mention, it's a more difficult task than I first expected. I have pretty much given up on this now after days of trying to find a solution.

    An alterbnative I have come up with is to create User Defined Functions so the template can be more dynamic at the user end without storing the template in a table.
    Cheers,

    Matthew

  4. #4
    Join Date
    28th January 2003
    Location
    Solihull - England
    Posts
    8,676

    Re: Create Dynamic Worksheet Template And Store Resulting Structure In A Sql Table

    Quote Originally Posted by Craig Ottley
    you can't store the structure of a template in an 'SQL' table.
    Well, that's not necessarily true. It depends what you are trying to store ;-)

    Let's say you converted your Excel "reports" to XML, then you could save the XML definition to a SQL databae (along with XSL/XSD formatting if you wanted). SQL 2005 has support for an XML data type etc... too large a subject to cover in a post such as this though - here's some background

    http://technet.microsoft.com/en-us/l.../ms345117.aspx
    http://office.microsoft.com/en-us/ex...895301033.aspx

    I was also wondering why you are using Excel at all for this? Not sure what version of SQL you are using, but this sounds like a fair reason to use a report model or SSRS

    Maybe a little bit more information about what the requirements are might help concentrate my thoughts.
    Kind Regards, Will Riley

    Web Presence:
    Personal: The Trouble With Data
    LinkedIn: Will Riley

  5. #5
    Join Date
    22nd November 2004
    Location
    Perth - Australia
    Posts
    74

    Re: Create Dynamic Worksheet Template And Store Resulting Structure In A Sql Table

    Will,

    I am using Excel as that is what I have been asked to develop the reports in. Once data is extracted, there will be analysis performed in Excel. Another reason the reports are running in Excel is that there will be minimal database support on site after installation. The users on site are reasonably proficient in Excel (as far as users go), and have little to no database skills.

    I am developing this on SQL 2005 Express, but will be migrating to the full version for production. I haven't had much exposure to XML/XSD/XSL, so will have a look at the links you posted.
    Cheers,

    Matthew

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Import Folder Structure into Worksheet & Hyperlink
    By ljones60 in forum EXCEL HELP
    Replies: 14
    Last Post: September 7th, 2006, 20:07
  2. Replies: 2
    Last Post: January 15th, 2006, 20:58
  3. Can query update table structure
    By iwrk4dedpr in forum Excel and/or Access Help
    Replies: 2
    Last Post: October 21st, 2005, 03:30
  4. store worksheet name
    By Gethzerion in forum EXCEL HELP
    Replies: 5
    Last Post: August 12th, 2005, 22:45

Bookmarks

Posting Permissions

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