BUG: Cannot Add Chart to Specific Worksheet using Automation to Excel (245089)

MORE INFORMATION

Steps to Reproduce Behavior

1. Start a new Standard EXE project in Visual Basic.
2. On the Project menu, click References. Check Microsoft Excel 8.0 Object Library (or Microsoft Excel 9.0 Object Library), and then click OK.
3. 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
   					

4. 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