C# .NET - C# code to set Excel workbook macro enabled and to trust VBA projects

Asked By Aldo Liaks on 14-Oct-08 01:37 PM

Hi guys,

I am working on a C# application that inserts VBA code into a new Excel Workbook instance.

Is there anyway to check and set the workbook to be macro enabled and to trust VBA projects?

Thanks in advance,
Aldo.

re

Web Star replied to Aldo Liaks on 14-Oct-08 11:46 PM

Creating a Workbook That Contains VBA Code

The first step is to create a macro-enabled workbook that contains a simple VBA macro. Before you can expose code in a customization to VBA, the workbook must already contain VBA code. Otherwise, Visual Studio Tools for Office cannot modify the VBA project to enable VBA code to call into the Visual Studio Tools for Office assembly.

If you already have a workbook that contains VBA code that you want to use, you can skip this step.

To create a workbook that contains VBA code

Start Excel 2007.
Save the active document as an Excel Macro-Enabled Workbook (*.xlsm) with the name WorkbookWithVBA. Save it to a convenient location, such as the desktop.
On the Ribbon, click the Developer tab.

Note:

If the Developer tab is not visible, you must first show it. For more information, see http://msdn.microsoft.com/en-us/library/bb608625.aspx.
In the Code group, click Visual Basic.

The Visual Basic Editor opens.
In the Project window, double-click ThisWorkbook.

The code file for the ThisWorkbook object opens.
Add the following VBA code to the code file. This code defines a simple function that does nothing. The only purpose of this function is to ensure that a VBA project exists in the workbook. This is required for later steps in this walkthrough.
Copy Code
Sub EmptySub() End Sub
Save the document and exit Word.

Note:
If the Developer tab is not visible, you must first show it. For more information, see http://msdn.microsoft.com/en-us/library/bb608625.aspx.

Creating the Project

Now you can create a document-level project for Excel 2007 that uses the macro-enabled workbook you created earlier.

To create a new project

Start Visual Studio.
On the File menu, point to New, and then click Project.
In the Project Types pane, expand Visual C#, and then expand Office.
Select the 2007 folder.
In the Templates pane, select the Excel Workbook project.
In the Name box, type CallingCodeFromVBA.
Click OK.

The Visual Studio Tools for Office Project Wizard opens.
Select Copy an existing document, and, in the Full path of the existing document box, specify the location of the WorkbookWithVBA workbook that you created earlier. If you are using your own macro-enabled workbook, specify the location of that workbook instead.
Click Finish.

Visual Studio opens the WorkbookWithVBA workbook in the designer and adds the CallingCodeFromVBA project to Solution Explorer.

Trusting the Location of the Workbook

Before you can expose code in your solution to VBA code in the workbook, you must trust VBA in the workbook to run. There are several ways to do this. In this walkthrough, you will accomplish this task by trusting the location of the workbook in the Trust Center in Excel.

To trust the location of the workbook

Start Excel.
Click the Microsoft Office Button .
Click the Excel Options button.
In the categories pane, click Trust Center.
In the details pane, click Trust Center Settings.
In the categories pane, click Trusted Locations.
In the details pane, click Add new location.
In the Microsoft Office Trusted Location dialog box, browse to the folder that contains the CallingCodeFromVBA project.
Select Subfolders of this location are also trusted.
In the Microsoft Office Trusted Location dialog box, click OK.
In the Trust Center dialog box, click OK.
In the Excel Options dialog box, click OK.
Exit Excel.

Adding a Method to the Sheet1 Class

Now that the VBA project is set up, add a public method to the Sheet1 host item class that you can call from VBA code.

To add a method to the Sheet1 class

In Solution Explorer, right-click Sheet1.cs, and then click View Code.

The Sheet1.cs file opens in the Code Editor.

Add the following code to the Sheet1 class. The CreateVstoNamedRange method creates a new http://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.namedrange.aspx object at the specified range. This method also creates an event handler for the http://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.namedrange.selected.aspx event of the http://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.namedrange.aspx. Later in this walkthrough, you will call the CreateVstoNamedRange method from VBA code in the document.

Copy Code

private Microsoft.Office.Tools.Excel.NamedRange namedRange1;
public void CreateVstoNamedRange(Excel.Range range, string name)
{
    if (!this.Controls.Contains(name))
    {
        namedRange1 = this.Controls.AddNamedRange(range, name);
        namedRange1.Selected += new Excel.DocEvents_SelectionChangeEventHandler(
                namedRange1_Selected);
    }
    else
    {
        MessageBox.Show("A named range with this specific name " +
            "already exists on the worksheet.");
    }
}
private void namedRange1_Selected(Microsoft.Office.Interop.Excel.Range Target)
{
    MessageBox.Show("This named range was created by Visual Studio " +
        "Tools for Office.");
}

Add the following method to the Sheet1 class. This method overrides the http://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.worksheet.getautomationobject.aspx method to return the current instance of the Sheet1 class.
C#

Copy Code

protected override object GetAutomationObject() { return this; }
Apply the following attributes before the first line of the Sheet1 class declaration. These attributes make the class visible to COM, but without generating a class interface.
C#

Copy Code

[System.Runtime.InteropServices.ComVisible(true)] [System.Runtime.InteropServices.ClassInterface( System.Runtime.InteropServices.ClassInterfaceType.None)]

Extracting an Interface for the Sheet1 Class

Before you can expose the CreateVstoNamedRange method to VBA code, you must create a public interface that defines this method, and you must expose this interface to COM.

To extract an interface for the Sheet1 class

In the Sheet1.cs code file, click anywhere in the Sheet1 class.
On the Refactor menu, click Extract Interface.
In the Extract Interface dialog box, in the Select public members to form interface box, click the entry for the CreateVstoNamedRange method.
Click OK.

Visual Studio generates a new interface named ISheet1, and it modifies the definition of the Sheet1 class so that it implements the ISheet1 interface. Visual Studio also opens the ISheet1.cs file in the Code Editor.
In the ISheet1.cs file, replace the ISheet1 interface declaration with the following code. This code makes the ISheet1 interface public, and it applies the http://msdn.microsoft.com/en-us/library/system.runtime.interopservices.comvisibleattribute.aspx attribute to make the interface visible to COM.
C#

Copy Code

[System.Runtime.InteropServices.ComVisible(true)] public interface ISheet1 { void CreateVstoNamedRange(Microsoft.Office.Interop.Excel.Range range, string name); }
Build the project.

Exposing the Method to VBA Code

To expose the CreateVstoNamedRange method to VBA code in the workbook, set the ReferenceAssemblyFromVbaProject property for the Sheet1 host item to True.

To expose the method to VBA code

In Solution Explorer, double-click Sheet1.cs.

The WorkbookWithVBA file opens in the designer, with Sheet1 visible.
In the Properties window, select the ReferenceAssemblyFromVbaProject property, and change the value to True.
Click OK in the message that is displayed.
Build the project.

Calling the Method from VBA Code

You can now call the CreateVstoNamedRange method from VBA code in the workbook.

Note:
In this walkthrough, you will add VBA code to the workbook while debugging the project. The VBA code you add to this document will be overwritten the next time that you build the project, because Visual Studio replaces the document in the build output folder with a copy of the document from the main project folder. If you want to save the VBA code, you can copy it into the document in the project folder. For more information, see http://msdn.microsoft.com/en-us/library/bb386306.aspx.

Note:

In this walkthrough, you will add VBA code to the workbook while debugging the project. The VBA code you add to this document will be overwritten the next time that you build the project, because Visual Studio replaces the document in the build output folder with a copy of the document from the main project folder. If you want to save the VBA code, you can copy it into the document in the project folder. For more information, see http://msdn.microsoft.com/en-us/library/bb386306.aspx.

To call the method from VBA code

Press F5 to run your project.
On the Developer tab, in the Code group, click Visual Basic.

The Visual Basic Editor opens.
On the Insert menu, click Module.

Add the following code to the new module.

