Edit an imbedded chart programatically that was not added at runtime

Question

In one of my Excel templates there is a preset chart that auto fills with data gained from an automated table.

That all works just fine. The part I am having trouble with is how to refer to that chart later in my code.

The best I can gather is using something like Main.xlBook.Charts(6), where 6 is the index of the chart in the xlBook.

    Dim chart As Excel.Chart = CType(Main.xlBook.Charts(6), Excel.Chart)
    chart.Axes(Excel.XlCategoryType.xlTimeScale).MinimumScale = Main.xLSheet1.Cells(2, 1).value / 86277
    chart.Axes(Excel.XlCategoryType.xlTimeScale).MaximumScale = Main.xLSheet1.Cells(radios.Count + 1, 1).value / 86277

Using the code above, it breaks when I try to set the object. I don't want to make make the chart at runtime, because in most cases I do not need to edit the chart at all. The way I have it automated, it fills in correctly 95% of the time. I just need to be able to adjust the ranges the other 5%.

So is there a way to refer to a chart object in an xlbook that was not made at runtime?

ps, as a bonus second question, how would I change the axis with option strict turned on? (the way I have it currently is not option strict friendly)

Thanks guys as always

Update

Figured it out

    Dim xlCharts As Excel.ChartObjects = CType(Main.xlSheet3.ChartObjects, Excel.ChartObjects)
    Dim myChart As Excel.ChartObject = xlCharts.Item(5)
    Dim chartPage As Excel.Chart = myChart.Chart
    chartPage.Axes(Excel.XlAxisType.xlCategory).minimumscale = Main.xLSheet1.Cells(2, 1).value
    chartPage.Axes(Excel.XlAxisType.xlCategory).maximumscale = Main.xLSheet1.Cells(radios.Count + 1, 1).value

score 0 · Answer 1 · answered Oct 17 '13 at 22:58

0

In your original code set a name for the chart. Then you can just call the chart by name:

Charts("Sales").Activate

answered Oct 17 '13 at 22:58

Automate This

30,726
11
60
82

it returns this error: Invalid index. (Exception from HRESULT: 0x8002000B (DISP_E_BADINDEX))... and I know that its called "Chart 6" because I created a macro and that's what excel is calling it... is there a way to get all objects in an excel workbook? – Nefariis Oct 17 '13 at 23:09
@Nefarii - `Charts(6)` is not always called "Chart 6" – Tim Williams Oct 17 '13 at 23:13
ive tried numbers 0-10 with nothing. Main.xlSheet3.ListObjects.Count = 0 and Main.xlSheet3.OLEObjects.Count = 0 ... sooo I guess there is no way to identify an object unless its created at runtime? – Nefariis Oct 17 '13 at 23:29
You can get the chart one way or another. Try recording a macro and click on the border of the chart, then stop recording. The code will give you the chart name and look something like this: ActiveSheet.ChartObjects("Chart 2").Activate – Automate This Oct 17 '13 at 23:49
You can also use this [code](http://accessprogrammer.blogspot.com/2005/05/loop-through-charts-in-excel-worksheet.html) to loop through all charts in document and display the name. – Automate This Oct 17 '13 at 23:52
It's "Chart 6", but something that doesnt look promising - MsgBox(Main.xLSheet1.OLEObjects.count & " " & Main.xLSheet2.OLEObjects.count & " " & Main.xlSheet3.OLEObjects.count & " " & Main.xLBook.Charts.Count & " " & Main.xLApp.Charts.Count) returns "0 0 0 0 0" ... It honestly looks like that if the chart was added to excel manually instead of at runtime, that it cant be returned as an object – Nefariis Oct 17 '13 at 23:53
Incase your names are different try this: MsgBox (ActiveSheet.ChartObjects.Count) – Automate This Oct 17 '13 at 23:58
The error is now this: Unable to cast COM object of type 'System.__ComObject' to interface type 'Microsoft.Office.Interop.Excel.Chart'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{000208D6-0000-0000-C000-000000000046}' failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)). .... it looks like i need to do something like this to fix it: http://stackoverflow.com/questions/12957595/error-accessing-com-components – Nefariis Oct 18 '13 at 00:14

score 0 · Accepted Answer · answered Jan 23 '14 at 23:50

In order to use minimumscale and maximumscale, you have you chartobject and set the chart

Dim xlCharts As Excel.ChartObjects = CType(Main.xlSheet3.ChartObjects, Excel.ChartObjects)
Dim myChart As Excel.ChartObject = xlCharts.Item(5)
Dim chartPage As Excel.Chart = myChart.Chart
chartPage.Axes(Excel.XlAxisType.xlCategory).minimumscale = Main.xLSheet1.Cells(2, 1).value
chartPage.Axes(Excel.XlAxisType.xlCategory).maximumscale = Main.xLSheet1.Cells(radios.Count + 1, 1).value

Hope this helps

Edit an imbedded chart programatically that was not added at runtime

2 Answers2