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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Ivanov_GCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.