This code calls the CreateTable method in the Visual Studio Tools for Office assembly. The macro accesses this method by using the global GetManagedClass method to access the Sheet1 host item class that you exposed to VBA code. The GetManagedClass method was automatically generated when you set the ReferenceAssemblyFromVbaProject property earlier in this walkthrough.

Copy Code

Sub CallVSTOMethod()
    Dim VSTOSheet1 As CallingCodeFromVBA.Sheet1
    Set VSTOSheet1 = GetManagedClass(Sheet1)
    Call VSTOSheet1.CreateVstoNamedRange(Sheet1.Range("A1"), "VstoNamedRange")
End Sub

Press F5.
In the open workbook, click cell A1 on Sheet1. Verify that the message box appears.
Exit Excel without saving your changes

Solution

Perry replied to Aldo Liaks on 15-Oct-08 12:08 AM

Hi,

I remember that I have tried this but it seemed it is not possible to trust VBA project or enable macro via any programming language because of security reason. You have to reduce the seurity level at your own to make this but that will also not reasonable.

Regards,
Megha

read this example

C_A P replied to Aldo Liaks on 15-Oct-08 12:35 AM

Access to Visual Basic Projects needs to be manually enabled for
this to work. So there is no threat of malicious action by this code.

VB Code:

Option Explicit
'Add reference to MS Excel xx.0 Object Library
'Trust Access to VB Projects needs to be enabled in Excel first.
'Check it, in Tools > Macros > Security > Trusted Publishers tab - "Trust access to Visual Basic projects"
Private moApp As Excel.Application
 
Private Sub Command1_Click()
    
    On Error GoTo No_Bugs
    
    Dim oWB As Excel.Workbook
    Dim i As Integer
    Dim iReplace As Integer
    Dim sReplace As String
    Dim sOriginalLine As String
    Dim iLine As Integer
    
    moApp.Visible = True
    'Do a loop here and iterate through your folder containing the workbooks.
    'ToDo: Start workbook loop of all folders and sub folders passing the workbook name
        Set oWB = moApp.Workbooks.Open("C:\Documents and Settings\VB-Guru\My Documents\Book2.xls")
        If moApp.VBE.ActiveVBProject.VBComponents.Item(1).Properties("HasPassword").Value = False Then
            If moApp.VBE.ActiveVBProject.VBComponents.Count > 0 Then 'Components are like sheet1,thisworkbook,etc.
                For i = 1 To moApp.VBE.ActiveVBProject.VBComponents.Count
                    moApp.VBE.ActiveVBProject.VBComponents.Item(i).Activate
                    Debug.Print "------------------------------------------------------------------"
                    Debug.Print moApp.VBE.ActiveVBProject.VBComponents.Item(i).Name & ": Type - " & moApp.VBE.ActiveVBProject.VBComponents.Item(i).Type
                    Debug.Print "Lines: " & moApp.VBE.ActiveVBProject.VBE.CodePanes.Item(i).CodeModule.CountOfLines
                    If moApp.VBE.ActiveVBProject.VBE.CodePanes.Item(i).CodeModule.CountOfLines > 0 Then
                        'Loop through all lines searching for "\\servername and replace with new server name"
                        For iLine = 1 To moApp.VBE.ActiveVBProject.VBE.CodePanes.Item(i).CodeModule.CountOfLines
                            Debug.Print "Line " & iLine & "  : " & moApp.VBE.ActiveVBProject.VBE.CodePanes.Item(i).CodeModule.Lines(iLine, 1)
                            iReplace = InStr(1, moApp.VBE.ActiveVBProject.VBE.CodePanes.Item(i).CodeModule.Lines(iLine, 1), "\\Server Name", vbTextCompare)
                            If iReplace > 0 Then
                                sOriginalLine = moApp.VBE.ActiveVBProject.VBE.CodePanes.Item(i).CodeModule.Lines(iLine, 1)
                                sReplace = Replace(sOriginalLine, "\\Server Name", "\\New Server", 1, 1, vbTextCompare)
                                moApp.VBE.ActiveVBProject.VBE.CodePanes.Item(i).CodeModule.ReplaceLine iLine, sReplace
                                Debug.Print "Repl " & iLine & "  : " & moApp.VBE.ActiveVBProject.VBE.CodePanes.Item(i).CodeModule.Lines(iLine, 1)
                            End If
                        Next
                    Else
                        Debug.Print moApp.VBE.ActiveVBProject.VBComponents.Item(i).Name & " contains No Macro code!"
                    End If
                Next
            Else
                Debug.Print oWB.FullName & " contains No VBComponents!!"
            End If
        End If
        oWB.Close True 'Save modifications to workbook
    'Loop
    Exit Sub
    
