Scripting the Excel Visual Basic Editor

AppleScript
#1

Hello, all –

I’m trying to GUI script the Excel VBE. Using UI Browser, the Applescript seems to be

Tell process "Microsoft Excel"
    tell menu bar  -- (menu bar 1)
        tell menu bar item "Edit" -- (menu bar item 4)
            tell menu -- (menu 1)
        	    click menu item "Select All" -- (menu item 8)
            end tell
        end tell
     end tell
 end tell

However, Script Debugger doesn’t like the line ‘tell menu bar item “Edit”’ with the error “Expected end of line but found class name.” I don’t know how to fix that (or understand it even.) I did try to do it by number (4).

Much thanks,
…Mick

#2

Hi!

It’s not “Microsoft Excel” you need to tell that to but “System Events”.

You do need to activate Excel, though. The have System Events handle the menu hierarchy.

#3

Hi, Martin –

My apologies for the confusion. The above was only the tell-chain reported by UI Browser The complete code is:

tell application "Microsoft Excel" to activate
tell application "System Events"
Tell process "Microsoft Excel"
    tell menu bar  -- (menu bar 1)
        tell menu bar item "Edit" -- (menu bar item 4)
            tell menu -- (menu 1)
        	    click menu item "Select All" -- (menu item 8)
            end tell
        end tell
     end tell
 end tell	
end tell

The error from Script Debugger is "Expected end of line but found class name.” reported on the 'tell menu bar item “Edit”” line. Visually, “Edit” is the 4th menu over. I just can’t see the error.

I find UI Browser very counter-intuitive to use, probably because I only use it a few times a year. But the above seems to make sense.

Thanks,

Mick

#4

Hi All –

In reviewing yesterday’s work, I see that menu bar 1 is Excel’s spreadsheet menu. The VBE’s menu is ‘top level function row (UI element 40)’ So the new code is:

tell application "Microsoft Excel" to activate
tell application "System Events"
	Tell process "Microsoft Excel"
        tell top level function row -- (UI element 40)
            tell menu bar item "Edit" -- (menu bar item 4)
                tell menu -- (menu 1)
        	        click menu item "Select All" -- (menu item 8)
                end tell
            end tell
         end tell
     end tell	
    end tell

The error is now on that line "tell top level function row – (UI element 40)” as “Expected end of line but found identifier.”

Confused as ever.

…Mick ©¿©¬

#5

Hi All –

Drill is over, secure from the drill.

This code complies, and I’m now well on my way.

tell application "Microsoft Excel" to activate
tell application "System Events"
    tell process "Microsoft Excel"
	    tell UI element 40
		    tell menu bar item "Edit" -- (menu bar item 4)
			    tell menu 1
				    tell menu item "Select All" -- (menu item 8) 
					    click
				    end tell
				    tell menu item "Copy"
					    click
				    end tell
			    end tell
		    end tell
	    end tell
    end tell
end tell

Thanks Martin. Some time you have to use the names, and some times the numbers.

…Mick

1 Like
#6

I think your problem in your original script was in omitting a valid index key in lines 2 and 4. You actually had the correct reference in the comment made against both of these lines.

So, line 2 ought to be:

tell menu bar 1

and line 4 ought to be:

tell menu 1
#7

Hi CK –

I think either the index or the name works. Leaving both out is just wrong. :sunglasses:

Sometimes you have to test one or the other. It seems very fickle.

Thanks, Mick