BUG: Cannot Add Chart to Specific Worksheet using Automation to Excel

View products that this article applies to.

This article was previously published under Q245089

Symptoms

When setting an embedded chart's location to a specific worksheet, the chart is always added to the first worksheet in the workbook and not the sheet that you specify.

↑ Back to the top

Cause

This problem occurs when:

You are automating Microsoft Excel. - and -
- and - You set the chart's location using the Chart object's Location method.

↑ Back to the top

Resolution

To work around this problem, do not use the Location method. Instead, use the Add method to add the embedded chart to the ChartObjects collection.

↑ Back to the top

Status

Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of this article. This problem was corrected in Microsoft Excel 2002.

↑ Back to the top

More information

Steps to Reproduce Behavior

Start a new Standard EXE project in Visual Basic.
On the Project menu, click References. Check Microsoft Excel 8.0 Object Library (or Microsoft Excel 9.0 Object Library), and then click OK.

Add a command button to Form1 and add the following code to the Click event for that button:

'Start Excel and create a new workbook
Dim xlsApp As Excel.Application
Dim xlsBook As Excel.Workbook
Dim xlsSheet As Excel.Worksheet

'Start a new workbook in Excel and add a new worksheet to the end of the 
'workbook
Set xlsApp = CreateObject("Excel.Application")
Set xlsBook = xlsApp.Workbooks.Add
Set xlsSheet = xlsBook.Worksheets.Add(after:=xlsBook.Worksheets(xlsBook.Worksheets.Count))

'Add some data to the last sheet in cells A1:C3
xlsSheet.Range("B1:C1").Value = Array("1998", "1999")
xlsSheet.Range("A2:C2").Value = Array("Bill", 500, 200)
xlsSheet.Range("A3:C3").Value = Array("Sue", 333, 555)

'Create a new chart and embed it on the last worksheet
Dim xlsChart As Excel.Chart
Set xlsChart = xlsBook.Charts.Add
xlsChart.SetSourceData Source:=xlsSheet.Range("A1:C3"), PlotBy:=xlColumns
xlsChart.Location Where:=xlLocationAsObject, Name:=xlsSheet.Name

'Make Excel visible and give the user control
xlsApp.Visible = True
xlsApp.UserControl = True

Press the F5 key to run the program. Click the command button on Form1. Results: The embedded chart is added to Sheet1 rather than the last worksheet as expected.

To work around this problem, do not use the Location method to specify a worksheet for the embedded chart. Instead, use the Add method to add the embedded chart to the ChartObjects collection. To implement this workaround in the previous code sample, replace the following

Dim xlsChart As Excel.Chart
Set xlsChart = xlsBook.Charts.Add
xlsChart.SetSourceData Source:=xlsSheet.Range("A1:C3"), PlotBy:=xlColumns
xlsChart.Location Where:=xlLocationAsObject, Name:=xlsSheet.Name

with:

Dim xlsChart As Excel.Chart
Set xlsChart = xlsSheet.ChartObjects.Add(50, 40, 200, 100).Chart
xlsChart.SetSourceData Source:=xlsSheet.Range("A1:C3"), PlotBy:=xlColumns

↑ Back to the top