Using worksheet data in the chart office web component

[carlmack]

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

Private Sub UserForm_Initialize()

Dim ChtSpc As OWC11.ChartSpace
Dim cht As OWC11.ChChart
Dim Sps As OWC11.Spreadsheet
Dim ws As Worksheet

Set ChtSpc = Me.ChartSpace1
Set Sps = Me.Spreadsheet1
Set ws = ThisWorkbook.Worksheets("ChartData") ' change to you worksheet name

Sps.Range("A1:Z1000") = ws.Range("A1:Z1000").Value ' Set worksheet range to sheet control range
Set ChtSpc.DataSource = Sps ' set sheet control as chart control source
Set cht = ChtSpc.Charts.Add ' Add blank chart

' Set data for chart
With cht
    .SetData chDimCategories, 0, "A4:A15" ' change to your category range
    .SeriesCollection(0).SetData chDimValues, 0, "B4:B15" ' change to your series 1 range
    .HasLegend = True
    .SeriesCollection.Add
    .SeriesCollection(1).SetData chDimValues, 0, "C4:C15" ' change to your series 2 range
    .HasTitle = True
    .Title.Caption = Sps.Range("A1") ' change to your title cell
    .Title.Interior.Color = 1677215
    .Type = chChartTypeColumn3D
End With

' add some fancy shading
ChtSpc.Interior.SetTwoColorGradient chGradientFromCenter, chGradientVariantEnd, 9125192, 16777215

' hide the sheet control
Me.Spreadsheet1.Visible = False
' set the height of the chart control
Me.ChartSpace1.Height = 480

End Sub

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 :- http://www.microsoft.com/downloads/d...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.

Carl

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

[Will Riley]

Carl,

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
http://www.activeinterface.com/b2004_12_2.html