Solved

Get the Tab color in Excel 2007 in C#

Posted on 2012-04-12
7
1,227 Views
Last Modified: 2012-06-27
I found out how to set a tab's color using c# in Excel 2007:

LastSheet.Tab.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.ForestGreen);

This does indeed set the tab color to ForestGreen (although as a side note, I would like to be able to set it based on RGB or ARGB).

However, now what I want to do is retrieve the current(initial) color of the tab into a variable. I tried this:

System.Drawing.Color currentColor;
currentColor = (System.Drawing.Color)LastSheet.Tab.Color;  

Although the compiler was happy with this, at runtime I got an error:
"Specified cast is not valid."

I suspect I need to use the ColorTranslator method somehow, but I tried some different configurations and none worked.

Any ideas?
0
Comment
Question by:bassman592
  • 4
  • 3
7 Comments
 
LVL 42

Expert Comment

by:dlmille
ID: 37840816
I don't think you need to cast it.  LastSheet.Tab.Color is long, so just make currentColor long and make a direct assignment to currentColor:

'After having dimensions currentColor to Long... The following should work:
currentColor = LastSheet.Tab.Color;

Open in new window


Its been 25 years since I programmed in C, so rather than building a C# example, here's a working VBA.NET module that works with no problems:

Module Module1
    Sub doExcelStuff()
        Dim objXL As Object

        Dim objBooks As Object
        Dim objBook As Object
        Dim objSheets As Object
        Dim objSheet As Object
        Dim objrange As Object
        Dim myTabColor As Long

        objXL = CreateObject("Excel.Application")
        objXL.Visible = True
        ' Get a new workbook.
        With objXL

            .Workbooks.Add()
            objrange = .Range("A1")
            objSheet = .sheets("Sheet1")
            myTabColor = objSheet.tab.color
            objSheet.tab.color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.ForestGreen)
            MsgBox("Tab color was changed from: " & myTabColor & " to " & objSheet.tab.color)
            objSheet.tab.color = myTabColor
        End With

    End Sub
End Module

Open in new window

0
 

Author Comment

by:bassman592
ID: 37842672
Unfortunately, in C# that doesn't work. If I do this:

long currentColor;
currentColor = LastSheet.Tab.Color;

The compiler complains with: "Cannot implicitly convert type 'object' to 'long'. An explicit conversion exists (are you missing a cast?)"

Then, if I use an explicit cast:

long currentColor;
currentColor = (long)LastSheet.Tab.Color;

The compiler is happy, but I get the runtime error: "Specified cast is not valid."

I think this is one of the things that makes automating office in C# a little more difficult than doing it in VB...
0
 

Author Comment

by:bassman592
ID: 37842866
FYI...

I also tried this (because I saw it online somewhere as a possible solution):

System.Drawing.Color currentColor;
currentColor = System.Drawing.ColorTranslator.FromOle((int)LastSheet.Tab.Color);

but this also gives me the runtime error:  "Specified cast is not valid."

I had high hopes for this because in order to set the tab color I use the ColorTranslator.ToOle method. It had a nice symmetry.
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 42

Expert Comment

by:dlmille
ID: 37843616
Shucks!  I'm at work, and my VSTO for C# book is at home.  Promise I'll look tonite if someone else hasn't. All I did was create a windows form that on a button click called that module I posted.  Any words of advice?  For the sake of my sanity, can you give me a close equivalent to the VB module I posted in C#?  Its easy to spend way to long on something stupid due to lack of experience in another language but I'm good at debugging if I have everything else working.

Cheers,

Dave
0
 
LVL 42

Accepted Solution

by:
dlmille earned 500 total points
ID: 37845712
Ok - well, its curious.  Based on the way you set up the casting, you actually have to have an exception handler via try/catch or other mechanism to ensure that the exception regarding assignment to currentColor gets handled.

To figure that out, I went down this rabbit trail:
http://msdn.microsoft.com/en-us/library/system.invalidcastexception(v=vs.90).aspx and then down to Handling and Throwing exceptions.

