Re: Scripting Excel Charts
Re: Scripting Excel Charts
- Subject: Re: Scripting Excel Charts
- From: Paul Berkowitz <email@hidden>
- Date: Fri, 04 Aug 2006 17:21:36 -0700
- Thread-topic: Scripting Excel Charts
Title: Re: Scripting Excel Charts
On 8/4/06 8:58 AM, "Steven Valenti" <email@hidden> wrote:
> Can someone please get me started with an example on how to script an
> Excel chart?
> Data = "" column would be times (02:07:25, 01:24:55, 02:42:18) column
> "B" would have (Job A, Job B, Job C)
> I want to make a simple bar chart with jobs listed on the left and
> times on the bottom.
Here's an example (for Excel 2004). You'll find that some defaults that occur in the UI aren't the same in AppleScript: The size of the chart object has to be set or it's teeny. I used values from an example I made in the UI. If you don't know how many rows your data will have and thus the height necessary you'll have to work it out. By default, the gridline "Major Units" are a peculiar number, since it uses 0.02, which as a proportion of 1 day (1440 minutes) works out to 00:28:40, a rather stupid number of minutes. 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.
You need that 'set source data' line or nothing else works. You can't set the caption (title) until you've created the series or at least set the source data. You have to set 'has data labels' to true before you can set the data label type.
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
set major unit of xAxis to 0.020833333333
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