Re: Scripting Excel Charts
Re: Scripting Excel Charts
- Subject: Re: Scripting Excel Charts
- From: Paul Berkowitz <email@hidden>
- Date: Fri, 04 Aug 2006 19:26:11 -0700
- Thread-topic: Scripting Excel Charts
Title: Re: Scripting Excel Charts
On 8/4/06 5:21 PM, "Paul Berkowitz" <email@hidden> wrote:
In the UI, by selecting the X-axis, choosing Format/Selected Axis.../Number/Time, I can change the format from the default 13:30:55 to 13:30, which is neater and appropriate here (no seconds needed, only minutes). I can't do this in AppleScript. In the UI, there's a really neat conversion, whereby if I enter "00:30:00" for Major Unit in Scale tab, it gets converted to the correct floating point value of 0.02083333333333 (as you can see if you close and then re-open the Format/Axis dialog), and appears correct as 00:30:00 or 00:30 (depending on the Number format you chose) in the chart. There's no neat conversion available in VBA or AppleScript, as far as I can see: you really have to set the major unit property to 0.02083333333333. I guess that's OK, if too bad. But I still can't change the resulting format from 00:30:00 to 00:30.
OK. I just got some VBA help on this, which I adapted to AppleScript. You can do both these things (specify 30 minutes units, without seconds), easily. In the script I sent, replace the last effective line ( set major unit of xAxis to 0.020833333333 ) with this:
tell xAxis
set major unit to 30 / 1440
set number format of its tick labels to "h:mm"
end tell
(There are 1440 minutes in a day, and Excel uses a day, and decimal fractions of it, to specify time.)
The whole script:
tell application "Microsoft Excel"
set theRange to range "A1:B3" of active sheet -- or used range of active sheet
set theChartObject to make new chart object at active sheet with properties {top:169.0, left position:461.0, height:295.0, width:431.0}
set theChart to chart of theChartObject
set source data theChart source theRange plot by by columns
tell theChart
set chart type to bar clustered
set has legend to false -- unless you want it
tell series 1
set xvalues to range "B1:B3" of active sheet
set series values to range "A1:A3" of active sheet
set has data labels to true
set data label type of every data label to data labels show value
end tell
set caption of its chart title to "My Chart"
set xAxis to get axis axis type value axis
tell xAxis
set major unit to 30 / 1440
set number format of its tick labels to "h:mm"
end tell
end tell
end tell
--
Paul Berkowitz
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Applescript-users mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden