Microsoft KB Archive/245089

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

Article ID: 245089

Article Last Modified on 12/12/2003

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition
 * Microsoft Excel 97 Standard Edition

-



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.



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.



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.



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.



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

<div class="references_section">