Creating an Estimate Solution Using Visual Studio 2005 Tools for Office

Kevin Schultz, NuSoft Solutions

Pam Davis, NuSoft Solutions

Aisha Khurshid, NuSoft Solutions

Lionel Rouvillois, NuSoft Solutions

Kevin Schuler, Microsoft Corporation

Published: November 2004

Updated: April 2006

Applies to: Microsoft Visual Studio 2005 Tools for the Microsoft Office System, Microsoft Office Professional 2003, Microsoft Office Word 2003, Microsoft Office Excel 2003

Summary: Learn about the Estimate solution, a basic order entry system that uses Microsoft Visual Studio 2005 Tools for the Microsoft Office System (Visual Studio Tools for Office), Microsoft Office Excel 2003, and Microsoft Office Word 2003. (22 printed pages)

Download OfficeVSTOEstimateSample.msi.

Contents

• Scenario Overview

• Installing the Estimate Solution

• Architectural Considerations in Designing the Solution

• Developing the Estimate Solution

• Deploying the Estimate Solution

• About the Authors

• Additional Resources

Scenario Overview

The Estimate solution illustrates a basic order entry system that uses Microsoft Visual Studio 2005 Tools for the Microsoft Office System (Visual Studio Tools for Office). This example is easy to build and run; however, it is not intended as a real-world solution. It uses Microsoft Office Excel 2003 for data entry, calculation, and charting capabilities. Microsoft Office Word 2003 is used for the generation of a report.

The Estimate solution was created for Contoso, Ltd., a fictional vendor of custom cabinets. Contoso believes it can become more competitive and profitable by improving the accuracy and efficiency of its sales process. Contoso's management installed Microsoft Office 2003 on all of its desktops because of the rich feature set and low total cost of ownership. Because Contoso trains all employees in Excel and Word, they chose to use that knowledge and technology investment through an Estimate solution written using Visual Studio Tools for Office.

What You Will Learn

By successfully completing this example, you will gain skills related to:

• The Managed Actions Pane. Create a managed actions pane that contains managed controls. Controls in the actions pane are accessible.

• Managed Code Behind Word and Excel. Create charts in Excel and a custom report in Word using the data from Excel. Customize Office 2003 application menus and toolbars.

• View Controls: Named Ranges and List Objects. Learn to use Excel view controls (including how to protect a List object so that users cannot delete records).

• Schema Mapping. Use an XML Schema Definition (XSD) to map schema elements to an Excel worksheet.

• Data Binding. Use data binding to read data from an Office 2003 document, and to write data to the document.

• Data Binding and Databases. Read data directly from a Microsoft SQL Server 2000 or Microsoft SQL Server 2005 database, or from a Microsoft SQL Server Desktop Engine (MSDE) database, and write data to the database.

• Deployment. Learn about deploying a more secure Office 2003 solution.

Business Scenarios

Contoso custom cabinet customers have several options available for their selection. But with all the options, customers want to hold to a strict budget. The Contoso sales force assists its customers to make appropriate choices within the customer's budget. While this service benefits Contoso's customers greatly, the order entry process is time-consuming and prone to error. In an effort to minimize errors, Contoso sends an order confirmation report to the customer that itemizes the products purchased, quantity, costs, discounted price, and total price charged.

In the past, Contoso's sales representatives found it difficult and time-consuming to search for the appropriate products, calculate their costs, and correctly enter the information on an estimation form. The sales force could save time, increase accuracy, and enhance customer satisfaction by combining the estimation process with an order entry solution based on Excel, an application that their sales staff is very familiar with. Contoso's management believed that the order entry process was an excellent place to begin realizing the full benefit of its software investment.

This order entry process is easy to understand and typical of many companies, which makes it an ideal sample application for solution architects and developers.

Solution Description

The solution is a simple system for entering customer orders to create an initial estimate for the customer. The system also creates a confirmation report for customers. The user interface is designed to be self-explanatory and intuitive. The solution automates a manual order and estimation process.

The process begins when a customer requests an estimate and a Contoso sales representative creates a document using an Excel template. The sales representative enters the customer's information and cabinet choices in this Excel worksheet created for order entry, and then the sales representative submits the entered data to a database by clicking a button on the actions pane. This automatically creates a Word report for the customer that confirms the estimate.

Example Customer Interaction

Consider the following example of a Contoso sales representative's interaction with a customer using the Estimate solution:

