Making new objects and adding them to MS app collections?

AppleScript
#1

I have been finding that Microsoft applications seem a little more resistant than most to the creation of new objects (and their addition to existing collections) through AppleScript and JXA.

Has anyone had more success than I have in using AppleScript to define and add, for example:

  1. PowerPoint slides
  2. Excel named items (used for named ranges, etc) ?

I’m finding that the usual tell .... to make new xyz with properties {...:...} is coming back with messages like parameter error 50.

With some applications I’ve found that the trick has been to track down some minimum set of properties or location specifiers that is required to make and embed a new object successfully.

In these two examples, however, I’m certainly failing some kind of IQ test, and before I soldier on, I just wondered whether others had discovered any particular quirks in this aspect of current MS osascript interfaces ?

(Could always pivot back to VBA, but it seems a pity …)

#2

This may give a sense of the kind of thing that I would like to be able to do in the Excel case, for example.

tell application "Microsoft Excel"
    tell active workbook
        set oItem to make new named item with properties {name:"FN", value:"=LAMBDA(x, x)", macro type:macro type command}
    end tell
end tell
#3

I’m not familiar with Excel scripting, but you generally have to provide a location (e.g. at beginning) for the newly created object relative to the tell context.

#4

Yes, it’s a good point, and I have indeed tried all the permutations that come to mind of:

make new named item at [end|etc] of named items with properties {...}

but the ‘parameter’ error doesn’t, alas, seem to vary with any of them.

#5

Maybe 'named item` cannot be placed within a workbook? Where does SD’s explorer show named items within the Excel application?

#6

Workbooks do appear to be the parent objects for named item collections:

Screenshot 2021-04-28 at 20.00.26

and this kind of thing yields the expected harvest:

tell application "Microsoft Excel"
    tell active workbook
        name of named items
    end tell
end tell
#7

Okay, since active workbook is a property, Excel may not be willing to resolve that correctly. Give this a try:

tell application "Microsoft Excel"
   tell (get active workbook)
       make new ...
   end tell
end tell
#8

Good thinking. All permutations of this (+/- properties, location specifier) still seem to give the same “Parameter error” message:

tell application "Microsoft Excel"
    tell (get active workbook)
        make new named item at end of named items with properties ¬
            {name:"IDFN", value:"=LAMBDA(x, x)", macro type:macro type command}
    end tell
end tell
#9

Not sure if those snippets can help…

 tell application id "PPT3"
	-- to create a slide 
	set theSlide to make new slide at end of active presentation
	
	-- to create a rectangle on the current slide
	set theSlide to (slide of view of active window)
	if theSlide = missing value then set theSlide to (slide 1 of slide range of selection of active window)
	set theShape to make new shape at end of theSlide with properties {top:30, left position:30, height:30, width:30}
end tell

tell application id "XCEL"
	tell worksheet 1
		set name of (range "A1:C3") to "whatEver"
		return range "whatEver"
	end tell
end tell
#10

Thanks – those do indeed work. Naming an existing range is no problem.

(I must reinstall PowerPoint to retest the with properties selection of slide templates for new slides – I have given up for the moment and resorted to generating Latex Beamer source)

Have you found any way to add to the named items collection in an Excel worksheet ?

#11

Something like this?

tell application id "XCEL"
	tell active sheet
		make new named item at end of it with properties {name:"Print_Area", visible:true, reference local:"=A1:D12"}
	end tell
end tell
#12

Excellent – you’ve cracked it. The trick is to supply the LAMBDA definition to one of the reference properties rather than to the value property (which is flagged as writable, but fails in practice).

Writing, for example:

-- LAMBDA expressions require macOS Excel Beta at time of writing

property lambdas : {¬
    {"CHARS", "=LAMBDA(string, MID(string, ROW(INDIRECT(\"1:\" & LEN(string))), 1))"}, ¬
    {"INTERCALATE", "=LAMBDA(delim, LAMBDA(xs, TEXTJOIN(delim, TRUE, xs)))"}, ¬
    {"TAKE", "=LAMBDA(n, LAMBDA(xs, INDEX(FILTER(xs, xs<>\"\"), SEQUENCE(n, 1, 1, 1))))"}}

tell application id "XCEL"
    tell active workbook
        repeat with kv in lambdas
            make new named item with properties ¬
                {name:item 1 of kv, references:item 2 of kv}
        end repeat
    end tell
end tell

successfully yields:

Screenshot 2021-04-28 at 22.33.36
Screenshot 2021-04-28 at 22.33.361452×548 95.6 KB

Thanks !!

I’m very grateful for your help.

The context FWIW, is the current Excel Beta, which supports a LAMBDA and LET pattern for defining reusable custom functions, see, for example:

#13

I’m glad you were able to find a solution. If we can say one thing: the Excel error is spectacularly unhelpful :slight_smile:

2 Likes
#14

Many osascript APIs take the trouble to equip constructors with default properties, letting us add vanilla objects to a collection (and then refine their properties if we didn’t specify them earlier).

These slightly magic (undocumented) subsets of properties without which object creation simply fails (more or less mutely) seem to be a bit of a hall-mark of the MS offerings to the osascript interface.

Probably nothing to be done about it in practice, I guess.

(Possibly a constraint of the kind of bridging that they are doing ?)