I tried for a month to get Excel to create charts from scratch and best I was able to do was create a base chart by hand and get Excel to update it as new data is added. For example, the ChartWizard feature, despite what the dictionary says, is not supported via AppleScript (I even have a partial routine from Jacques that was tried). Like you, I even tried staring at the VBS version for clues, but in this one instance, I came-up empty handed.
I can’t recall, but I think Jacques managed to get a chart generated, but no control over the formatting or appearance, which kinda sank it for me (I needed more finite charting control).
Here’s an append methodology though. It updates a chart on one Worksheet in a file with data from another worksheet in the same file. The primary worksheet data is collected and entered via script as well, but this is just the “append” part (taken out of a larger handler):
--update existing chart
--(requires pre-existing chart, but easier to implement)
--
Select Sheet "Data Capture"
set myRange to Range ("A" & (gEndRow + 1) & ",D" & (gEndRow + 1)) of ActiveSheet
Select Chart "Charting"
ExtendSeries of every Series of ActiveChart RowOrCol xlColumns Source myRange
If anyone wants the full script, let me know–it’s a script that reads the free space of our file server and records it in a spreadsheet, then updates a graph so we can track free space useage over time (script runs every night via cron).
Jacques gave me this, it’s commented-out in my code in case I needed it, no idea if it works:
--
-- Charting and ChartWizard syntax provided with the help of
--"Jacques" of MacScripter.net, 4/17/06
--
--make chart
try
Select Chart "Charting"
on error
set newChart to Create New Chart After ActiveSheet of ActiveWorkbook
end try
Select Sheet "Data Capture"
set myRange to Range ("A2:A" & gEndRow & ",D2:D" & gEndRow) of ActiveSheet
Select Chart "Charting"
ChartWizard ActiveChart Source myRange Gallery xlLine Format 4 PlotBy xlColumns Title "Diamond Design Free Space Over Time" CategoryAxisTitle "DATE" ValueAxisTitle "FREE SPACE" without HasLegend
--rename chart sheet
try
Select Sheet "Chart1"
set Name of ActiveChart to "Charting"
end try
This is for Microsoft Excel 10.1.5 (Office X).