Solved

C# code to Hide toolbars in an excel document.

Posted on 2010-11-16
14
2,152 Views
Last Modified: 2012-05-10
Hello,

I have a C# Winforms program that opens an excel document with the code below.
It works great but what I can not figure out how to do, is to turn off ALL menu's and toolbars.

The excel version I am using right now is 2003... But I will be upgrading to 2010 in the near future.
Any ideas?
//top of source...
using Excel = Microsoft.Office.Interop.Excel;

// Code inside a function...

// Get report and display it on the screen.
Excel.Application xlApp;
Excel.Workbook xlWorkBook;
Excel.Worksheet xlWorkSheet;
object misValue = System.Reflection.Missing.Value;
xlApp = new Excel.ApplicationClass();

xlWorkBook = xlApp.Workbooks.Open(strFileName, 0, true, 5, "", "", true,Excel.XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0);
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
xlApp.Visible = true;
xlApp.DisplayFullScreen = true;

 // Display the Document and then Sleep.
System.Threading.Thread.Sleep(timeToShowMilliseconds);

// Close the Excel report 
 xlWorkBook.Close(false, misValue, misValue);
xlApp.Quit();

releaseObject(xlWorkSheet);
releaseObject(xlWorkBook);
releaseObject(xlApp);

Open in new window

0
Comment
Question by:ScottParker
[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
  • 9
  • 4
14 Comments
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34152740
xlApp.Windows(1).DisplayHeadings = false
Will kill the row and column headings.
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34152743
Application.CommandBars("Worksheet Menu Bar").Enabled = False
Will kill the menu bar. Of course, setting either to true will put it back on.
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34152746
Ach, xlApp.CommandBars of course.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34152766
xlsApp.Windows(1).DisplayGridlines = False for the gridlines if you want them gone.

Did I get them all?
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34154443
You could use xlApp.DisplayFullScreen = True

For 2010, manipulating commandbars won't work - you'll need to either alter the XML of the workbook to remove the ribbon, or use:

xlApp.ExecuteExcel4Macro "Show.ToolBar(""Ribbon"",False)"
0
 
LVL 3

Author Comment

by:ScottParker
ID: 34154480
Sometimes I have toolbars that I dont know the name of show up..
Like there is a chart toolbar, and sometimes a refresh data toolbar, and sometimes a random toolbar that some other software has installed.

Correct me if I am wrong, but wouldn't I need to know the names of all of them if I used the
xlApp.CommandBars("Worksheet Menu Bar").Enabled = False; code?

0
 
LVL 3

Author Comment

by:ScottParker
ID: 34154493
When I do use the
xlApp.CommandBars("Worksheet Menu Bar").Enabled = False;
code,
I get an error...
Non-invocable member 'Microsoft.Office.Interop.Excel._Application.CommandBars' cannot be used like a method.

and if I change it to...
xlApp.CommandBars["Worksheet Menu Bar"].Enabled = False;
Cannot apply indexing with [] to expression of type 'Microsoft.Office.Core.CommandBars'
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34157212
You could try
For Each cmdBar in xlApp.CommandBars
  cmdBar.Enabled = false
Next
0
 
LVL 3

Author Comment

by:ScottParker
ID: 34157962
no. that does not work.
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34158060
Oh yeah. Sorry about the (). More people use VB here so I forgot we were in C#.
You can use the index.
Application.CommandBars[1] is the menu bar
(or perhaps it's CommandBars[0] I don't have my C# compiler to test it).
I attached a list of the numbers and names of all of them that I have. (You might have to subtract 1 since I used VBA and it's 1 indexed)

Does application.commandbars.count work? I expect it would. You could do a for loop through all 117 or so of them and just turn them all off.
0
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 34158067
Upload failed. Here it is again.
CommandBars.xls
0
 
LVL 37

Accepted Solution

by:
TommySzalapski earned 500 total points
ID: 34158099
Something like
for(int i = 0; i < Application.CommandBars.Count; ++i)
  Application.CommandBars[i].Enabled = false

Open in new window

0
 
LVL 3

Author Comment

by:ScottParker
ID: 34187467
Tommy,

I tried that and I get error message..
Cannot apply indexing with [] to an expression type 'Microsoft.Office.Core.CommandBars'
0
 
LVL 37

Assisted Solution

by:TommySzalapski
TommySzalapski earned 500 total points
ID: 34190123
Hmm, now that I can test it I have no trouble at all running those lines.

Maybe you need to add a using directive. The Command Bars type is in Office.Core not Office.Interop.Excel
I used
using Microsoft.Office.Interop.Excel;
using Microsoft.Office.Core;

Open in new window

But Core seems to be missing from yours. Try adding it and I bet it'll work. Again, mine turns all the command bars off.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

617 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