• Karen, a sales representative at Contoso, Ltd., is on the phone with John Steel, an existing customer who wants to purchase cabinets for his new kitchen.

• Karen and John discuss his preferences. She opens up the Estimate solution template, Estimate.xlt,to begin the estimate process.

  Figure 1. Excel application with actions pane

  

• When Excel starts, the menu structure functionality is limited to those operations that pertain to the Estimate solution.

• Karen starts by selecting John's name in the Customer Name list box. Then she types his available budget into the Budget text box.

• Then she selects the cabinet options from the actions pane by selecting John's desired Cabinet Style, Cabinet Type, Hinge Type, Pull Type, and Quantity.

• She adds each cabinet configuration to the Bill of Materials (BOM) by clicking Add Cabinet.

The Budget Tracking Chart displays the status of the estimate. The solution indicates whether the estimate is still within budget or whether it exceeds the allocated budget amount.

Because John is a preferred customer, Karen then applies a discount to his cabinet by editing the percent values in the Discount column of the BOM. She can also adjust individual line-item quantities or set them to zero ("0") to remain within budget. The worksheet automatically updates the remaining budget and chart to reflect the changes to the cabinet costs.

After Karen finishes adding all of John's cabinet requests to the BOM, she clicks Submit in the actions pane; this stores the estimate information in the database and opens Estimate.dot in Word.

The Estimate.dot template reads user, customer, and order information from the database and fills in a table with cabinet cost information.

Karen reviews the estimate data and adds a few comments about John's order. The BOM section of the estimate is protected to prevent changes to the generated estimate pricing.

Now she can save the Estimate report, print it, and fax or mail it to the customer, or she can send it as a document attached to an e-mail message.

Office Templates

Two Microsoft Office templates are the basis of this application: Estimate.xltandEstimate.dot. Estimate.xlt is the Excel template used for creating a new .xls file when entering a customer's order. Estimate.dot is the Word template used when creating an Estimate report from the information previously entered into the .xls file. The customer receives the Estimate report as an order confirmation.

Estimate.xlt

The Excel template, Estimate.xlt, is used for estimating the cost of the order. Sheet1 contains a blank estimate entry form. The behavior of the worksheet changes when you are creating a new estimate, entering data, or submitting an order.

There are two major components of the Estimate.xlt file: 1) the Estimate Entry form and 2) the actions pane. The Estimate Entry form has three major sections: Customer Information, Budget Tracking Chart, and Bill of Materials (BOM). The sales representative selects the customer from the customer list and then enters the customer's budget amount. The Budget Tracking Chart monitors the use of the customer's budget. The sales representative adds cabinets to the order using choices presented in the actions pane. As the user adds cabinets to the order, their description and cost are placed in the Bill of Materials section. After the order is complete, the sales representative submits the order by clicking Submit in the actions pane, which then opens Estimate.dot in Word.

Estimate.dot

An Estimate report is created in Word using the information entered in the Excel worksheet. The Estimate.dot template automatically creates a table containing cabinet cost information by reading customer and order information from the database. The sales representative can edit this report before printing and mailing it to the customer or attaching it to an e-mail message.

Installing the Estimate Solution

Software Requirements

To run the Estimate solution, you must have the following software installed, in the following order:

1. Microsoft Windows 2000 or later

2. Microsoft Office Professional 2003, the complete installation

3. or these two stand-alone Office applications:

   • Microsoft Office Excel 2003

   • Microsoft Office Word 2003

4. Microsoft Visual Studio 2005 Tools for the Microsoft Office System

5. Microsoft SQL Server 2000, Microsoft SQL Server 2000 Desktop Engine (MSDE 2000), Microsoft SQL Server 2005, or Microsoft SQL Server 2005 Express

Copying the Sample

To download the sample application that accompanies this article, click OfficeVSTOEstimateSample.msi at the top of this article. In the Microsoft Download Center, click Download and follow the instructions to run the installation.

The default installation location for the sample files is the directory C:\Documents and Settings\username\My Documents\Visual Studio 2005\Projects\Estimate Sample. The default location for the Microsoft Visual Basic solution is the subfolder \Visual Basic, and the default location for the C# solution is \C#.

Creating the Database and Building the Sample

1. Open the Database solution in Microsoft Visual Studio 2005. By default, Database.sln is in C:\Documents and Settings\username\My Documents\Visual Studio 2005\Projects\Estimate Sample\Database.

   Note

   If a message appears stating "Unable to connect to a database specified in the Solution. Do you want to modify the Database connection properties?" click No. If no message appears, proceed to step 2.

