Using worksheet data in the chart office web component

  • I have been looking at the Office Web Components and was thinking they need some publicity among Ozgrid Excel users.

    The Office Web Components (OWC) are a series of components that you can add into a web page or any Office application. In this post I will be looking at adding a chart component to an Excel Userform.

    One advantage of using a chart component rather than using the normal chart object is that you can put it on a userform. There is a problem though - it is not easy to reference data from a worksheet like you do with the normal chart object.

    Below demonstrates how you can use the OWC Spreadsheet component to take the data from a normal worksheet and pass it to the chart control.

    I am using Office 2003 which comes with OWC11 but with a little editing you should be able use the same techniques with earlier versions.

    You need to create a new userform (from the VBE Insert > Userform) and add both a OWC spreadsheet control and a OWC chart control to the userform. Click the toolbox icon > Right click the tool box > additional controls > then check Microsoft Office Spreadsheet 11.0 and Microsoft Office Chart 11.0. The icons should now appear on the tool box and you can drag them on to the userform.

    After you have entered your data into a normal worksheet you can build the userform initalisation event to copy the data from the worksheet to the spreadsheet control and then set the spreadsheet control as source for the chart control. You can then set the data in the chart using the same cell references as in your worksheet.

    Example code

    In the above example I have copied the range("A1:Z1000") across. Given that most people live in this range you can then copy this code as is, and just change the "Set data for chart" section to your ranges.

    Attached is an example workbook. It is based on OWC11 so you need Office 2003 to use it. You can download OWC11 at :-…FD290D4B76&displaylang=en

    In the example workbook have a look at the data in the worksheet (ChartData) then press the command button (Click This). A userform should appear with the chart component on. Remember this only works if you have Office 2003. Click the rotate button then right click the chart and select "commands and options", then select the 3D view and play with the slide bars.

    In the next post I will try and demonstrate using OWC with OLAP.


    Thanks to XL Dennis and Will Riley for lighting the way.

  • Re: Using worksheet data in the chart office web component


    Excellent post. I have added below some useful links on OWC programming & use from our post in the "other" forum


    Re XML stuff (and OWC in general)

    Dennis' chart OWC post here shows one way of using an XML file for an OWC chart.

    Here are also some KB articles
    How to use the Office Web Components with XML data
    How To Use XML Data with the Chart Component

    and just in case you haven't yet found the (little) programming help that is available
    HOW TO: Find Office Web Components (OWC) Programming Documentation and Samples

    Here's also a nice article