I have a procedure that creates an array, and I created a template in Excel that is formatted for that array. The procedure creates the array, then opens the template. I can manually paste into the spreadsheet. But,
Is there a way to automate that step? Just one more little step will make this an almost perfect procedure.
You could create an VBA macro in Excel, that automatically runs when you activate a certain sheet — but I doubt that there are any automations faster than a cmd-V.
I’m not familiar with the workings of AppleScript - but I think it can be used to transfer information between applications. Each app., if it supports AppleScript, has its own collection of commands it understands.
Applescript can do this. As your template is already open, this very basic script will put your array into any cell you want. Paste the script into your procedure, change the cell reference and the ArrayName to the name of a windowglobal variable containing your array. See the AppleScript statement in Panorama Help. If you’re using Monterey you’ll probably get a dialog asking for consent for Panorama to control Excel which you must OK.
Applescript |||
try
tell application "Microsoft Excel"
activate
activate sheet 1 of workbook 1
set value of cell "A1" to $«ArrayName»$
end tell
end try |||
Put the array to the clipboard in your Panorama procedure.
Replace the script line set value of cell "A1" to $«ArrayName»$
with paste special on worksheet sheet 1
This will paste the array from the clipboard into sheet 1 spreading over columns and rows starting at cell A1.
Excel has excellent AppleScript support so you can do almost anything you want. AppleScript however is a pig.
Very close to perfect. This version of the AppleScript opens the spreadsheet and shows a highlighted area where the clipboard contents will fit, but it doesn’t paste it in. Using a command-V at that point pastes the clipboard into the spreadsheet.
I do not see Excel showing up with a dictionary in my script editor, and I know Microsoft has announced it is release AppleScript support for the “New Outlook” in a couple of months. So I wonder if some of the statements are not currently working in Excel.
Or is there something else to try.
Thank to everyone for the suggestions. Unfortunately, I have not gotten this to work with any approach. But it’'s not a big deal and I have moved on. The users will just have to press command-V.