2. In Server Explorer, right-click Data Connections and then click Add Connection.

3. In the Choose a Data Source dialog box, select Microsoft SQL Server and click Continue.

4. In the Add Connection dialog box, select or type the name of the computer that is running SQL Server in the Server name selection box. Specify the information to log on to that computer. Leave the Select or enter a database name value blank. Click Test Connection to confirm that the server name and logon information are valid. Remember these settings because you will use them in step 6 below. Click OK on the Add Connection dialog box to close the dialog.

   Figure 2. Add Connection dialog box

   

5. In Solution Explorer, expand the Create Scripts folder. Right-click Create Scripts.cmd and select Run.

6. In the SQL Server Login dialog (shown in Figure 3), type the values used in step 4 in the Server and security-related text boxes (either select Use Windows NT Integrated Security or type Login and Password values). In the Database field, type EstimatesSampleV2 — this is the name of the database that will be created by the script. Click OK to run the script.

   Note

   Entering a different database name causes the script to fail. Failure during script execution results in the Command Prompt window displaying a message and pausing. If an error does occur, execute steps 5 and 6 again.

   If you do not know the server or security information from earlier setup, you can review it by expanding the Connections node in Server Explorer. Right-click the connection you want to review, and then click Modify Connection.

   Figure 3. SQL Server Login dialog box

   

7. The script runs successfully after displaying the Command Prompt window for 5–10 seconds and then automatically closing. After successful execution of the script, the EstimatesSampleV2 database contains the required tables and stored procedures with tables populated with default values. You can now close the Database solution.

8. Open the Estimate solution in either Visual Basic or C#.

   Note

   If the message "Do you want to allow access to the Visual Basic for Applications project system?" appears, click Yes to continue.

9. If your database is installed on a separate instance other than the root server, you must modify the database connection strings to ensure that the code can connect to the database:

   • In Solution Explorer, right-click the EstimateExcelTemplate project and click Properties to display the screen in Figure 4.

   Figure 4. Project settings

   

• Click the Settings tab.

• Modify the EstimatesSampleConnection value to point to the correct server.

  Note

  By default, both connection values are "Server=localhost;Integrated Security=True;Database=EstimatesSampleV2".

• Close the Properties window and save your changes.

• Click the Settings tab.

• Modify the EstimatesSampleConnection value to point to the correct server.

  Note

  By default, both connection values are "Server=localhost;Integrated Security=True;Database=EstimatesSampleV2".

• Close the Properties window and save your changes.

1. Repeat the previous step to modify the database connection string for the EstimateWordReport project.

2. On the Debug menu, click Start Debugging to run the solution in Debug mode.

   Note

   If you did not choose the installation option "Complete" when installing Office 2003, you may receive compilation errors related to not being able to find the Microsoft.Office.Core or Microsoft.Office.Interop components. One way to correct this problem is to create blank Excel and Word applications, which install the components before running the new applications. Another approach is to select the "Advanced" option during Office installation and specify .NET Programmability Support for Excel and Word, Forms 2.0 .NET Programmability Support, and Microsoft Graph .NET Programmability Support for Visual Studio Tools for Office.

To run the application outside of the Visual Studio development environment, you must open the Estimate.xlt file in the EstimateExcelTemplate\bin folder (not the EstimateExcelTemplate folder). The .xlt requires the supporting .dll file to be in the same folder in order to execute.

#Architectural Considerations in Designing the Solution

There are several factors to consider when assessing the architectural options for an Office solution. These include evaluating the strengths and weaknesses of each development platform as related to the requirements, the budgetary constraints, and the time frame of the project. The architectural considerations for choosing the platform for the Estimate solution are outlined in this section.

Why Office?

We could have created the Estimate solution as a custom Microsoft Windows Forms or Web Forms application instead of an Office solution. Why was the decision made to use Office? The key considerations were as follows:

• Simplicity. The Estimate solution is a relatively simple application. The functionality and the interface of an Office solution provide a good platform for this application.

• User Familiarity. The users are familiar and comfortable with the Office environment. A custom interface (for a Windows Forms or Web Forms application) would result in additional user education time and increased risk that users would not like or use the new tool.

• Office Functionality. The existing functionality of Office can accelerate development by taking advantage of tables, charts, the actions pane, protection, and computational functions.

These factors made Office a logical choice for this application.

Why Visual Studio Tools for Office?

