Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


Delphi Automation of Excel - Menu Control - 400 points

Posted on 2004-11-02
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
LVL 12

Expert Comment

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

Author Comment

ID: 12481744
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

Ivanov_G earned 800 total points
ID: 12482572
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.
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.


Author Comment

ID: 12491368

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

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

Author Comment

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

Thanks, Geoff

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The uses clause is one of those things that just tends to grow and grow. Most of the time this is in the main form, as it's from this form that all others are called. If you have a big application (including many forms), the uses clause in the in…
Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Suggested Courses

636 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