Solved

C# code to Hide toolbars in an excel document.

Posted on 2010-11-16
14
2,100 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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

710 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