After deciding to use Office applications in this solution, the next step was comparing Visual Basic for Applications and Visual Studio Tools for Office. There were considerations specific to the Estimate solution:

• .NET Advantages. Visual Studio Tools for Office uses the common language runtime (CLR) of the Microsoft .NET Framework. This simplifies common development functions such as user controls, XML interaction, and data binding, and creates an extensible platform. Visual Basic for Applications does not use .NET Framework–based code.

• Macro Recording. Visual Basic for Applications provides the ability to record steps taken by a user and then customize them. This does not exist in Visual Studio Tools for Office. The Estimate solution did not require macro-like functionality, so this was a minor consideration.

• Development Tools. The development tools provided as part of Visual Studio provide a richer and easier-to-use environment for developers over the comparable tools available with Office for Visual Basic for Applications. This toolset makes Office development straightforward for developers experienced with Visual Studio and the .NET Framework, because they can use the familiar coding and debugging environment it provides.

• Object-Oriented Development. Object-level development enables a more structured, reusable, and maintainable code base.

• Globalization. The tools for resource file implementation provide a basis for supporting the Office solution across different languages and cultures.

• The Security Model. Both Visual Basic for Applications and Visual Studio Tools for Office can use the same security model within Office. Visual Studio Tools for Office additionally offers the security features that are built into the .NET Framework.

• Distribution and Support. Visual Basic for Applications does not require any additional installed components outside of Office. Visual Studio Tools for Office requires a document-specific .dll file, the .NET Framework 2.0, and the Visual Studio Tools for Office runtime to be installed on client computers. While the initial installation of a Visual Studio Tools for Office solution may be more involved, long-term application maintenance is much simpler.

Based on the requirements for the Estimate solution, the considerations strongly favored Visual Studio Tools for Office over Visual Basic for Applications as the development environment for implementation.

Developing the Estimate Solution

Development of the Estimate solution application focused on delivering a solution that met the specified business scenarios and provided a sample that demonstrated many of the capabilities of Visual Studio Tools for Office. Because the actual primary end user of this application is a software developer rather than a custom cabinet distributor, a few code embellishments were introduced as demonstrations of a Visual Studio Tools for Office functionality or capability. Nevertheless, much of the code is streamlined to meet the business needs in a "real-world" implementation scenario.

Overview of the Excel Estimate.xlt

The Excel portion of the sample uses an Excel template. The new template creates an .xls workbook application when it runs. The Excel template was created by dragging new controls onto the template in the Visual Studio integrated development environment (IDE). Figure 5 shows a NamedRange control that is about to be dragged to the template.

Figure 5. Excel template editing in IDE with drag and drop

The solution template includes two worksheets. The main worksheet, Sheet1, is open when the application starts. It provides interactive features for the user and displays the BOM and the budget chart. The chartData worksheet is hidden at run time from the user and holds the remaining budget value and a running list of added cabinets (names and costs). The running list of cabinets is the source for the budget chart on Sheet1.

As part of the template, the actions pane functions as a data entry form for adding cabinets to the BOM. Figure 6 shows the actions pane in the application at run time. The alternative approaches of using a modal Windows Form or adding the controls directly on the sheet were not as desirable as the actions pane solution. The actions pane provides a consistent, integrated Excel interface. In comparison, a Windows Form appears as a separate disconnected window. Additionally, the "always available" or "docked" nature of the actions pane enables the user to scroll a longer BOM while retaining the ability to add another cabinet or submit the current estimate. These advantages make the actions pane a desirable user interface component when designing Excel solutions.

Figure 6. Excel application with actions pane

Overview of Execution States

The execution states described here are from a user-interaction point of view. They are not application state objects controlled by a state manager.

New Estimate State

When the template first opens, it creates a spreadsheet in the New Estimate State. The actions pane is visible but is not active. When the user enters a valid budget value, the application switches to the New State actions pane. This state is also entered if a new estimate is created by opening the File menu and selecting New Estimate.

Actions Pane New State

The actions pane enables the user to add cabinets to the BOM. The Submit button is not available until the user adds a new cabinet. After the user adds a new cabinet, the application enters the Data Entered State.

Data Entered State

All controls are available after the user enters valid data. The Submit button in the actions pane is enabled and editing of grid data in the BOM list is allowed.

BOM Append State

After the user adds the initial cabinet, the BOM Listobject is set to append additional cabinets to the end of the list through an XML import.

Toolbars and Menus

