Am trying to automate the creation of a chart in Excel based on data in the same spreadsheet.
After beating my head against Excel’s infamously poorly documented commands and properties, and it’s rather so-so support of “watchableness” of it’s wizards, I did finally find there is some sort of Chart Wizard support. But the dictionary doesn’t give much clue how to use it.
I determined the properties in a bass-ackwards fashion…recording a macro to do it in Excel, which is essentially VB script near as I can tell. The macro shares property names with AppleScript commands so I back-figured this:
on makeChart()
tell application "Microsoft Excel"
ChartWizard "Data Capture" Source (("A2:A" & gEndRow & ",D1:D" & gEndRow) as text) Gallery xlLine Format 4 PlotBy xlColumns Title "Diamond Design Free Space over Time" CategoryAxisTitle "DATE" ValueAxisTitle "FREE SPACE" without HasLegend
end tell
end makeChart
It compiles okay, which leads me to believe it at least is formatted right. The stumbling block seems to be the “Data Capture” right after the ChartWizard command. “Data Capture” is the name of the worksheet in Excel, as this is how the Macro did it. However, running the AppleScript makes Excel report that it doesn’t understand “Data Capure” in that context. I tried the name of the file, no dice. Supposedly that data is supposed to be a “table name,” though I’m not sure what that means in Excel-speak. There are no other “names” I am aware of beyond file name and worksheet name.
Looked-up the ChartWizard command in the out-of-date Excel 2004 reference supplied at Mactopia, but it only shows “Not supported at this time.” (or something similar).
Anybody ever made this work? Or perhaps there is a way around it with another free/cheap utility that itself is scriptable with supplied data?
Model: Dual 1.8 GHz Dual PowerPC G5
AppleScript: 1.9.3
Browser: Firefox 1.5.0.1
Operating System: Mac OS X (10.3.9)
BTW, my name is Kelvin, so you’re my arch-nemesis.
Anyway, I’m using Office X so the object class names are different, but looking at the dictionary for the ‘ChartWizard’ command, I see that it requires a reference while “Data Capture” seems to be a string. You might want to get a reference to the chart. I just thought of this offhand, but if you need more help, I’ll create a chart and show you how to get a reference to it. I think you use a name or something to get a reference to a particular chart.
I might be mixing terminology, or misunderstanding the ChartWizard…I need to automate the creation of a chart, not modify an existing chart, so I’m not sure how I can have a reference to a chart I don’t have yet.
Have data, need chart, want AppleScript and Excel to play nice together to accomplish it.
Model: Dual 1.8 GHz Dual PowerPC G5
AppleScript: 1.9.3
Browser: Firefox 1.5.0.1
Operating System: Mac OS X (10.3.9)
My apologies, we appear to be on Office X, and Script Editor keeps getting hung-up on this, and won’t compile:
make new chart sheet at beginning of active workbook
It’s reporting:
Syntax Error
Expected end of line, etc. but found class name.
I tried to bypass with an explicit name, but then got the same error on the “active chart” reference in the ChartWizard line.
Tried this:
set myRange to ("A2:A" & gEndRow & ",D1:D" & gEndRow) as text
set mysource to Sheet "Data Capture"
make new Chart Sheet at "Charting"
ChartWizard (Sheet "Data Capture") Source mysource Gallery xlLine Format 4 PlotBy xlColumns Title "Diamond Design Free Space over Time" CategoryAxisTitle "DATE" ValueAxisTitle "FREE SPACE" without HasLegend
…but then got:
AppleScript Error
Microsoft Excel got an error: Can’t make a Chart Sheet.
Some more details:
–Microsoft Excel X (Release 1)
–Excel file name: “Diamond Server Free Space.xls”
–First sheet in file: “Data Capture”, automated data capture done here and working fine
–Second sheet in file: “Charting”, where I want the chart to be when created
–Since the spreadsheet data grows every day in a specific way, gEndRow is the row number result of a “find last row with data in it” detection
–Columns A and D in “Data Capture” are the known columns to pull data from
Thanks for the attempts so far, hopefully we can get this figured out…I can’t be the only one who needs this, can I?
Oh you folks are good. Can I ask how you figured that all out? I didn’t find the dictionary very helpful, but perhaps that was my failing, not the dictionary’s?
I need to fix a few things on my end (the graph came out odd looking, think I may have swapped the axis’ accidentally).
While I have your expert assistance. The chart this script makes needs to refreshed every time it’s made. My thought was to check for an existing chart and delete it. However, deleting a chart causes a user “are you sure” prompt. Is there some sort of “without prompt” qualifier for deleting sheets? Didn’t see one in the dictionary.
Again, a heaping helping of thanks…one huge step close to a full solution.
Jacques, you’re the greatest. The finished snippet:
--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 ("A1:A" & gEndRow & ":D1: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
--reset to data entry sheet
Select Sheet "Data Capture"
I’ve found most dictionaries “decent,” but the Excel ones have been notorious incomplete or cryptic, even when “recording” Excel to figure out how to do things. Problem is the “wizards” are not recordable to AppleScript, only the macro editor (which looks to me like VB).
I’m glad you had better luck with the trial-and-error part of it. I got most of it through experimentation with the returns from an Excel macro (as you saw), but just couldn’t figure out that last few inches. Much appreciated.
UPDATE:
Well after all that annoying trouble it seems the ChartWizard script command builds charts differently from it’s VB scripts differently from the actual GUI wizard, and I coulnd’t get the data to display properly.
But I think I found a workaround. If I create a chart that I like in advance, I can use the “ExtendSeries” command to add data to an existing chart. So far my manual runs of the script see to work correctly. Going to let it automate for a few days before calling it a success.
Here’s the relevant code:
Select Sheet "Data Capture"
set myRange to Range ("A" & gEndRow & ",D" & gEndRow) of ActiveSheet
Select Chart "Charting"
ExtendSeries of every Series of ActiveChart RowOrCol xlColumns Source myRange
