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

Article translations Article translations
Article ID: 243192 - View products that this article applies to.
This article was previously published under Q243192
Expand all | Collapse all

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 %>
<HTML>

<STRONG><CENTER>Bind a Chart Component to a Spreadsheet Component &#xa0;</CENTER></STRONG>

<BODY>

<object id=ChartSpace1 classid=CLSID:0002E500-0000-0000-C000-000000000046 style="width:100%;height:480"></object>
<object id=Spreadsheet1 classid=CLSID:0002E510-0000-0000-C000-000000000046 style="width:100%;height:480"></object>

<script language=vbs>
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
</script>
</BODY>
</HTML>
				
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.

REFERENCES

For additional information about using the Chart Web Component, please click on the numbers below to view the articles in the Microsoft Knowledge Base:
240263 HOWTO: Create a Combination Chart with the Chart Web Component
235885 HOWTO: Use the Office Chart Web Component With VB

Properties

Article ID: 243192 - Last Review: July 13, 2004 - Revision: 3.1
APPLIES TO
  • Microsoft Office Spreadsheet Component 9.0
  • Microsoft Office Chart Component 9.0
  • Microsoft Internet Explorer 5.0
Keywords: 
kbhowto kbofficewebchart kbofficewebspread KB243192
Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com