C# .NET - VSTO - Managing Excel 2003 and Excel 2007 from C# 2008 - Creating New CommandBar

Asked By Aldo Liaks

11-Feb-08 06:22 AM

Hi there,

I still have two questions...

1.- There is a part of the code below that should generate a new CommandBar called "VBAMacroCommandBar", but when checking in Office 2007 I don't see the command bar.

2.- The code below runs okay with Office 2007, but when trying it in a machine with Office 2003 does not work.

All the references in the project are for Office 12. What should I do in order to make the code running under both versions, 2003 and 2007?

Thanks in advance,

Aldo.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using Office = Microsoft.Office.Core;

using Excel = Microsoft.Office.Interop.Excel;

using VBIDE = Microsoft.Vbe.Interop;

namespace Excel2008021000

{

class Program

{

static void Main(string[] args)

{

Excel.Application oExcel = null; //Excel Application

Excel.Workbook oBook = null; // Excel Workbook

Excel.Sheets oSheetsColl = null; // Excel Worksheets collection

Excel.Worksheet oSheet = null; // Excel Worksheet

Excel.Range oRange = null; // Cell or Range in worksheet

VBIDE.VBComponent oModule = null; // VBA Module

Office.CommandBar oCommandBar = null;

Office.CommandBarButton oCommandBarButton = null;

String sCode;

Object oMissing = System.Reflection.Missing.Value;

oExcel = new Excel.Application(); // Create an instance of Excel.

oExcel.Visible = true; // Make Excel visible to the user.

oExcel.UserControl = true; // Set the UserControl property so Excel won't shut down.

System.Globalization.CultureInfo ci = new System.Globalization.CultureInfo("en-US");

oBook = oExcel.Workbooks.Add(oMissing); // Add a workbook.

oSheetsColl = oExcel.Worksheets; // Get worksheets collection

oSheet = (Excel.Worksheet)oSheetsColl.get_Item("Sheet1"); // Get Worksheet "Sheet1"

oRange = (Excel.Range)oSheet.get_Range("E5", "E5"); // Get Range (or cell) "E5"

oRange.Value2 = "Hi There!"; // Do something to oRange

// Create a new VBA code module.

// MUST GET Excel PERMISSION: see more at http://support.microsoft.com/kb/282830/

oModule = oBook.VBProject.VBComponents.Add(VBIDE.vbext_ComponentType.vbext_ct_StdModule);

sCode =

"Sub VBAMacro()\r\n" +

" MsgBox \"VBA Macro called\"\r\n" +

"End Sub";

oModule.CodeModule.AddFromString(sCode); // Add the VBA macro to the new code module.

// Run VBA macro "VBAMacro"

oBook.Application.Run("VBAMacro", oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing,

oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing,

oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);

//wb.SaveAs(FileName, Excel.XlFileFormat.xlWorkbookNormal,

// null, null, false, false, Excel.XlSaveAsAccessMode.xlShared,

// false, false, null, null, null);

try

{

// Create a new toolbar and show it to the user.

oCommandBar = (Office.CommandBar)oExcel.CommandBars.Add("VBAMacroCommandBar", oMissing, oMissing, true);

oCommandBar.Visible = true;

// Create a new button on the toolbar.

oCommandBarButton = (Office.CommandBarButton)oCommandBar.Controls.Add(Office.MsoControlType.msoControlButton, oMissing, oMissing, oMissing, oMissing);

oCommandBarButton.OnAction = "VBAMacro"; // Assign a macro to the button.

oCommandBarButton.Caption = "Call VBAMacro"; // Set the caption of the button.

oCommandBarButton.FaceId = 2151; // Set the icon on the button to a picture.

}

catch (Exception eCBError) { }

// Release the variables.

oCommandBarButton = null;

oCommandBar = null;

oModule = null;

oBook.Close(false,oMissing,oMissing);

oBook = null;

oExcel.Quit();

oExcel = null;

// Collect garbage.

GC.Collect();

}

Something strange...

I deleted the references to Office 12 and added those to Office 11.

Then I saw that

1.- The code runs okay on a machine with Office 2003 (I can even get the new floating CommandBar)

2.- The code also runs on a machine with Office 2007 (without creating or showing the new Command Bar)

Doesn't it strange that references to Office 11 run on both systems and references to Office 12 run only under Office 2007?

Reason why this don't work in 2003

11-Feb-08 07:17 AM

That code won't work with 2003 version of office as you are using reference to 2007 Office version. Support of these two version is not the same as you are using version 12(2007) of Office interop not for 11 (2003).

I'm afraid you have to split your code in two path to be able to work with both versions and if you use installer check which version does client use and apply correct dll for corect Office version.

I don't have 2007 experience to be able to tell you why you don't see your toolbar.

C# 2008 - Installer Check - Excel version

11-Feb-08 07:49 AM

Do you know how to use "installer check" to retrieve application's version?

Thanks.

To check what kind of version of Office is intalled you have to check registry

11-Feb-08 09:17 AM

Office use registry key to set installation version in it. I would rather use some other option but unfortunately it's not know to my knowledge.
But here is the code I used:

public enum eOfficeVersion
    {
        eOfficeVersion_Unknown, // error return value
        eOfficeVersion_95,
        eOfficeVersion_97,
        eOfficeVersion_2000,
        eOfficeVersion_XP,   // XP = 2002 + marketing
        eOfficeVersion_2003,
        eOfficeVersion_2007,
    };