However, I tried something much simpler.  As you did, I didn't declare currentColor as System.Drawing.Color.  I declared it dynamically with the assignment to lastSheet.Tab.Color and the system allowed that implicit conversion.

Here's my code, which runs as a result of a Windows Forms button click:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;


namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            var objExcel = new Excel.Application();
            var objbook = objExcel.Workbooks.Add(Type.Missing);
            var lastSheet = objExcel.Worksheets[objExcel.Worksheets.Count] as Excel.Worksheet;
            var i = 0;
         

            //System.Drawing.Color currentColor;
            
            objExcel.Visible = true;

            lastSheet.Tab.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.ForestGreen);
            var currentColor = lastSheet.Tab.Color;

            //try
            //{
                //currentColor = (System.Drawing.Color)lastSheet.Tab.Color;
            //}
            //finally
            //{
                // To run the program in Visual Studio, type CTRL+F5. Then 
                // click Cancel in the error dialog.
              //  Console.WriteLine("\nExecution of the finally block after an unhandled\n" +
              //      "error depends on how the exception unwind operation is triggered.");
              //  Console.WriteLine("i = {0}", i);
            //}

        }
    }
}

Open in new window


Now, no compile or runtime error.

Thank goodness for a common object model and VS or I would have been lost.  Starting up C programming after 25 years is NOT like riding a bike!  And I wrote thousands of lines, re: early Windows programming.

Cheers,

Dave
0
 
LVL 42

Expert Comment

by:dlmille
ID: 37845803
And, for your color conversions, here's a bit of play with that, using HEX RGB and the fromArgb functionality.

Enjoy!

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;


namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            var objExcel = new Excel.Application();
            var objbook = objExcel.Workbooks.Add(Type.Missing);
            var lastSheet = objExcel.Worksheets[objExcel.Worksheets.Count] as Excel.Worksheet;
         

            System.Drawing.Color customColor;
          
            objExcel.Visible = true;

            var currentColor = lastSheet.Tab.Color;
            MessageBox.Show("The Last Sheet's Tab Color is: " + lastSheet.Tab.Color);

            lastSheet.Tab.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.ForestGreen);
            MessageBox.Show("The Last Sheet's Tab Color is now: " + lastSheet.Tab.Color);

            lastSheet.Tab.Color = 0xFF0000; //RGB Blue Hex Representation
            MessageBox.Show("The Last Sheet's Tab Color is now: " + lastSheet.Tab.Color + " Which should be Blue!");

            lastSheet.Tab.Color = 0x00FF00; //RGB Green Hex Representation
            MessageBox.Show("The Last Sheet's Tab Color is now: " + lastSheet.Tab.Color + " Which should be Green!");

            lastSheet.Tab.Color = 0x0000FF; //RGB Red Hex Representation
            MessageBox.Show("The Last Sheet's Tab Color is now: " + lastSheet.Tab.Color + " Which should be Red!");

            //The below was my first attempt, after doing some googling
            //customColor = System.Drawing.Color.FromArgb(255, 255, 0); //RGB Yellow using FromArgb
            //int myClr = customColor.ToArgb();
            //lastSheet.Tab.Color = customColor.ToArgb();

            //I translated then to this...
            lastSheet.Tab.Color = System.Drawing.Color.FromArgb(255, 255, 0).ToArgb(); //RGB Yellow using FromArgb
            MessageBox.Show("The Last Sheet's Tab Color is now: " + lastSheet.Tab.Color + " Which should be Cyan!");
        }
    }
}

Open in new window


Fun - thanks to you, I've now written my first C# code, lol.  Can't wait to get back to VB.NET! lol - just joking.

Dave
0
 

Author Closing Comment

by:bassman592
ID: 37851609
Brilliant! Thanks a lot. I'm just starting C# (no prior c background, though) and I've seen the "var" keyword before but I've never used it. It's weird how the default tab color appears to be "False" - I wasn't expecting that! Anyway, thanks again, and I hope the foray into c# wasn't too inconvenient. I do wish I could use VB on this project, but unfortunately I can't.
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

828 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