VSTO VBA Interop Sample

This sample demonstrates how to call a method in a document-level customization for Microsoft Office Excel from Visual Basic for Applications (VBA) code in the workbook.

 
 
 
 
 
4 Star
(1)
6,046 times
Add to favorites
2/19/2011
E-mail Twitter del.icio.us Digg Facebook

Solution explorer

C#
VB.NET
<html><head><META HTTP-EQUIV="Content-Type" CONTENT="text/html; CHARSET=utf-8" /><META NAME="save" CONTENT="history" /><title xmlns:mshelp="http://msdn.microsoft.com/mshelp" xmlns:msxsl="urn:schemas-microsoft-com:xslt">VSTO VBA Interop Sample</title><META NAME="Description" CONTENT="This sample runs only in Microsoft Office Excel 2007."></META><META NAME="MS.LOCALE" CONTENT="en-us" /><style>
body
{font-family:Verdana;font-size:medium;}
div#mainSection, div#header
{font-size:70%;width: 100%;}
div#mainBody
{font-size:90%;}
div#mainSection div table
{font-size: 100%;text-align: left;}
span#nsrTitle
{color:#003399;font-size:90%;font-weight:600;}
div#header
{background-color:#D4DFFF;}
.heading
{font-size:120%;color:#003399;}
.subHeading
{font-size:100%;margin-bottom:4;}
li
{margin-top:-2;margin-bottom:3;}
p
{margin-top:10;margin-bottom:5;}
div.tableSection p
{margin-top:1;margin-bottom:4;}	
div#mainSection table th
{background-color:#EFEFF7;color:#000066;text-align:left;}
div#mainSection table td
{background-color:#F7F7FF;}
div#mainSection table
{margin-top:5px;margin-bottom: px;}
.style1{color: Green;}
			</style></head><body><div id="header"><table width="100%" id="topTable"></table></div><div id="mainSection"><div id="mainBody"><p><span class="style1">&#39; Copyright © Microsoft Corporation. All Rights Reserved.
    </span>
    <br class="style1" />
    <span class="style1">&#39; This code released under the terms of the </span>
    <br class="style1" />
    <span class="style1">&#39; Microsoft Public License (MS-PL,
    <a href="http://opensource.org/licenses/ms-pl.html">
    http://opensource.org/licenses/ms-pl.html</a>.)</span></p><br /><font color="DarkGray">[This readme is a partial copy of the sample's topic in the SDK documentation.]</font><br /><div class="introduction"><div class="alert"><table width="100%" cellspacing="0" cellpadding="0" xmlns:mshelp="http://msdn.microsoft.com/mshelp" xmlns:msxsl="urn:schemas-microsoft-com:xslt"><tr><th align="left"><b>Note:</b></th></tr><tr><td><p>This sample runs in Microsoft Office Excel 2007 and higher.</p></td></tr></table></div><p xmlns:mshelp="http://msdn.microsoft.com/mshelp" xmlns:msxsl="urn:schemas-microsoft-com:xslt">
        This sample demonstrates how to call a method in a document-level customization 
        for Microsoft Office Excel from Visual Basic for Applications (VBA) code in the 
        workbook. The document customization gets data from an ADO.NET data service 
        which is then exposed to VBA code through public methods in defined in the 
        document customization. The Excel document specifies user defined functions in 
        VBA that call into the public methods exposed by the document customization.</p><div class="alert"><table width="100%" cellspacing="0" cellpadding="0" xmlns:mshelp="http://msdn.microsoft.com/mshelp" xmlns:msxsl="urn:schemas-microsoft-com:xslt"><tr><th align="left"><b>Security Note:</b></th></tr><tr><td><p>This sample code is intended to illustrate a concept, and it shows only the code that is relevant to that concept. It may not meet the security requirements for a specific environment, and it should not be used exactly as shown. We recommend that you add security and error-handling code to make your projects more secure and robust. Microsoft provides this sample code "AS IS" with no warranties.</p></td></tr></table></div><p xmlns:mshelp="http://msdn.microsoft.com/mshelp" xmlns:msxsl="urn:schemas-microsoft-com:xslt">For information about how to install the sample project on your computer, see How to: Install and Use Sample Files Found in Help.</p></div><h3 class="procedureSubHeading" xmlns:mshelp="http://msdn.microsoft.com/mshelp" xmlns:msxsl="urn:schemas-microsoft-com:xslt">To run this sample</h3><div class="subSection"><ol xmlns:mshelp="http://msdn.microsoft.com/mshelp" xmlns:msxsl="urn:schemas-microsoft-com:xslt"><li>
    <p>
        <span style="font-size:8.0pt;font-family:&quot;Verdana&quot;,&quot;sans-serif&quot;;
color:black">This sample uses the Northwind database. Install the scripts from the 
        download center: <o:p></o:p></span>
    </p>
    <p>
        <span style="font-size:8.0pt;font-family:&quot;Verdana&quot;,&quot;sans-serif&quot;;
color:black">
        <a href="http://www.microsoft.com/downloads/details.aspx?FamilyID=06616212-0356-46A0-8DA2-EEBC53A68034&amp;displaylang=en">
        http://www.microsoft.com/downloads/details.aspx?FamilyID=06616212-0356-46A0-8DA2-EEBC53A68034&amp;displaylang=en</a>
<o:p></o:p></span>
    </p>
    <p>
        <span style="font-size:8.0pt;font-family:&quot;Verdana&quot;,&quot;sans-serif&quot;;
color:black">The installer will copy a script to create the Northwind database to your 
        C: drive. <o:p></o:p></span>
    </p>
    <p>
        <span style="font-size:8.0pt;font-family:&quot;Verdana&quot;,&quot;sans-serif&quot;;
color:black">To install the database on your local sqlexpress instance, open a command 
        prompt as an administrator and run the following:<o:p></o:p></span></p>
    <p>
        <span style="font-size:8.0pt;font-family:&quot;Lucida Console&quot;;
color:black">&gt;sqlcmd -S .\sqlexpress -i &quot;C:\SQL Server 2000 Sample 
        Databases\instnwnd.sql&quot;<o:p></o:p></span></p>
    </li>
    <li>
        <p>Ensure the VBA solution in the document is trusted to run. You can do so by 
            adding the project directory as a trusted location by performing the following 
            steps</p>
        <ul xmlns:mshelp="http://msdn.microsoft.com/mshelp" 
            xmlns:msxsl="urn:schemas-microsoft-com:xslt">
            <li>
                <p>
                    Go to Excel Options.
                </p>
            </li>
            <li>
                <p>
                    In the categories pane, click Trust Center.
                </p>
            </li>
            <li>
                <p>
                    In the details pane, click Trust Center Settings.
                </p>
            </li>
            <li>
                <p>
                    In the categories pane, click Trusted Locations.
                </p>
            </li>
            <li>
                <p>
                    In the details pane, click Add new location. In the Microsoft Office Trusted 
                    Location dialog box, browse to the folder that contains the project.
                </p>
            </li>
            <li>
                <p>
                    Select Subfolders of this location are also trusted.
                </p>
            </li>
        </ul>
    </li>
    <li>
        <p>Set default project to NorthwindEmployees.</p>
    </li>
    <li>
        <p>Press F5 to start debugging the solution.</p>
    </li>
    <li>
        <p>Excel worksheet will display a table that lists the Employee Names present in the 
            Northwind database.</p></li><li><p>You can get the notes associated with 
        each employee by calling the VBA function GetNotes and passing it the id for 
        each employee.</p>
    </li></ol></div><h1 class="heading" xmlns:mshelp="http://msdn.microsoft.com/mshelp" xmlns:msxsl="urn:schemas-microsoft-com:xslt"><span onclick="ExpandCollapse(requirementsTitleToggle)" style="cursor:default;" onkeypress="ExpandCollapse_CheckKey(requirementsTitleToggle, event)" tabindex="0">Requirements</h1><div id="requirementsTitleSection" class="section"><p xmlns:mshelp="http://msdn.microsoft.com/mshelp" xmlns:msxsl="urn:schemas-microsoft-com:xslt">This sample requires the following applications:</p><ul xmlns:mshelp="http://msdn.microsoft.com/mshelp" xmlns:msxsl="urn:schemas-microsoft-com:xslt"><li><p>
            Visual Studio Tools for Office.</p></li><li><p>Microsoft Office Excel 2007.</p></li></ul></div><h1 class="heading" xmlns:mshelp="http://msdn.microsoft.com/mshelp" xmlns:msxsl="urn:schemas-microsoft-com:xslt"><span onclick="ExpandCollapse(demonstratesToggle)" style="cursor:default;" onkeypress="ExpandCollapse_CheckKey(demonstratesToggle, event)" tabindex="0">Demonstrates</span></h1><div id="demonstratesSection" class="section"><p xmlns:mshelp="http://msdn.microsoft.com/mshelp" xmlns:msxsl="urn:schemas-microsoft-com:xslt">This sample demonstrates the following concepts:</p><ul xmlns:mshelp="http://msdn.microsoft.com/mshelp" xmlns:msxsl="urn:schemas-microsoft-com:xslt"><li><p>
    Excel workbook customization accessing Data from ADO.NET data service</p></li><li><p>
        Exposing methods in workbook customization to VBA code.</p></li><li><p>Calling 
        the method from VBA code.</p></li></ul></div><h1 class="heading" xmlns:mshelp="http://msdn.microsoft.com/mshelp" xmlns:msxsl="urn:schemas-microsoft-com:xslt"><span onclick="ExpandCollapse(seeAlsoToggle)" style="cursor:default;" onkeypress="ExpandCollapse_CheckKey(seeAlsoToggle, event)" tabindex="0">See Also</span></h1><div id="seeAlsoSection" class="section"><h4 class="subHeading" xmlns:mshelp="http://msdn.microsoft.com/mshelp" xmlns:msxsl="urn:schemas-microsoft-com:xslt">Concepts</h4><div class="seeAlsoStyle">
    Calling Code in Document-Level Customizations from VBA</div><div class="seeAlsoStyle">
        How to: Expose Code to VBA in a Visual Basic Project</div><div class="seeAlsoStyle">
        How to: Expose Code to VBA in a Visual C# Project</div><div class="seeAlsoStyle">
        Walkthrough: Calling Code from VBA in a Visual Basic Project</div><div class="seeAlsoStyle">
        Walkthrough: Calling Code from VBA in a Visual C# Project</div></div></div></div></body></html>