Microsoft KB Archive/243192

= HOWTO: Use VBScript to Bind a Chart to a Spreadsheet Component =

Article ID: 243192

Article Last Modified on 7/13/2004

-

APPLIES TO


 * Microsoft Office Spreadsheet Component 9.0
 * Microsoft Office Chart Component 9.0
 * Microsoft Internet Explorer 5.0

-



This article was previously published under Q243192



SUMMARY
The Microsoft Office Chart Web Component can be used to create a chart using data from different sources, such as arrays, spreadsheets, and databases. This article describes how to use data in a Spreadsheet Web component to create a chart. When a chart is bound to spreadsheet data, changes made to the spreadsheet data are reflected in the chart.



MORE INFORMATION
To connect a chart to spreadsheet data, you can set the Datasource property of the Chartspace object to the Spreadsheet component that contains the data. The example below describes how to bind a spreadsheet control to a chart using VBScript:

Sample Code
<%@ Language=VBScript %> 

Bind a Chart Component to a Spreadsheet Component &#xa0;



 

Sub Window_OnLoad

' Clear the Spreadsheet and fill in values to draw the chart ' The values here are data from three test scores for a class Dim oSheet Set oSheet = Spreadsheet1.ActiveSheet oSheet.Cells.Clear oSheet.Range("A2:A4").Value = Array("Test1", "Test2", "Test3") oSheet.Range("B1:B4").Value = Array("A Grade", 0.07, 0.13, 0.22) oSheet.Range("C1:C4").Value = Array("B Grade", 0.28, 0.42, 0.41) oSheet.Range("D1:D4").Value = Array("C Grade", 0.45, 0.37, 0.23) oSheet.Range("E1:E4").Value = Array("D Grade", 0.11, 0.04, 0.11) oSheet.Range("F1:F4").Value = Array("F Grade", 0.09, 0.04, 0.03)

' Clear the chartspace object and add a new chart Dim oChart ChartSpace1.Clear Set oChart = ChartSpace1.Charts.Add

' Set the Spreadsheet component as the data source for the chart ChartSpace1.DataSource = Spreadsheet1

'Get the constants for the Chart component dim c set c = ChartSpace1.Constants

'Add the data to the chart and set the series names dim oSeries for i=1 to 5 Set oSeries = oChart.SeriesCollection.Add oSeries.SetData c.chDimValues, 0, oSheet.Range(oSheet.Cells(2, i+1), oSheet.Cells(4, i+1)).Address oSeries.SetData c.chDimSeriesNames, 0, oSheet.Cells(1, i+1).Address next oChart.SetData c.chDimCategories, 0, "a2:a4"

' Format the chart to have a legend ChartSpace1.Charts(0).HasLegend = True

' Set the x-Axis to be percentages with 5% intervals oChart.Axes(c.chAxisPositionLeft).NumberFormat = "0%" oChart.Axes(c.chAxisPositionLeft).MajorUnit = 0.05

End Sub   View the page above in Microsoft Internet Explorer and note that the Chart and a Spreadsheet component appear on the page. Change the data in the Spreadsheet and note that the chart is updated accordingly.

