How to disable automatic creation SeriesCollection in Excel Chart

I have already thi开发者_开发技巧s code

Excel::_ApplicationPtr app("Excel.Application");

app->Visible[0] = false;

Excel::_WorkbookPtr book = app->Workbooks->Add();
Excel::_WorksheetPtr sheet = book->Worksheets->Item[1];

RangePtr pRange = sheet->Cells;
RangePtr pCell;

pCell = pRange->Item[1][1]; // A1
pCell->Value2 = "1";
pCell = pRange->Item[1][2]; // B1
pCell->Value2 = "1";
pCell = pRange->Item[1][3]; // C1
pCell->Value2 = "10";

pCell = pRange->Item[2][1]; // A2
pCell->Value2 = "3";
pCell = pRange->Item[2][2]; // B2
Cell->Value2 = "1";
pCell = pRange->Item[2][3]; // C2
pCell->Value2 = "20";

and next

Excel::RangePtr pBeginRange = pRange->Item[1][1];
Excel::RangePtr pEndRange = pRange->Item[5][9];
Excel::RangePtr pTotalRange = sheet->Range[(Excel::Range *)pBeginRange][(Excel::Range *)pEndRange];

_ChartPtr  pChart2  = book->Charts->Add();
pChart2->ChartType = xlBubble3DEffect; 

pChart2->SetSourceData((Excel::Range *)pTotalRange, (long)Excel::xlColumns);

How to disable automatic creation SeriesCollection in Excel Chart. I want to set the ranges manually. In the automatic creation all SeriesCollection has XValues in the first column. But I needn't it.

---

I've come across the same issue as well. From experimentation I think the following is happening:

When you create a new chart in VBA using the following code

Dim chSheet As Chart
Set chSheet = Charts.Add

Excel, trying to be helpful I bet, automatically looks at which ever worksheet your cursor had selected when you executed your code from the developer window and searches for the nearest dataset it thinks could be a range of values for the graph. This is then automatically populated in the graph. The only way around it I've found so far is to execute the following code to delete all series objects in the series collection object on the chart immediately after having created it. Touch counter intuitive but it works...

Public Sub DeleteChartSeries(chartSheet As Chart)

    'Shorter but perhaps less clean way of writing the code compared to below
    Do Until chartSheet.SeriesCollection.Count = 0
        chartSheet.SeriesCollection(1).Delete
    Loop

    'With chartSheet
    '    Do Until .seriesCollection.Count = 0
    '        .seriesCollection(1).Delete
    '    Loop
    'End With

End Sub

---

Just hit a workaround: First move the cursor somewhere outside the UsedRange, then create the Chart: Won't do auto-detection. Then move back.

In other words, the following code works for me (Excel 2007):

' Assume src As Range (proposed source data for the chart) in the ActiveSheet.

' put the cursor somewhere outside the UsedRange to avoid Excel's
' 'helpful' auto detection.
Dim ur As Range
Set ur = src.Worksheet.UsedRange
ur.Cells(1, ur.Columns.Count).Offset(0, 2).Select

Dim crt As Chart
Set crt = src.Worksheet.Shapes.AddChart().Chart
' ^ does NOT do auto-detection.

---

Probably can't prevent it being created, but you can delete it immediately after creating the chart, then create you own series as required.

In VBA:

Set Chrt = wb.Charts.Add()
Chrt.SeriesCollection(1).Delete