trying to programmatically change chart data range with vb.net

[doofy]

I'm trying to adjust the data range for a chart, based on how many rows
of data have been imported in. The data range will be in the same
worksheet as the chart.

rowEnrolling and rowCnt are integers, but using integers to concat to a
string has been working so far.

Here's my line of code:

ws.ChartObjects(1).chart.seriescollection(1).values = ws.Range("f" &
rowEnrolling & ":f" & rowCnt - 1)

and I get a "data type mismatch", I'm assuming that the
seriescollection(1).values is not matching the ws.range

Any pointers on this?

 

[Jon Peltier]

Well, rows go up to at least 65536, which is twice the largest integer.
Perhaps if you use Longs instead it might work. You could be really
compulsive and use CLng(rowCnt - 1) or whatever vb.net uses for CLng.

- Jon

 

[doofy]

Well, rows go up to at least 65536, which is twice the largest integer.
Perhaps if you use Longs instead it might work. You could be really
compulsive and use CLng(rowCnt - 1) or whatever vb.net uses for CLng.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______

I'm only porting over 20 rows at this time, and it should never go over
100. So, I don't think that's the issue, though I could be wrong.

I think it's the syntax and sequencing of the code, though I'm finding
little on the web to help me, and nothing in books I have.

 

[Jon Peltier]

Post on top like everyone else does, so it's easier to follow the thread.

The syntax looks okay, but I'm using VBA. The VB.net code I've seen often
has added syntactical elements which I don't recognize.

The variable type may not care how many rows you're accessing, it it's
expecting a long to accommodate the largest possible row number. VBA is easy
about this, not being strongly typed, but maybe dot net is stricter.

One thing to check is whether the series is displayed before this command is
executed. Line and Scatter charts that do not display for whatever reason
(only error values in the range) cannot be fully accessed by VBA. You
usually can change to a column chart type first, fix the series, then change
back.

- Jon

 

[doofy]

Post on top like everyone else does, so it's easier to follow the thread.

I believe this would be the only newsgroup where I was asked to top
post. Are you sure the net nanny inquisition knows what you're doing
here? ;-)

I'll look into what you said about the longs.

 

[Jon Peltier]

These forums have traditionally been top-posted, at least for the past 8-10
years.

- Jon

 

[doofy]

My reader automatically drops me to the bottom.

This code worked for me:

'set data range
oSeries = ws.ChartObjects(1).chart.seriescollection(1)
'names
oSeries.XValues = ws.Range("b" & rowEnrolling, "b" &
rowCnt - 1)
'values
oSeries.Values = ws.Range("f" & rowEnrolling, "f" &
rowCnt - 1)

 

[Jon Peltier]

Well, there are lots of syntax variations which should work. I guess VB.net
is a little picky.

- Jon

 

[Kashif Pervaiz]

Hi,
I have the excel part working however when trying to Export the excel file as PDF, it just hangs. I have made the changes discussed in several posts for "dcomcnfg" which has the excel working, however it just hangs when trying to export as PDF. Any help will be greatly appreciated.

thanks,

 

[Kashif Pervaiz]

Another question I have:

I remember previously that I had been able to assign a string to Range object. Now, it asks for two cells. Why can't I assign a prebuilt string to range to assign to charts?

thanks,