During initialization of the Excel worksheet, the toolbars and menus are customized for the Estimate solution; the solution contains the custom CommandBarManager class for this purpose. The CommandBarManager class provides an abstraction layer so that you can customize or restore the command bars without needing to know any of the details about how that is done.

In ThisWorkbook, a new instance of the CommandBarManager class is instantiated in the Startup event handler. The command bar and menu customizations occur in ThisWorkbook_ActivateEvent when the workbook is activated, and in ThisWorkbook_Deactivate when the workbook is deactivated. Command bar and menu customization is done during workbook activation, as opposed to startup, to eliminate conflicting or redundant customizations in the case of multiple solutions being run simultaneously in the same instance of Excel.

Private Sub ThisWorkbook_ActivateEvent() Handles Me.ActivateEvent
    Me.commandBarManager.CustomizeCommandBars()
End Sub
Private Sub ThisWorkbook_Deactivate() Handles Me.Deactivate
    Me.commandBarManager.RestoreCommandBars()
End Sub

It is the CustomizeCommandBars member of the CommandBarManager class that removes any command bars and menus that are not required for the template. The project contains an embedded string resource named CommandBarData that serves as an XML blueprint for describing which command bars and menus are needed.

Figure 7. The CommandBarData string resource

Prior to customizing command bars and menus for the template solution, CustomizeCommandBars will save the current visible state of command bars and menus so that they can be properly restored when RestoreCommandBars is called.

Public Sub CustomizeCommandBars()

    ' If there are any custom menu items,
    ' remove them now.
    Me.RemoveCustomMenuItems()

    Dim allowedCommandBars As ArrayList = _
        Me.commandBarData.GetAllowedCommandBars()

    ' Save the current state of any visible command bars and then
    ' set their visible state to false.
    Dim commandBar As Office.CommandBar
    For Each commandBar In Me.commandbars
        Dim hide As Boolean = _
            allowedCommandBars.BinarySearch(commandBar.Name) < 0
        If commandBar.Type = Office.MsoBarType.msoBarTypeNormal Then
            Me.CustomizeCommandBar(commandBar, hide)
        Else
            Me.CustomizeMenuBar(commandBar, hide)
        End If
    Next

    ' Disable the Help Ask a Question box.
    Me.isHelpDropDownDisabled = Me.commandbars.DisableAskAQuestionDropdown
    commandbars.DisableAskAQuestionDropdown = True

End Sub

Bill of Materials (BOM) and XML Schema Mapping

The Bill of Materials is shown in a List object that is updated with new items as each new cabinet is added to the order. Using the Excel editor in the Visual Studio 2005 IDE, the BOMTable_Map were loaded into the XML Source Map feature of the Excel template. The XML nodes were then dragged onto the List objectto create the binding. When the addCabinetButton on the actions pane is clicked, the Bill of Materials data set is filled with data returned from a SQL procedure and then an event named NewBom is raised. Sheet1 handles the NewBom event to import and map the new XML data to the existing List object on the worksheet. The chart is also updated at this time to reflect the added data.

' Update the Bill of Materials when a new cabinet is added.
Private Sub UpdateBom(ByVal dsBom As DataSet)
    Try
        Me.UnprotectSheet()
        Me.ImportBom(dsBom)
        FormatBomListObjectCells()

        ' Determine which rows were just added and group them together.
        Dim rowsAdded As Integer = _
            dsBom.Tables(My.Resources.BomItem).Rows.Count
        GroupListObjectRows(rowsAdded)
        Me.cabinetCount += 1

        ' Update the list of entries on the budget chart with the
        ' cabinet that was just added to the Bill of Materials.
        Me.UpdateBudgetTrackingChart(rowsAdded)
    Catch ex As System.Data.SqlClient.SqlException
        MessageBox.Show(ex.Message, _
            My.Resources.ErrorCaption, _
            MessageBoxButtons.OK, MessageBoxIcon.Error, _
            MessageBoxDefaultButton.Button1, _
            utils.GetMessageBoxOptions())
    Finally
        Me.ProtectSheet()
    End Try
End Sub

Three rows (cabinet, hinge, and pull) are added to the List objectfor each additional cabinet. The borders are set and the formulas are filled down to include the new rows. The range of the new rows is then grouped together so it can be collapsed or expanded.

