Working with Excel in VSTA and VSTA in InfoPath

Recently we were asked to show how to access programs with VBA integration, like Microsoft Excel, from within VSTA- specifically from VSTA in InfoPath.  The process to run an add-in from an excel file is shown below for both InfoPath (VB.Net) and  ShapeAppCSharp (C#).  

1)       Add a reference to the “Microsoft Excel 12.0 Object Library” from the COM tab (the version may be different).  This will appear as “Microsoft.Office.Interop.Excel” in the references.

2)      Add the method below (shown here in VB.Net).  To run the method at start up, add a call to the method in the InternalStartup method.

Private Sub ControlExcel()

'create a new instance of excel and make it visible

Dim myExcel As Microsoft.Office.Interop.Excel.Application

myExcel = New Microsoft.Office.Interop.Excel.Application()

myExcel.Visible = True

'open the excel file containing the add-in

myExcel.Workbooks.Open("C:\VSTA_Test.xlsm")

'run the add-in without parameters (like a macro)

myExcel.Run("HelloWorld")

'run the add-in with parameters

myExcel.Run("HelloWorld", "InfoPath")

End Sub

3)      Change the security setting to allow partially trusted callers (like the Microsoft.Office.Interop.Excel assembly).  Do this by selecting in the Design View, Tools then Form Options to bring up the Form Options window.  Select the category Security and Trust and change the default Security Level from Automatically determine security level to Full Trust.   The Restricted and Domain security levels will not allow partially trusted callers.

4)      Sign the template.  If the template is not signed InfoPath will display the message “InfoPath cannot open the form.  To fix this problem, contact your system administrator.”.

1)       Add a reference to the “Microsoft Excel 12.0 Object Library” from the COM tab (the version may be different).  This will appear as “Microsoft.Office.Interop.Excel” in the references.

2)      Add the method below (shown here in C#).  To run the method at start up, add a call to the method in the AppAddIn_Startup method.

private void ControlExcel()

{

//create a missing value object for use in the optional parameters

object missingValue = System.Reflection.Missing.Value;

//create a new instance of excel and make it visible

Microsoft.Office.Interop.Excel.ApplicationClass myExcel;

myExcel = new Microsoft.Office.Interop.Excel.ApplicationClass();

myExcel.Visible = true;

//create an excel workbooks instance

Microsoft.Office.Interop.Excel.Workbooks myBooks = myExcel.Workbooks;

//open the excel file containing the add-in,

// using the missingValue object for the optional paramaters

myBooks.Open("C:\\VSTA_Test.xlsm", missingValue, missingValue,

missingValue,missingValue, missingValue, missingValue, missingValue,

missingValue, missingValue, missingValue, missingValue,

missingValue, missingValue, missingValue);

//create the argument array without paramaters

object[] myArgs = new object[] { "HelloWorld" };

//run the add-in without paramaters

myExcel.GetType().InvokeMember("Run", System.Reflection.BindingFlags.InvokeMethod, null, myExcel, myArgs);

//change the argument array to include parameters

myArgs = new object[] { "HelloWorld", "ShapeApp" };

//run the add-in with paramaters

myExcel.GetType().InvokeMember("Run", System.Reflection.BindingFlags.InvokeMethod, null, myExcel, myArgs);

}

Sub HelloWorld(Optional strCalledFrom As String = "Excel")

            'Fill in the 1st cell and autosize the column

Range("A1").Activate

ActiveCell.FormulaR1C1 = "Hello World! from " & strCalledFrom

Columns("A:A").EntireColumn.AutoFit

            'Display a message box showing which program activated the add-in

Call MsgBox("Excel Macro Hello World", vbCritical, "This macro was activated by " _

& strCalledFrom)

End Sub

• The excel file used in this example is a macro enabled Excel 2007 file. 
• To use the attached file, download it and save it directly to the “C” directory, if saved elsewhere update the location in the ControlExcel methods as needed. 
• To use an older version of excel, such as Excel 2003 or XP, copy and paste the excel macro below into an excel VBA module and update the name of the file in the ControlExcel methods. 
• Be sure that the Macro Setting in excel is set to allow macros to run. 
• The code from both ControlExcel methods show above will work in both Visual Studio 2005 and VSTA.
• For more information on how to control office programs from within Visual Studio and VSTA check out HOW TO:  Run Office Macros by Using Automation from Visual C# .NET.