Delphi Automation of Excel - Menu Control - 400 points

Posted on 2004-11-02
Last Modified: 2010-04-05
I am trying to use Delphi (D6 or D7) to control Excel (2000 or XP).   I can use TExcelApplication, TExcelWorkbook etc to read & write data & formulae to/from cells AOK, but I'm having a problem controlling the menus.

What I would like to do for example is to disable the File:Print menu item.   When I try to set Commandbars.Item .........Enabled := False it tells me "Cannot assign to a read-only property.   This should be a simple thing to do - or am I missing something ?

Can someone provide me a simple code sample on how to do this please ?

Question by:geoffdb
    LVL 12

    Expert Comment

    I don't remember how it was exactly, but it was something like:

    Author Comment

    OK, I'm starting to make some progress .. but couldn't get the above code to work .....

    with the following code
        iCount := ExcelApplication.CommandBars.Count ;
           jCount := ExcelApplication.CommandBars[i].Controls.Count ;

              sTmp := ExcelApplication.CommandBars[i].Controls[j].Caption ;
              Memo1.Lines.Add( Format('%d %d %s', [i, j, sTmp ]  ));

    I'm looping through the I & j items, and printing these out to a memo, output as follows
    wher ethe first column is i, the second column j, the last sTmp :
    1 1 &File
    1 2 &Edit
    1 3 &View
    1 4 &Insert
    1 5 F&ormat
    1 6 &Tools
    1 7 &Data
    1 8 A&ction
    1 9 &Window
    1 10 &Help
    2 1 &File
    2 2 &Edit
    2 3 &View
    2 4 &Insert
    2 5 F&ormat
    2 6 &Tools
    2 7 &Chart
    2 8 A&ction
    2 9 &Window
    2 10 &Help
    3 1 &New
    3 2 Open
    3 3 &Save
    3 4 Permission (Unrestricted Access)
    3 5 &Mail Recipient

    Some questions :
    a) What do the 'Commandbar' and 'Control' arrays represent in real life ?
    b) Why the i=1 & i=2 identical items ?
    c) In the full list (I have iCount=129 or so, & up to 46 for some jcount) there appear to be multiple identical entries.   Why ?
    d) with the i=3 column, I can say
              if( sTmp = '&New' )
    but this doesn't disable the menu item.   Why ?

    I'm trying to understand how it thinks so as to modify it appropriately.

    LVL 12

    Accepted Solution

    CommandBar represent the menus. About "Controls" - i think it was for the subitems. For b) I don't have answer. But it seems it doen't catch the subitems using Controls.

    try to manipulate
    and see if it works.

    I used TExcelApplication just to execute some action. I am not very familiar with disable-ing the items.

    Author Comment


    I've tried this - without success.   "Visible" seems to be like "Enabled" -its a readonly property & have to use Set_Visible instead.

    I have been able to (theoretically) turn off menu items, e.g.

              sTmp := ExcelApplication.CommandBars[i].Controls[j].Caption ;
              Memo1.Lines.Add( Format('%d %d %s', [i, j, sTmp ]  )); // lists all the items

              iPos := Pos( 'Save', sTmp ) ;
              if( iPos > 0 )
              then begin
                 ExcelApplication.CommandBars[i].Controls[j].Set_Visible( False );
                 bMenu := ExcelApplication.CommandBars.Item[i].Controls[j].Enabled ;
                 if( bMenu )
                 then Memo1.Lines.Add( 'Item still enabled ....................' )
                 else Memo1.Lines.Add( 'Item disabled..........................' ) ;
              end ;

    In the above, it goes thru, finds the "Save" menu items, disables, verifies that it did disable - but then it is still active & visible on the menu.   What's going on here ?

    I can disable a whole submenu, e.g.
        ExcelApplication.CommandBars[1].Controls['Edit'].Set_Enabled(False) ; // Edit menu, works
    this disables the whole Edit menu

    but I seem unable to disable an item in that submenu.   I have done it with VB - but can't replicate with Delphi.

    Any help appreciated.
    Thanks, Geoff

    LVL 12

    Expert Comment

    I really have no idea why it is not working. It was supposed to work. Maybe something inside TExcelApplication...

    Author Comment

    Thanks for your help, not quite the solution I'd hoped for but will clean this up & award the points anyway.

    Thanks, Geoff

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Course: JavaScript Coding - Massive 12-Part Bundle

    Regardless of your programming skill level, you'll go from basics to advanced concepts in a vast array of JavaScript subjects including Sammy.js, Agility.js, Ember.js, Node.js, jQuery, AJAX, Extjs, AngularJS, Knockout.js, and JSON.

    In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
    Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
    Want to pick and choose which updates you receive? Feel free to check out this quick video on how to manage your email notifications.
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!

    884 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now