Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

trying to programmatically change chart data range with vb.net

559 views
Skip to first unread message

doofy

unread,
Feb 5, 2008, 4:15:42 PM2/5/08
to
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

unread,
Feb 5, 2008, 6:05:39 PM2/5/08
to
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
_______


"doofy" <no...@would.nt.be.prudent> wrote in message
news:47a8d203$0$84209$742e...@news.sonic.net...

doofy

unread,
Feb 6, 2008, 10:45:28 AM2/6/08
to

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

unread,
Feb 6, 2008, 3:19:07 PM2/6/08
to
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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"doofy" <no...@would.nt.be.prudent> wrote in message

news:47a9d611$0$84236$742e...@news.sonic.net...

doofy

unread,
Feb 6, 2008, 7:18:45 PM2/6/08
to
Jon Peltier wrote:
> 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

unread,
Feb 6, 2008, 9:44:22 PM2/6/08
to
These forums have traditionally been top-posted, at least for the past 8-10
years.

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


"doofy" <no...@would.nt.be.prudent> wrote in message

news:47aa4e5e$0$84171$742e...@news.sonic.net...

doofy

unread,
Feb 7, 2008, 1:52:18 PM2/7/08
to
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

unread,
Feb 8, 2008, 9:02:25 AM2/8/08
to
Well, there are lots of syntax variations which should work. I guess VB.net
is a little picky.

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


"doofy" <no...@would.nt.be.prudent> wrote in message

news:47ab535b$0$84234$742e...@news.sonic.net...

Kashif Pervaiz

unread,
Dec 9, 2010, 8:49:27 AM12/9/10
to
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,

> On Tuesday, February 05, 2008 4:15 PM doofy wrote:

> 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?


>> On Tuesday, February 05, 2008 6:05 PM Jon Peltier wrote:

>> 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
>> _______
>>
>>
>> "doofy" <no...@would.nt.be.prudent> wrote in message

>> news:47a8d203$0$84209$742e...@news.sonic.net...


>>> On Wednesday, February 06, 2008 10:45 AM doofy wrote:

>>> Jon Peltier wrote:
>>>
>>> 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.


>>>> On Wednesday, February 06, 2008 3:19 PM Jon Peltier wrote:

>>>> 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
>>>> -------
>>>> Jon Peltier, Microsoft Excel MVP
>>>> Tutorials and Custom Solutions
>>>> Peltier Technical Services, Inc. - http://PeltierTech.com
>>>> _______
>>>>
>>>>
>>>> "doofy" <no...@would.nt.be.prudent> wrote in message

>>>> news:47a9d611$0$84236$742e...@news.sonic.net...


>>>>> On Wednesday, February 06, 2008 7:18 PM doofy wrote:

>>>>> Jon Peltier wrote:
>>>>>
>>>>> 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.


>>>>>> On Wednesday, February 06, 2008 9:44 PM Jon Peltier wrote:

>>>>>> These forums have traditionally been top-posted, at least for the past 8-10
>>>>>> years.
>>>>>>
>>>>>> - Jon
>>>>>> -------
>>>>>> Jon Peltier, Microsoft Excel MVP
>>>>>> Tutorials and Custom Solutions
>>>>>> Peltier Technical Services, Inc. - http://PeltierTech.com
>>>>>> _______
>>>>>>
>>>>>>
>>>>>> "doofy" <no...@would.nt.be.prudent> wrote in message
>>>>>> news:47aa4e5e$0$84171$742e...@news.sonic.net...


>>>>>>> On Thursday, February 07, 2008 1:52 PM doofy wrote:

>>>>>>> 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 wrote:


>>>>>>>> On Friday, February 08, 2008 9:02 AM Jon Peltier wrote:

>>>>>>>> Well, there are lots of syntax variations which should work. I guess VB.net
>>>>>>>> is a little picky.
>>>>>>>>
>>>>>>>> - Jon
>>>>>>>> -------
>>>>>>>> Jon Peltier, Microsoft Excel MVP
>>>>>>>> Tutorials and Custom Solutions
>>>>>>>> Peltier Technical Services, Inc. - http://PeltierTech.com
>>>>>>>> _______
>>>>>>>>
>>>>>>>>
>>>>>>>> "doofy" <no...@would.nt.be.prudent> wrote in message
>>>>>>>> news:47ab535b$0$84234$742e...@news.sonic.net...


>>>>>>>> Submitted via EggHeadCafe
>>>>>>>> Microsoft ASP.NET For Beginners
>>>>>>>> http://www.eggheadcafe.com/training-topic-area/ASP-NET/7/ASP.aspx

Kashif Pervaiz

unread,
Dec 9, 2010, 8:51:34 AM12/9/10
to
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,

> On Tuesday, February 05, 2008 4:15 PM doofy wrote:

> 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?


>> On Tuesday, February 05, 2008 6:05 PM Jon Peltier wrote:

>> 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
>> _______
>>
>>
>> "doofy" <no...@would.nt.be.prudent> wrote in message

>> news:47a8d203$0$84209$742e...@news.sonic.net...


>>> On Wednesday, February 06, 2008 10:45 AM doofy wrote:

>>> Jon Peltier wrote:
>>>
>>> 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.


>>>> On Wednesday, February 06, 2008 3:19 PM Jon Peltier wrote:

>>>> 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
>>>> -------
>>>> Jon Peltier, Microsoft Excel MVP
>>>> Tutorials and Custom Solutions
>>>> Peltier Technical Services, Inc. - http://PeltierTech.com
>>>> _______
>>>>
>>>>
>>>> "doofy" <no...@would.nt.be.prudent> wrote in message

>>>> news:47a9d611$0$84236$742e...@news.sonic.net...


>>>>> On Wednesday, February 06, 2008 7:18 PM doofy wrote:

>>>>> Jon Peltier wrote:
>>>>>
>>>>> 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.


>>>>>> On Wednesday, February 06, 2008 9:44 PM Jon Peltier wrote:

>>>>>> These forums have traditionally been top-posted, at least for the past 8-10
>>>>>> years.
>>>>>>
>>>>>> - Jon
>>>>>> -------
>>>>>> Jon Peltier, Microsoft Excel MVP
>>>>>> Tutorials and Custom Solutions
>>>>>> Peltier Technical Services, Inc. - http://PeltierTech.com
>>>>>> _______
>>>>>>
>>>>>>
>>>>>> "doofy" <no...@would.nt.be.prudent> wrote in message
>>>>>> news:47aa4e5e$0$84171$742e...@news.sonic.net...

>>>>>>> On Thursday, February 07, 2008 1:52 PM doofy wrote:

>>>>>>> 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 wrote:


>>>>>>>> On Friday, February 08, 2008 9:02 AM Jon Peltier wrote:

>>>>>>>> Well, there are lots of syntax variations which should work. I guess VB.net
>>>>>>>> is a little picky.
>>>>>>>>
>>>>>>>> - Jon
>>>>>>>> -------
>>>>>>>> Jon Peltier, Microsoft Excel MVP
>>>>>>>> Tutorials and Custom Solutions
>>>>>>>> Peltier Technical Services, Inc. - http://PeltierTech.com
>>>>>>>> _______
>>>>>>>>
>>>>>>>>
>>>>>>>> "doofy" <no...@would.nt.be.prudent> wrote in message
>>>>>>>> news:47ab535b$0$84234$742e...@news.sonic.net...


>>>>>>>>> On Thursday, December 09, 2010 8:48 AM Kashif Pervaiz wrote:

>>>>>>>>> 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,


>>>>>>>>> Submitted via EggHeadCafe
>>>>>>>>> Microsoft LINQ Query Samples For Beginners
>>>>>>>>> http://www.eggheadcafe.com/training-topic-area/LINQ-Standard-Query-Operators/33/LINQ-Standard-Query-Operators.aspx

0 new messages