No_Bugs:
    If Err.Number = 50289 Then
        MsgBox oWB.Name & " contains a password on the VBProject!", vbOKOnly + vbExclamation
        Debug.Print oWB.FullName & " contains a password on the VBProject!"
    Else
        MsgBox Err.Number & " - " & Err.Description, vbOKOnly + vbExclamation
        Debug.Print "Other error" & vbNewLine & Err.Number & " - " & Err.Description
    End If
End Sub
 
Private Sub Form_Load()
    Set moApp = New Excel.Application
    moApp.Visible = False
End Sub
 
'MY SAMPLE OUTPUT!!!!
'------------------------------------------------------------------
'ThisWorkbook: Type - 100
'Lines: 3
'Line 1  : Public Sub Test()
'Line 2  :     MsgBox "Module Test"
'Line 3  : End Sub
'------------------------------------------------------------------
'Sheet1: Type - 100
'Lines: 0
'Sheet1 contains No Macro code!
'------------------------------------------------------------------
'Sheet2: Type - 100
'Lines: 0
'Sheet2 contains No Macro code!
'------------------------------------------------------------------
'Sheet3: Type - 100
'Lines: 0
'Sheet3 contains No Macro code!
'------------------------------------------------------------------
'Module1: Type - 1
'Lines: 5
'Line 1  : Private Sub Workbook_Open()
'Line 2  :     'MsgBox "\\Server Name\Test"
'Repl 2:       'MsgBox "\\New Server\Test"
'Line 3  : End Sub
'Line 4  :
'Line 5  :

VB/Excel Guru™

Attached Images

read this

C_A P replied to Aldo Liaks on 15-Oct-08 12:36 AM

http://geekswithblogs.net/urig/archive/2007/01/15/103617.aspx

Just had my first encounter with VSTO, trying to embed a C# macro in an Excel workbook. I fired up the ol' Visual Studio 2005, created a new "Excel Workbook" project and bumped straight into this:

"Programmatic access to the Microsoft Office Visual Basic for Applications project system could not be enabled. If Microsoft Office Word or Microsoft Excel is running, it can prevent programmatic access from being enabled. Exit Word or Excel before opening or creating your project."

The problem with the above message? I didn't have any Word or Excel process running at all.

A quick google gave me http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=344932&SiteID=1 for Office 2003 (thanks go to http://forums.microsoft.com/MSDN/User/Profile.aspx?UserID=451&SiteID=1):

On the Tools menu, point to Macro, and then click Security.
Click the Trusted Publishers tab.
Select the check box next to Trust access to Visual Basic Project, and then click OK.
Close all Office applications and try your project again.

But! I'm using Office 2007 beta. Where on earth is the Tools menu there? Yours truly spent well over 2 minutes digging through the menus and ribbons to give you the following instructions:

Open Excel 2007.
Click the Office Button (top-left most button in the window)
Click the Excel Options button.
Click Trust Center in the menu on the left.
Click Trust Center Settings in the window on the right.
In the new window that opens click Macros in the menu on the left.
Check the box next to Trust Access to the VBA project object model.
Click OK.
Click OK.
Close all Office applications and try your project again.

Works like a charm, as long as you accept the security implications of allowing Excel and Word programmatic access to the "Microsoft Office Visual Basic for Applications project system" mentioned above.

Thanks!

Aldo Liaks replied to Web Star on 15-Oct-08 01:22 AM

end of post