Protection is set up to prevent deletion of rows in the BOM list and to allow only limited editing access for modification of the quantity and discount values. By default, all cells on a worksheet are locked. When a worksheet is protected, cells in a locked state cannot be modified or deleted. To allow editing, the quantity and discount cells were unlocked. To unlock the cells manually, in the template, in the Format Cells section, click the Protection tab and clear the Locked check box.

Actions Pane

To create a custom actions pane, a new user control, CabinetSelectionPane, was added to the project. This provided a rich interface to design the control. After the design was completed, the user control was added to the actions pane and initialized in the Sheet1_Startup event.

Private WithEvents cabinetSelectionPane As CabinetSelectionPane

Private Sub Sheet1_Startup(ByVal sender As Object, _
    ByVal e As System.EventArgs) Handles Me.Startup
    . . . 
    Me.InitializeCabinetSelectionPane()
End Sub

Private Sub InitializeCabinetSelectionPane()
    Me.cabinetSelectionPane = New CabinetSelectionPane()
    ' Add the custom user control to the actions pane.
    Globals.ThisWorkbook.ActionsPane.Controls.Add(cabinetSelectionPane)
End Sub 

Note that you could have added the control to the actions pane in the Startup event of the workbook; however, we defer this activity until the worksheet's Startup event instead to guarantee that both the workbook and worksheet classes have been constructed because the actions pane control will subscribe to worksheet events in its constructor.

For this sample, only a single user control was used in the actions pane. However, you can add multiple user controls to delineate functionality and enhance the user interface.

Most interactions between Sheet1 and the actions pane are managed through the events raised by the budgetTextBox control on Sheet1 and the CabinetSelectionPane control. Sheet1 and the actions pane essentially have only two circumstances in which they need to interact with one another:

• When the budgetTextBox value changes, the enabled state of controls on the actions pane may require changing.

• When the addCabinetButton is clicked, data is added to the worksheet for the new cabinet selection. As previously mentioned, this all happens through the raising of the NewBom event.

Sheet1 exposes a custom event named DataEnteredStateChanged; this event is raised when the value in budgetTextBox is changed. The CabinetSelectionPane control subscribes to this DataEnteredStateChanged event in its constructor so that it can enable or disable controls when the event is raised.

Public Sub New()
    . . . 
    AddHandler Globals.Sheet1.ReadyStateChanged, _
        AddressOf Sheet1_DataEnteredStateChanged
End Sub

Private Sub Sheet1_DataEnteredStateChanged( _
    ByVal inDataEnteredState As Boolean)
    EnableControls(inDataEnteredState)
End Sub

Submit (submitButton_Click) includes multiple steps before showing the Estimate report in Word. If the estimate is over budget, a message box asks the user if they want to continue anyway. If the user continues or the estimate is not over budget, the SubmitBOMListData subroutine steps through the remaining functionality. The estimate ID and record are created or updated in the database for the specified customer in SaveEstimateNumber by using the QuoteTableAdapter data adapter. The budget is only used on the worksheet and is not saved to the database. The user ID for the estimate is always set to User1.

After the estimate record is saved, the data on the worksheet for the BOM is saved to the database by exporting the mapped XML on the worksheet to an XML document, loading the XML document into the BOMTable dataset, and then using the OrderDetailAdapter data adapter to update the database based on the BOMTable dataset.

Private Sub SubmitBOMListData()
    . . .
    ' Pull the XML data from the Bill of Materials table and
    ' submit it to the database.
    Dim bomMap As Excel.XmlMap = _
       Globals.ThisWorkbook.XmlMaps("BOMTable_Map")
    Dim bomXML As String = ""
    bomMap.ExportXml(bomXML)
    SaveEstimateDetails(bomXML)
    . . .
End Sub

Private Sub SaveEstimateDetails(ByVal bomXML As String)
    Dim orderInfo As New BOMTable
    . . . 
    ' Read the XML data into a dataset.
    Dim reader As New System.IO.StringReader(bomXML)
    orderInfo.ReadXml(reader)
    . . .
End Sub

After all the data is saved to the database, the Estimate workbook is saved to the file system and then the Word template Estimate.dot is opened by Automation. As the code below shows, a new document is created from the Word template, the customer ID and quote number variables on the Word template are set, and then the document is activated.

