Beefy Boxes and Bandwidth Generously Provided by pair Networks
Don't ask to ask, just ask
 
PerlMonks  

Setting axis data range on Excel chart using Win32::OLE

by apple123 (Initiate)
on Nov 16, 2006 at 22:19 UTC ( [id://584617]=perlquestion: print w/replies, xml ) Need Help??

apple123 has asked for the wisdom of the Perl Monks concerning the following question:

I have written a program that uses Win32::OLE to add data to an Excel file and update a chart with the new data. The examples in Using Win32::OLE and Excel were invaluable, but I'm stuck on one last feature. I need to adjust the data ranges in the chart to include the newly-added data. I'm using the following code: 
$sum_sheet->Range("B1:G2")->Copy();
$sum_sheet->ChartObjects(1)->Chart->SeriesCollection->Paste({CategoryL
+abels=>1, NewSeries=>0});

[download]
This correctly adjusts the data range for the chart Values, but the range for the Category (X) Axis Labels is incorrect as follows:
=(Summary!$B$1:$F$1,Summary!$B$1:$G$1)
when it should be:
=(Summary!$B$1:$G$1)
I have also tried using the SeriesCollection->Extend() method, but the results are similar. I've been banging my head on this wall for a while now. Any suggestions?

Thanks in advance,

Replies are listed 'Best First'.
Re: Setting axis data range on Excel chart using Win32::OLE
by Ponky (Curate) on Nov 17, 2006 at 06:22 UTC
    I've also tried to get this type of thing working previously and given up in disgust! I would try just setting the X Axis values directly after the Paste: 
    $sum_sheet->ActiveChart->SeriesCollection(1)->{XValues} "='Summary!'$B
+$1:$G$1";

    [download]
[reply]
[d/l]
Re: Setting axis data range on Excel chart using Win32::OLE
by Unanimous Monk (Sexton) on Nov 17, 2006 at 15:29 UTC
    If B1:G2 is the only data in the chart, you can use: 
    $sum_sheet->ChartObjects(1)->SetSourceData($sum_sheet->Range("B1:G2"))
+;

    [download]
[reply]
[d/l]

Back to Seekers of Perl Wisdom

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?

www.com | www.net | www.org
Node Status?
node history
Node Type: perlquestion [id://584617]
Approved by planetscape
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? • Last hour • Other CB clients
Other Users?
Others rifling through the Monastery: (1)
As of 2024-04-25 05:51 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found