VB.NET Excel Automation of Charts with Named Variables

[fergy2004]

Hi all! I'm new to the forums and hope I am placing this post in the correct location. I'm having a lot of trouble automating charts from my VB.NET program. I'm simply graphing one series of data as a time series that must maintain its order as the user filters the data, deletes some data, etc. Because of the changes the user can make, I am using a named variable with the offset command to maintain a dynamic range. I am aware of how to manually build the graph by supplying the series range =workbookname.xls!namedvariable, but I can't figure out how to supply this to my chart from my VB program. The following is some of my VB.NET code.

xlapp.names.add("graph" & i, "=OFFSET(Hidden!$A$20,0," & 1 + i * 3 & ",Hidden!$A$20,1)")
chartrange= ???????
chartPage.ChartWizard(Source:=chartRange, HasLegend:=False, Title:=ParameterNames(i) & " Time Series")

I can supply more code if needed, but figured this would give a general idea of what I am trying to do. This has to work across multiple Office platforms so I am using late binding. All help is greatly appreciated!

 

[Legacy 98055]

Is this an embedded chart of a chart sheet?

 

[fergy2004]

this will be one of a number of embedded charts on a worksheet named "Charts"

 

[Legacy 98055]

It really depends on the context. Here are two of a handful of options.

xlapp.ActiveChart.SetSourceData "=NamedRange"
xlapp.ActiveWorkbook.Worksheets("Charts").ChartObjects("Chart 1").Chart.SetSourceData "=NamedRange"

I don't know but you may need the parenthesis in vb.net

xlapp.ActiveChart.SetSourceData("=NamedRange")
xlapp.ActiveWorkbook.Worksheets("Charts").ChartObjects("Chart 1").Chart.SetSourceData("=NamedRange")

 

[fergy2004]

Unfortunately, I have tried variations like this and I get a type mismatch error when I try to setsourcedata. Here is a little more of my code, it might help:

Dim chartPage As Object
Dim xlCharts As Object
Dim myChart As Object
Dim chartRange As Object
Dim ypos As Integer = 10

For i = 1 To ParameterCount - 1
chartRange = xlWorkSheet.names.add("graph" & i, "=OFFSET(Hidden!$A$20,0," & 1 + i * 3 & ",Hidden!$A$20,1)")
xlWorkSheet = xlbook.WorkSheets("Charts")
xlCharts = xlWorkSheet.ChartObjects
myChart = xlCharts.Add(100, ypos, 800, 250) '(xpos, ypos, xlength, ylength)
chartPage = myChart.chart
chartPage.SetSourceData(Source:="=graph1") '????????? This isn't working
chartPage.ChartType = 65 'xlLineMark chart type
chartPage.ChartWizard(HasLegend:=False, Title:=ParameterNames(i) & " Time Series")
chartPage.Axes(1).CategoryType = 3 'Sets the x-axis as a date type to keep time series order, 3=Microsoft.Office.Interop.Excel.XlCategoryType.xlTimeScale()
chartPage.Axes(1).Delete() 'Removes x-axis from graph, 1=Microsoft.Office.Interop.Excel.XlAxisType.xlCategory()
ypos += 260
Next

Thank you!

 

[Legacy 98055]

chartPage.SetSourceData(Source:="=graph1") '????????? This isn't working

chartPage.SetSourceData(Source:= "=" & chartRange.ToString)

or more likely

chartPage.SetSourceData(Source:= "=" & chartRange.name)

This???

 

[fergy2004]

Still no luck, I'm still getting a type mismatch exception.

chartRange = xlWorkSheet.names.add("graph" & i, "=OFFSET(Hidden!$A$20,0," & 1 + i * 3 & ",Hidden!$A$20,1)")

This should have been the following:

xlWorkSheet.names.add("graph" & i, "=OFFSET(Hidden!$A$20,0," & 1 + i * 3 & ",Hidden!$A$20,1)")

I have tried a lot of different scenarios and left that in the code by mistake. I'm sure this a simple syntax problem, but I cannot seem to iron it out.

 

[Legacy 98055]

Just post all of your relevant code or email me your VB project. Are you sure the dymanic range is returning valid data?

tstom1970 at gmail dot com

 

[tusharm]

When you used a named formula in a chart you have to include the 'full' name. If the scope of the name is the workbook, use {workbook name}!{name}.

For the correct syntax, use the Excel macro recorder. Then, adapt the code to .Net

Hi all! I'm new to the forums and hope I am placing this post in the correct location. I'm having a lot of trouble automating charts from my VB.NET program. I'm simply graphing one series of data as a time series that must maintain its order as the user filters the data, deletes some data, etc. Because of the changes the user can make, I am using a named variable with the offset command to maintain a dynamic range. I am aware of how to manually build the graph by supplying the series range =workbookname.xls!namedvariable, but I can't figure out how to supply this to my chart from my VB program. The following is some of my VB.NET code.

xlapp.names.add("graph" & i, "=OFFSET(Hidden!$A$20,0," & 1 + i * 3 & ",Hidden!$A$20,1)")
chartrange= ???????
chartPage.ChartWizard(Source:=chartRange, HasLegend:=False, Title:=ParameterNames(i) & " Time Series")

I can supply more code if needed, but figured this would give a general idea of what I am trying to do. This has to work across multiple Office platforms so I am using late binding. All help is greatly appreciated!

 

[fergy2004]

Well, I solved it for the most part by a combination of things. The data that my named variable referred to wasn't "created" yet because of the order my code was in. So I just moved that before all of this named variable code. The next thing I did was the following:

Dim t_Series As Object
t_Series = chartPage.SeriesCollection.NewSeries()
With t_Series
.Name = "Series 1"
'.XValues = xlWorkSheet.Range("$A$4:$A$" & (RecordsReceived + 3) & ",$" & col & "$4")
.Values = "=Data!graph" & i
End With

I had gone down this path to define my data before but I wasn't able to use .XValues using late binding. Since I don't need that using named variables, I simply commented it out and it was able to run this code using late binding. I was aware of needing to give it a full name as well but I was scoping it to the entire workbook, not a single worksheet. A small but necessary change since VB didn't like =test.xls!graph1 but does like =Data!graph1.

What sent me down this path in the beginning is that Office 07 handles all of this as expected but Office 03 seems to do things its own way.

The only problem I have now is that Office 03 takes a snapshot of the range my named variable represents and places that in the graph definition instead of the reference to the named variable. This leaves wasted space on the graph when data is removed since the range doesn't change at all. I would love to fix this still if possible.