Delphi Automation of Excel - Menu Control - 400 points

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 ?

Geoff
geoffdbAsked:
Who is Participating?
 
Ivanov_GConnect With a Mentor Commented:
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
Workbook.Application.CommandBars[i].Visible
and see if it works.

I used TExcelApplication just to execute some action. I am not very familiar with disable-ing the items.
0
 
Ivanov_GCommented:
I don't remember how it was exactly, but it was something like:
ExcelApplication1.CommandBars[1].Controls['Print'].Set_Enabled(False);
0
 
geoffdbAuthor Commented:
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' )
          then
             ExcelApplication.CommandBars[i].Controls[j].Set_Enabled(False);
but this doesn't disable the menu item.   Why ?

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

Thanks
Geoff
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
geoffdbAuthor Commented:
Ivanov

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_Enabled(False);
             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


0
 
Ivanov_GCommented:
I really have no idea why it is not working. It was supposed to work. Maybe something inside TExcelApplication...
0
 
geoffdbAuthor Commented:
Thanks for your help, not quite the solution I'd hoped for but will clean this up & award the points anyway.

Thanks, Geoff
0
All Courses

From novice to tech pro — start learning today.