' Start a new instance of Word with a new instance of the
' template loaded.
Private Sub LaunchWordWithTemplate()
    Dim wordTemplateFile As System.IO.FileInfo = New System.IO.FileInfo( _
        Me.wordTemplatePath)
    If (wordTemplateFile.Exists) Then
        Dim wordApplication As New Word.Application
        Dim wordTemplate As Word.Document
        Try
            wordApplication.Visible = False
            wordTemplate = WordNewTemplateInstance(wordApplication)
        Catch
            ' Since Word isn't visible, shut it down so it doesn't
            ' remain in memory.
            wordApplication.Quit(False)
            Throw
        End Try
        wordApplication.Visible = True
        wordTemplate.Activate()
    Else
        Throw New System.IO.FileNotFoundException(Me.wordTemplatePath _
                + My.Resources.WordTemplateError)
    End If
End Sub

' Create a new instance of the template in the specified instance of
' Word.
Private Function WordNewTemplateInstance( _
    ByVal wordApplication As Word.Application) As Word.Document
    Dim wordTemplate As Word.Document = _
       wordApplication.Documents.Add(Me.wordTemplatePath)
    ' Add variables that the Word template uses to 
    ' populate its data.
    wordTemplate.Variables.Add(My.Resources.CustomerId, _
       Globals.Sheet1.Customer_Name)
    wordTemplate.Variables.Add(My.Resources.QuoteNumber, _
       Me.estimateNumber)
    Return wordTemplate
End Function

Budget Tracking Chart

The budget tracking chart is set up as an auto-scaling stacked bar chart so that the costs of each cabinet with hardware can be seen together. The MajorUnitIsAuto property is set to true so that Excel properly scales the major units of the chart. The source data for the chart is stored on the chartData worksheet, which is hidden at run time. The solution updates the data on the chartData worksheet each time the BOM list data is edited or a new cabinet is added. The Calculate event ensures that the chart range is set correctly.

Named Range Controls

Named ranges are used on Sheet1 to store status or label values. The benefit to the sample is that a cell or group of cells can be referenced by name instead of by absolute location. This provides flexibility for location changes as well as an easy-to-read name describing the data. The named ranges used in the sample are described in the table below.

Table 1. Named ranges used in the Estimate solution

Named Range	Description
Sheet1.budgetStatusValue	Remaining budget amount, shown in dollars Displayed next to the budget amount
Sheet1.budgetStatusText	The static label of "Remaining Budget" for budgetStatusValue Not visible until a budget amount is entered
Sheet1.customerName	The location on the header for the BOM table where the selected customer name is displayed
Sheet1.overBudgetWarning	Static "Warning! Over Budget" label Shown next to the budget remaining amount if the budget has been exceeded

Overview of the Word Estimate.dot

Similar to Excel, the Word portion of the sample uses a template so that each report has a consistent look and can be saved in its own document.

Creating the Estimate Report

The baseline for the Estimate report was created by adding and editing text and creating tables in a Word template. The template was then mapped to an XML schema, Quote.xsd. Nodes from the schema were added to the template by dragging the elements by name from the XML Structure view onto the template in the resulting customer, sales representative, and BOM sections of the document. Figure 7 shows the Word XML elements that were added to the document.

Figure 8. Word template editing in IDE with XML structure

The Word template protects the BOM table while allowing editing of the header information for Contoso, customer, and notes. When a user views the report, the editable sections may be shown highlighted in yellow. This is due to a Word configuration setting to "highlight regions I can edit." This setting is available in the task pane for any protected document.

The Word Estimate template has two user scenarios: the template may be started by Automation from the Excel solution, or a new document may be created directly from the template. In the case where the Word solution is started by Excel, the user ID and quote number are stored as document variables and are used to retrieve a dataset of the estimate details from the SQL database for populating the document. In the case where a new document based on the template is created in Word directly, there are no document variables for the user ID or the quote number stored in the new document so the document shows a dialog box to collect this information. This is all handled in the PopulateDocument member of ThisDocument.

Private Sub PopulateDocument()
    Dim quoteID As Integer
    Dim customerID As Integer
    Dim estimateSelector As EstimateSelector = _
       New EstimateSelector(Me.userId)

    Try
        ' Get the requested estimate from variable objects
        ' if they have been set.
        If (Not Me.GetRequestedEstimateFromVariables( _
          quoteID, customerID)) Then
            ' If the variables have not been set,
            ' display the EstimateSelector so that
            ' the user can choose the estimate.
            If (estimateSelector.ShowDialog() = DialogResult.Cancel) Then
                Return
            End If

            quoteID = estimateSelector.SelectedQuoteID
            customerID = estimateSelector.SelectedCustomerID
        End If

        Me.PopulateDocument(quoteID, customerID, _
        estimateSelector.CustomerEstimateDataSet)
    Finally
        estimateSelector.Dispose()
    End Try
