Creating a Chart Using Data From a Microsoft Office Spreadsheet

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:0002E55D-0000-0000-C000-000000000046 style="width:49%;height:350"></object>
<object id=Spreadsheet1 classid=CLSID:0002E559-0000-0000-C000-000000000046 style="width:49%;height:350"></object>

<b>Source: </b>Industry Data
<p>

<script language=vbscript>
Sub Window_OnLoad()

    Dim chConstants

    Spreadsheet1.ActiveSheet.Cells.Clear
    Spreadsheet1.ActiveSheet.Cells(2, 1).Value = "Car"
    Spreadsheet1.ActiveSheet.Cells(3, 1).Value = "Sport-Utility"
    Spreadsheet1.ActiveSheet.Cells(4, 1).Value = "Truck"
    Spreadsheet1.ActiveSheet.Cells(5, 1).Value = "Minivan"

    Spreadsheet1.ActiveSheet.Cells(1, 2).Value = "1998"
    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 = "1999"
    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 = "2000"
    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 chConstants = 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 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 sales growth data for 1998.
    ' Bind the series name, the category names, and the values.
    ChartSpace1.Charts(0).SeriesCollection(0).SetData chConstants.chDimSeriesNames, chConstants.chDataBound, "B1"
    ChartSpace1.Charts(0).SeriesCollection(0).SetData chConstants.chDimCategories, chConstants.chDataBound, "A2:A5"
    ChartSpace1.Charts(0).SeriesCollection(0).SetData chConstants.chDimValues, chConstants.chDataBound, "B2:B5"

    ' Series two contains sales growth data for 1999.
    ChartSpace1.Charts(0).SeriesCollection(1).SetData chConstants.chDimSeriesNames, chConstants.chDataBound, "C1"
    ChartSpace1.Charts(0).SeriesCollection(1).SetData chConstants.chDimCategories, chConstants.chDataBound, "A2:A5"
    ChartSpace1.Charts(0).SeriesCollection(1).SetData chConstants.chDimValues, chConstants.chDataBound, "C2:C5"

    ' Series two contains sales growth data for 2000.
    ChartSpace1.Charts(0).SeriesCollection(2).SetData chConstants.chDimSeriesNames, chConstants.chDataBound, "D1"
    ChartSpace1.Charts(0).SeriesCollection(2).SetData chConstants.chDimCategories, chConstants.chDataBound, "A2:A5"
    ChartSpace1.Charts(0).SeriesCollection(2).SetData chConstants.chDimValues, chConstants.chDataBound, "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(chConstants.chAxisPositionLeft).NumberFormat = "0%"
    ChartSpace1.Charts(0).Axes(chConstants.chAxisPositionLeft).MajorUnit = 0.1
End Sub
</script>

		

See Also | Creating a Chart Using Data From an ADO Recordset | Creating a Chart Using Data Specified in Arrays