Create new Excel 2004 menu item in Applescript

[Jack Marr]

A couple of months ago I asked how to create a new command bar button,
which is documented incorrectly on page 30 of
Excel2004AppleScriptRef.pdf. Through other sources I found this is how
to execute that particular sample:

make new command bar control in command bar "Tools" with properties
{control type:control button, begin group:true, face id:0, on
action:"qtrReport", name:"Make Report"}

Now, how would I create a new menu item, i.e. a new command bar entry
to the right of File/Edit/.../Tools/.../Help? The documentation isn't
clear, or maybe it's just incorrect. Any advice would be appreciated.

 

[Paul Berkowitz]

A couple of months ago I asked how to create a new command bar button,
which is documented incorrectly on page 30 of
Excel2004AppleScriptRef.pdf. Through other sources I found this is how
to execute that particular sample:

make new command bar control in command bar "Tools" with properties
{control type:control button, begin group:true, face id:0, on
action:"qtrReport", name:"Make Report"}

Now, how would I create a new menu item, i.e. a new command bar entry
to the right of File/Edit/.../Tools/.../Help? The documentation isn't
clear, or maybe it's just incorrect. Any advice would be appreciated.

That's not a menu _item_, that's a whole menu. I don't think there's any way
you can do make a new menu in the main menu bar. The main menu bar belongs
to Apple. I don't see any way in the UI, either, You can make new Excel
toolbars, you van make new menus and menu items in Excel toolbars, you can
add new menu _items_ to existing menus that belong to Excel in the main menu
(from "File" rightwards), but you cannot make new menus in the main menu
bar. Not on a Mac, you can't. If I'm wrong, JE will know. but I really don't
think it's possible. If he knows how to do it in VBA, it should also be
possible in AppleScript.

--
Paul Berkowitz
MVP MacOffice
Entourage FAQ Page: <http://www.entourage.mvps.org/faq/index.html>
AppleScripts for Entourage: <http://macscripter.net/scriptbuilders/>

Please "Reply To Newsgroup" to reply to this message. Emails will be
ignored.

PLEASE always state which version of Microsoft Office you are using -
**2004**, X or 2001. It's often impossible to answer your questions
otherwise.

 

[JE McGimpsey]

If I'm wrong, JE will know. but I really don't think it's possible.
If he knows how to do it in VBA, it should also be possible in
AppleScript.

