This example creates a chart from data specified in a Microsoft Office spreadsheet. The chart is bound to the spreadsheet using the DataSource property, and then the SetData method is used to set the chart data from ranges on the spreadsheet.
To run this example, copy the remainder of the text in this topic into an HTML page.
<object id=ChartSpace1 classid=CLSID:0002E500-0000-0000-C000-000000000046 style="width:49%;height:350"></object>
<object id=Spreadsheet1 classid=CLSID:0002E510-0000-0000-C000-000000000046 style="width:49%;height:350"></object>
<br><small><b>Source: </b>Voters Research and Surveys</small><p>
<script language=vbs>
Sub Window_OnLoad()
' This example starts by putting the data into Spreadsheet1. Normally,
' the data would already be loaded, but this code was added for completeness.
Spreadsheet1.ActiveSheet.Cells.Clear
Spreadsheet1.ActiveSheet.Cells(2, 1).Value = "White"
Spreadsheet1.ActiveSheet.Cells(3, 1).Value = "Black"
Spreadsheet1.ActiveSheet.Cells(4, 1).Value = "Asian"
Spreadsheet1.ActiveSheet.Cells(5, 1).Value = "Latino"
Spreadsheet1.ActiveSheet.Cells(1, 2).Value = "Perot"
Spreadsheet1.ActiveSheet.Cells(2, 2).Value = 0.2
Spreadsheet1.ActiveSheet.Cells(3, 2).Value = 0.06
Spreadsheet1.ActiveSheet.Cells(4, 2).Value = 0.17
Spreadsheet1.ActiveSheet.Cells(5, 2).Value = 0.13
Spreadsheet1.ActiveSheet.Cells(1, 3).Value = "Clinton"
Spreadsheet1.ActiveSheet.Cells(2, 3).Value = 0.38
Spreadsheet1.ActiveSheet.Cells(3, 3).Value = 0.82
Spreadsheet1.ActiveSheet.Cells(4, 3).Value = 0.28
Spreadsheet1.ActiveSheet.Cells(5, 3).Value = 0.62
Spreadsheet1.ActiveSheet.Cells(1, 4).Value = "Bush"
Spreadsheet1.ActiveSheet.Cells(2, 4).Value = 0.42
Spreadsheet1.ActiveSheet.Cells(3, 4).Value = 0.12
Spreadsheet1.ActiveSheet.Cells(4, 4).Value = 0.55
Spreadsheet1.ActiveSheet.Cells(5, 4).Value = 0.25
' Clear the contents of the chart workspace. This removes
' any old charts that may already exist and leaves the chart workspace
' completely empty. One chart object is then added.
ChartSpace1.Clear
ChartSpace1.Charts.Add
Set c = ChartSpace1.Constants
' Set the chart DataSource property to the spreadsheet.
' It is possible to specify multiple data sources, but this example uses only one.
ChartSpace1.DataSource = Spreadsheet1
' Add three series to the chart.
ChartSpace1.Charts(0).SeriesCollection.Add
ChartSpace1.Charts(0).SeriesCollection.Add
ChartSpace1.Charts(0).SeriesCollection.Add
' Connect the chart to data by specifying spreadsheet cell references
' for the different data dimensions. Notice that the SetData method uses
' a data source index of 0; this is the first data source, which was previously
' set to the spreadsheet. If you had created multiple data sources,
' you could specify the index to any item in the WCDataSources collection for the
' data source index. For example, if two spreadsheet controls were attached to this
' chart workspace, you could set data from the first control using index 0
' and set data from the second control using index 1.
' Notice that the series name is also bound to a spreadsheet cell. Changing
' the contents of the cell "B1" will also change the name that appears in the legend.
' If you don't want this behavior, set SeriesCollection(0).Caption instead of
' using the SetData method to bind the series name to the spreadsheet.
' Series one contains election data for Perot.
' Bind the series name, the category names, and the values.
ChartSpace1.Charts(0).SeriesCollection(0).SetData c.chDimSeriesNames, 0, "B1"
ChartSpace1.Charts(0).SeriesCollection(0).SetData c.chDimCategories, 0, "A2:A5"
ChartSpace1.Charts(0).SeriesCollection(0).SetData c.chDimValues, 0, "B2:B5"
' Series two contains election data for Clinton.
ChartSpace1.Charts(0).SeriesCollection(1).SetData c.chDimSeriesNames, 0, "C1"
ChartSpace1.Charts(0).SeriesCollection(1).SetData c.chDimCategories, 0, "A2:A5"
ChartSpace1.Charts(0).SeriesCollection(1).SetData c.chDimValues, 0, "C2:C5"
' Series two contains election data for Bush.
ChartSpace1.Charts(0).SeriesCollection(2).SetData c.chDimSeriesNames, 0, "D1"
ChartSpace1.Charts(0).SeriesCollection(2).SetData c.chDimCategories, 0, "A2:A5"
ChartSpace1.Charts(0).SeriesCollection(2).SetData c.chDimValues, 0, "D2:D5"
' Make the chart legend visible, format the left value axis as percentage,
' and specify that value gridlines are at 10% intervals.
ChartSpace1.Charts(0).HasLegend = True
ChartSpace1.Charts(0).Axes(c.chAxisPositionLeft).NumberFormat = "0%"
ChartSpace1.Charts(0).Axes(c.chAxisPositionLeft).MajorUnit = 0.1
End Sub
</script>