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

x
?
Solved

Delphi Automation of Excel - Menu Control - 400 points

Posted on 2004-11-02
6
Medium Priority
?
367 Views
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 ?

Geoff
0
Comment
Question by:geoffdb
[X]
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
6 Comments
 
LVL 12

Expert Comment

by:Ivanov_G
ID: 12471862
I don't remember how it was exactly, but it was something like:
ExcelApplication1.CommandBars[1].Controls['Print'].Set_Enabled(False);
0
 

Author Comment

by:geoffdb
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' )
          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
 
LVL 12

Accepted Solution

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

by:geoffdb
ID: 12491368
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
 
LVL 12

Expert Comment

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

Author Comment

by:geoffdb
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
0

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