The main menu bar is accessed in MacOffice (Word and XL) via
Application.CommandBars(1) (in XL its name is "Worksheet Menu Bar", in
Word it's just "Menu Bar").

Here's a simple VBA sub to add a generic new menu with two items to the
main menu bar. It puts the new menu just to the left of the "Help" menu
item (if it exists). I imagine it would be fairly straightforward to
port it to AS:

Public Sub BuildNewMenu()
Const cnHelpMenuID As Long = 30010
Const csMyMenuTag As String = "MyMenu Tag"

Dim oApp As Application
Dim ctlNewMenu As CommandBarControl
Dim nPos As Long

Set oApp = Excel.Application
oApp.ScreenUpdating = False

With oApp.CommandBars(1)
'Check to see if the default worksheet menu bar
' is open. If it is, proceed. If not, bail out
If Not (.Enabled And .Visible) Then Exit Sub
On Error Resume Next
'If the menu exists, delete it, then rebuild it
.FindControl(Tag:=csMyMenuTag).Delete
nPos = .FindControl(Id:=cnHelpMenuID).Index
On Error GoTo 0
'If no Help Menu, new menu goes all the way right
If nPos = 0 Then nPos = .Controls.Count + 1
Set ctlNewMenu = .Controls.Add( _
Type:=msoControlPopup, _
Before:=nPos, _
Temporary:=True)
With ctlNewMenu
.Caption = "MyMenu"
.Tag = csMyMenuTag
.Visible = False
.Enabled = False
'Add a couple of menu items.
With .Controls.Add(Type:=msoControlButton)
.Caption = "MyMenu Item 1"
.OnAction = "MyMacro_1"
.Tag = "MyMenu Item 1 Tag"
.BeginGroup = False
.Visible = True
End With
With .Controls.Add(Type:=msoControlButton)
.Caption = "MyMenu Item 2"
.OnAction = "MyMacro_2"
.Tag = "MyMenu Item 2 Tag"
.BeginGroup = False
.Visible = True
End With
.Enabled = True
.Visible = True
End With
End With
oApp.ScreenUpdating = True
End Sub

 

[Jack Marr]

Great - thanks! (I also wouldn't call the VBA to AS port
straightforward.)

 

[Jack Marr]

Continuing this thread, now I'd like to manipulate the newly created
buttons (and builtin menu buttons for that matter), but am crashing
Excel. For instance, after running the steps above I can get the name
of the new menu, but not the new button:

tell application "Microsoft Excel"
get name of command bar 1
get name of command bar popup 11 of command bar 1
get name of command bar button 2 of command bar popup 11 of command
bar 1
end tell

In the Event Log:

get name of command bar 1
"Worksheet Menu Bar"
get name of command bar popup 11 of command bar 1
"My Menu"
get name of command bar button 2 of command bar popup 11 of command
bar 1
current application
"Microsoft Excel got an error: Connection is invalid."

Followed by the Microsoft Error Reporting dialog. This is also the case
when I want to access buttons of builtin menus. I'd like to be able to
change the button name, gray it out, change the action, etc, but can't
even ping it.

 

[Paul Berkowitz]

I confirm that. You get the crash also in Script Debugger when simply
accessing command bar popup 11 (MyMenu) with result display set to "Best"
-- i.e. with the "Explorer" active - it's OK with result display as 'source'
(like Script Editor's). The SD error message, shows that the bug is when it
tries to check for any elements of the menu. You get the same crash when
just asking for any command bar button or command bar control of the menu.
The problem isn't with the name, it's with the element.

There are all sorts of things about the Office apps, especially things like
these toolbars, which are very unorthodox and freaky. Many bugs came up also
in PowerPoint (lots more than in Excel). I'm not too surprised.

I'll report this bug in the Microsoft Office Suite. It's a bad one, that
will need to be fixed. Don't hold your breath, though. I wouldn't be
surprised if it's not fixed before the next main release. You never know,
though...

As I said last time (and I'll put in a feature request too), you can't
really do much anyway with custom menu items via AppleScript since their
actions can only be set via an 'on action' property to a VBA Macro. Once
you're going to have to write macros in VBA, you might as well do the whole
thing in VBA, as JE first showed. I will put in a feature request for being
able to set 'on action' properties of controls to script objects: that would
be cool.

--
Paul Berkowitz
MVP MacOffice
Entourage FAQ Page: <http://www.entourage.mvps.org/faq/index.html>
AppleScripts for Entourage: <http://macscripter.net/scriptbuilders/>

Please "Reply To Newsgroup" to reply to this message. Emails will be
ignored.

PLEASE always state which version of Microsoft Office you are using -
**2004**, X or 2001. It's often impossible to answer your questions
otherwise.

 

[Jack Marr]

I'll report this bug in the Microsoft Office Suite. It's a bad one, that
will need to be fixed. Don't hold your breath, though. I wouldn't be
surprised if it's not fixed before the next main release. You never know,
though...

Thanks much. If you email me the bug number I'll see if I can lobby
for a fix from my end as well.

 

[Paul Berkowitz]

Thanks much. If you email me the bug number I'll see if I can lobby
for a fix from my end as well.

Bug numbers are not divulged to anyone, including me. There is no public
access. Just report the bug via Help/Send Feedback, and it will get there.

--
Paul Berkowitz
MVP MacOffice
Entourage FAQ Page: <http://www.entourage.mvps.org/faq/index.html>
AppleScripts for Entourage: <http://macscripter.net/scriptbuilders/>

Please "Reply To Newsgroup" to reply to this message. Emails will be
ignored.

PLEASE always state which version of Microsoft Office you are using -
**2004**, X or 2001. It's often impossible to answer your questions
otherwise.