    public enum eOfficeApp // in case you are looking for a particular app
    {
        eOfficeApp_Word,
        eOfficeApp_Excel,
        eOfficeApp_Outlook,
        eOfficeApp_Access,
        eOfficeApp_PowerPoint,
    };

public static eOfficeVersion GetApplicationVersion(eOfficeApp appToCheck)
        {
            // some of this function is based on the code in the article at: http://support.microsoft.com/kb/q247985/
            string progID = GetProgID(appToCheck);

            RegistryKey hKey = Registry.ClassesRoot.OpenSubKey(progID, RegistryKeyPermissionCheck.ReadSubTree, System.Security.AccessControl.RegistryRights.ReadKey);
            if (hKey == null)
                return eOfficeVersion.eOfficeVersion_Unknown;

            RegistryKey hKey1 = hKey.OpenSubKey("CurVer", RegistryKeyPermissionCheck.ReadSubTree, System.Security.AccessControl.RegistryRights.ReadKey);
            if (hKey1 == null)
            {
                hKey1.Close();
                hKey.Close();
                return eOfficeVersion.eOfficeVersion_Unknown;
            }

            // Get the Version information
            string progAndVersion = (string)hKey1.GetValue("");

            // Close the registry keys
            hKey1.Close();
            hKey.Close();

            // Error while querying for value
            if (progAndVersion == null)
                return eOfficeVersion.eOfficeVersion_Unknown;

            // At this point progAndVersion contains the ProgID followed by a number. 
            // For example, Word 97 will return Word.Application.8 and Word 2000 will return Word.Application.9

            int lastDot = progAndVersion.LastIndexOf('.');
            int firstCharOfVersion = lastDot + 1; // + 1 to get rid of the dot at the front
            string versionString = progAndVersion.Substring(firstCharOfVersion, progAndVersion.Length - firstCharOfVersion);

            return StringToVersion(versionString);
        }

Excel.Application

11-Feb-08 10:34 AM

You can find out the Office Version through the Excel Application Object which provides Version property to know the current version. Excel Application Object.Version returns 12.0 for Office 2007 and 11.0 for Office 2003 etc.,

Sorry wasn't clear enough :) except through VSTO...but what if...

11-Feb-08 11:21 AM

I'm just currious as never tried it, but let say:

Situation 1:
I have 2007 Office and .NET build with reference to 11 (or vice versa) and want to check version on that client will I be able to do that with VSTO also?
(will I'll get exception here with improper DLL linked message)
If I want to serve just one version but for "testing version" purposes do I have to install all possible versions of Office (to be able to create proper reference, so be able to say proper version of Office client and go through all version instances) to point out what version client have?

Situation 2:
What will happen if I have client without any version of Office and try to instantiate any office object (well this is hypotetical question as we know we will get exception at this point)?

trying to run your code

12-Feb-08 03:02 AM

I am trying to run your code, but getting some exceptions (sorry, I am very new with this)

I have oppened a new console proj, then trying:

static void Main(string[] args)

{

GetApplicationVersion("MS Excel");

}

What should i do?

Do i need any special references?

Thanks,

Aldo.

Trying your code

12-Feb-08 03:04 AM

Hi, I oppened a console proj, and trying

static void Main(string[] args)

{

GetApplicationVersion("MS Excel");

}

but it doesn't work.

What should I do? Need any special references?

Thanks, Aldo.

Oh....what you have to do...

12-Feb-08 04:01 AM

There is enum class declared as in paramater for The call you are trying:

public enum eOfficeApp // in case you are looking for a particular app
    {
        eOfficeApp_Word,
        eOfficeApp_Excel,
        eOfficeApp_Outlook,
        eOfficeApp_Access,
        eOfficeApp_PowerPoint,
    };

So to call method you should write this:

GetApplicationVersion(eOfficeApp.eOfficeApp_Excel);

Look at the post up...

12-Feb-08 04:03 AM

end of post

more exceptions - references maybe?

12-Feb-08 04:16 AM

Hi man, thanks for your assistence. :)

Now I have the following exceptions:

Error 1 The name 'GetProgID' does not exist in the current context E:\Visual Studio 2008\Projects\CheckOffice\CheckOffice\Program.cs 34 29 CheckOffice

Error 2 The type or namespace name 'RegistryKey' could not be found (are you missing a using directive or an assembly reference?) E:\Visual Studio 2008\Projects\CheckOffice\CheckOffice\Program.cs 35 13 CheckOffice

Error 3 The name 'Registry' does not exist in the current context E:\Visual Studio 2008\Projects\CheckOffice\CheckOffice\Program.cs 35 32 CheckOffice

Error 4 The name 'RegistryKeyPermissionCheck' does not exist in the current context E:\Visual Studio 2008\Projects\CheckOffice\CheckOffice\Program.cs 35 72 CheckOffice

Error 5 The type or namespace name 'RegistryKey' could not be found (are you missing a using directive or an assembly reference?) E:\Visual Studio 2008\Projects\CheckOffice\CheckOffice\Program.cs 40 13 CheckOffice

Error 6 The name 'RegistryKeyPermissionCheck' does not exist in the current context E:\Visual Studio 2008\Projects\CheckOffice\CheckOffice\Program.cs 40 59 CheckOffice

Error 7 The name 'StringToVersion' does not exist in the current context E:\Visual Studio 2008\Projects\CheckOffice\CheckOffice\Program.cs 59 20 CheckOffice

Ok.. you have to add proper references to your project....

12-Feb-08 04:23 AM

name spaces missing in your code
using System.Security.Permissions;
using Microsoft.Win32;

I think you'll have to add reference to DLL but not sure here about that you'll see (mscorlib.dll in the list)

Missing source:

public static string GetApplicationAsString(eOfficeApp officeApp)
        {
            switch(officeApp) 
            {
                case eOfficeApp.eOfficeApp_Word: { return "Word"; } break;
                case eOfficeApp.eOfficeApp_Excel: { return "Excel"; } break;
                case eOfficeApp.eOfficeApp_Outlook: { return "Outlook"; } break;
                case eOfficeApp.eOfficeApp_Access: { return "Access"; } break;
                case eOfficeApp.eOfficeApp_PowerPoint: { return "Powerpoint"; } break;
                default:                    { /*ASSERT(false);*/ return string.Empty; }break; // added another ???
            }
        }