End Sub

Understanding Globalization and Localization Issues

To create the Estimate solution so that it addresses localization and globalization issues, many issues were considered.

Localization of the solution involves changing displayed messages to the target language. To facilitate this, resources were created for the strings that are displayed to the end user. The translator updating these resources must be familiar with the application, the English language, and the target language. You can update these localization resources under the Resources tab of the Project Settings dialog.

For more information about globalization and localization of Office solutions, see Globalization and Localization of Office Solutions.

Testing, testing, testing. After you make changes for a specific locale, the only way to know that the application works is to test it in the target environment. This includes both the operating system and Office. You must verify the application functionality along with localized messages and numeric and date input formats.

Deploying the Estimate Solution

You can deploy Visual Studio Tools for Office solutions with the Publish Wizard, which copies the document, assemblies, and manifests to the publish location.

Granting Permissions to Assemblies

Before an Office solution can run, the target computer's .NET Framework security policy must be modified. Under the most common deployment scenario, the Administrator or a Power User grants Full Trust (partial trust will not work) rights on the target user's computer to the managed code extensions that run as part of the solution. The Estimate solution must be set to Full Trust in the .NET Framework 2.0 configuration. This can be done by using either of the following:

• MSI installer. Build a Windows Installer (.msi) file using the .NET Framework Configuration tool.

• caspol.exe. Modify policy directly by scripting against the Code Access Security Policy tool (caspol.exe).

For more information about deployment and security considerations for Visual Studio Tools for Office, see Visual Studio 2005 Tools for Office Online Documentation.

Code Security

The sample code referred to in this article is intended for instructional purposes, and should not be deployed in production solutions without modifications. In particular, you must take code security into greater consideration.

To illustrate the simplicity of this sample solution, a list of potential threats has been identified using the threat modeling process and tools described in the Threat Modeling section of the Microsoft Security Developer Center.

The following are some examples, but not all, of the identified threats that should be taken into consideration before expanding or deploying this solution:

• Database Spoofed. If the database is compromised it could result in invalid data being displayed or the application not functioning properly. This threat can be mitigated by protecting the database, by limiting user access to the database. Connection strings are currently stored in application settings. Using Windows Authentication rather than SQL Server Authentication is the recommended approach. In SQL Server Authentication, user names and passwords are stored in plain text, which can be a security threat. Windows Authentication connection strings do not contain logon information, and centralized account maintenance of Windows accounts is well-defined and supported. Windows Authentication connections do not send credentials over the network, so they are much more secure.

• Visual Studio Tools for Office Assemblies Replaced. If the Visual Studio Tools for Office assemblies are replaced with other assemblies, this could result in the application behavior being altered to compromise the system. This threat can be mitigated by signing the code, using strong name conventions, or providing hash evidence. This sample does not currently implement these precautions, but they are strongly recommended before deploying an application to a production environment.

For more information about code security, please visit the Microsoft Security Developer Center.

About the Authors

Kevin Schultz is the custom development operations manager for NuSoft Solutions. He has created multiple desktop and client server applications for retail and commercial user bases over the past 10 years. Along with Business Intelligence (data warehousing) and embedded platform (Pocket PC) experience, Visual Studio Tools for Office provides another platform that enables Kevin to bridge the gap between traditional desktop and Web development and the mobility demands of today.

Kevin Schuler manages the Regional Director Program for Microsoft Corporation. Before joining Microsoft, he was an active member of the Regional Director Program for nine years and a member of the Microsoft .NET Partner Advisory council. He is an author of books and white papers about Internet security and various Microsoft products.

Pam Davis is a lead developer at NuSoft Solutions and a contributor to this sample.

Aisha Khurshid is a lead developer at NuSoft Solutions and a contributor to this sample.

Lionel Rouvillois is a project manager at NuSoft Solutions and a contributor to this sample.

Additional Resources

See the following resources for more information:

Visual Studio Tools for Office

• Microsoft Visual Studio Developer Center

• Online Documentation: Visual Studio Tools for Office

• Microsoft Office Developer Center: Visual Studio Tools for Office Developer Portal

• Blog: Microsoft Visual Studio 2005 Tools for the Microsoft Office System

• Forum: Visual Studio Tools for Office

Code Security

• Writing Secure Code

• Microsoft Security Developer Center

• Protect Your PC