        public static string GetProgID(eOfficeApp officeApp)
        {
            // ProgIDs from http://support.microsoft.com/kb/240794/EN-US/
            switch (officeApp)
            {
                case eOfficeApp.eOfficeApp_Word: { return "Word.Application"; } break;
                case eOfficeApp.eOfficeApp_Excel: { return "Excel.Application"; } break;
                case eOfficeApp.eOfficeApp_Outlook: { return "Outlook.Application"; } break;
                case eOfficeApp.eOfficeApp_Access: { return "Access.Application"; } break;
                case eOfficeApp.eOfficeApp_PowerPoint: { return "Powerpoint.Application"; } break;
                default: { /*ASSERT(false);*/ return string.Empty; } break; // added another ???
            }
        }

        public static eOfficeVersion StringToVersion(string versionString)
        {
            // mapping between the marketing version (e.g. 2003) and the behind-the-scenes version
            if("7" == versionString){
                return eOfficeVersion.eOfficeVersion_95;
            }else if("8" == versionString){
                return eOfficeVersion.eOfficeVersion_97;
            }else if("9" == versionString){
                return eOfficeVersion.eOfficeVersion_2000;
            }else if("10" == versionString){
                return eOfficeVersion.eOfficeVersion_XP;
            }else if("11" == versionString){
                return eOfficeVersion.eOfficeVersion_2003;
            }else if("12" == versionString){
                return eOfficeVersion.eOfficeVersion_2007;
            }else{
                return eOfficeVersion.eOfficeVersion_Unknown; // added another ???
            }
        }

another error encountered

12-Feb-09 05:22 AM

Hi ,

I have faced an another problem when trying to run this code in studio2008.It is throwing a COM exception saying "Programmatic access to visual basic project is not trusted".please suggest me how do it get rid of this.

Error trying to access registry. .NET Framework

Microsoft.Win32.RegistryKey.Win32Error(Int32 errorCode, String str) bij Microsoft.Win32.RegistryKey.CreateSubKey(String subkey, RegistryKeyPermissionCheck permissionCheck, RegistrySecurity registrySecurity) bij Microsoft.Win32.RegistryKey.CreateSubKey(String subkey) bij Microsoft.Win32.Registry.SetValue please help me with this matter? Many thanks and greetings from Brugge (Bruges - Belgium), Michel Visual Studio General Discussions Kassasysteem.MDIKassasysteem.MDIKassasysteem (1) Microsoft.Win32.RegistryKey.CreateSubKey (1) Microsoft.Win32.RegistryKey.Win32Error (1) Microsoft.Win32.Registry.SetValue (1) Visual Studio (1) SHGetKnownFolderPath (1) Vista (1) XP (1) did you right click on your exe and Hello, Thank you for responding. The error does occur when I start my program in Visual Studio. It has not been distributed yet and is still in programming phase. What can I will not run as a standard user (it only worked for an administrator). - - David Wilkinson Visual C++ MVP - -- -- Original Message - -- -- From: "David Wilkinson" <no-reply@effisols.com> Newsgroups: microsoft.public.vstudio

Upgrade Visual Studio 6 to Visual Studio 2008? .NET Framework

Upgrade Visual Studio 6 to Visual Studio 2008? .NET Framework I have Visual Studio 6 Professional. Can I install the upgrade version of Visual Studio 2008 Professional? I cannot find this info on Microsoft's website. Visual Studio General Discussions

Is Visual Studio self-hosting ? .NET Framework

Is Visual Studio self-hosting ? .NET Framework Does Microsoft use Visual Studio IDE, Visual Studio Debugger, Visual Studio Linker and Visual Studio compiler for developing Visual Studio ? Or is Visual Studio not good

Visual Studio versioning . . . . how to tell? .NET Framework

Visual Studio versioning . . . . how to tell? .NET Framework To my knowledge, Visual studio 6 was released in 1998, then Visual Studio .NET 2002 is VS 7, then Visual Studio .NET 2003 is VS 7.1, then Visual Studio .NET 2005 is VS 8, then