Skip to main content
Building the Contoso Auto Sales Office Business Application Part 3 – Generate Financing Information

Robert Green
MCW Technologies

Download the code for this tutorial on Code Gallery

Read Part 1: Building the Contoso Auto Sales Office Business Application Part 1 - Scheduling Customer Appointments

Read Part 2: Building the Contoso Auto Sales Office Business Application Part 2 - Generating Automobile Quotes

Introduction

Contoso Auto Sales is a fictitious automobile dealer specializing in high-end automobiles. Contoso has computer systems to talk to suppliers, to generate an invoice when a customer purchases an automobile, to bill for service visits and to pay employees. It wants a system to manage the day-to-day interaction with customers and improve its level of pre-sales customer service.

Contoso Auto would like a solution based on Office 2007 that addresses the following scenarios:

  • The sales manager needs to contact customers who have requested an appointment by calling Contoso or visiting the Web site. She wants to send a mail acknowledging the appointment request and then schedule appointments using Outlook.
  • Sales consultants want to use Word to create and print price quotes for automobiles, reflecting available options selected by a customer.
  • The financing manager wants to use Excel to generate financing information for customers with pending quotes. He wants the ability to generate this information interactively, but also wants it created automatically when the sales consultant generates a quote.
  • The sales manager wants to use Excel to run reports showing quotes by manufacturer, model, customer and employee. She wants to view reports offline on her laptop and wants the reports automatically updated with up to date information.

In the tutorials in the Building the Contoso Auto Sales Office Business Application series, you will use Visual Studio 2008 to create an Office Business Application that address each of the scenarios listed above, using Outlook 2007, Word 2007 and Excel 2007. The tutorials contain both Visual Basic and C# code.

Exercise 1: Create the Excel Financing Add-in Project

In the second tutorial in this series (Generate Automobile Quotes) you saw how to build a Word solution that generates quotes. Once a customer has a quote, he or she knows how much a car with a particular set of options will cost. The next question will often be how much will it cost to finance the car? Excel is a perfect place for this type of calculation.

Contoso uses Excel to generate financing information. The financing manager meets with a customer. The customer has quotes pending for one or more vehicles and wants to see financing options for each automobile. The financing manager generates this information for each quote. This can be a laborious process as the financing manager enters information into the spreadsheet. All of the information is contained in the quote, so Contoso would like an automated solution that doesn’t require manual typing of existing information.

In this tutorial, you will create an Excel 2007 financing solution. The Excel solution will support the following:

  • The ability of the financing manager to select a quote from the list of quotes for a customer.
  • The ability to retrieve the quote information from the database and populate an Excel workbook.
  • The ability to save the finance information so that it can be easily viewed and reprinted.

The solution you will build will enable the financing manager to generate financing information. You will base this solution on the Automobile Financing workbook. This workbook contains two worksheets.

The Cover Sheet worksheet (see Figure 1) will display information on the quote, including manufacturer and model, customer, vehicle cost, options and total cost. The Automobile Financing worksheet (see Figure 2) will display information on the loan, including loan amount, interest rate, monthly payment and more. In addition, it will display each month’s payment information.

Figure1.jpg

Figure 1. The solution displays quote information on this worksheet.

Figure2.jpg

Figure 2. The solution displays financing information on this worksheet.

The add-in you will create will work with data that resides in the ContosoAuto SQL Server database. Refer to the first tutorial in this series for information on how to create this database.

To get started, in Visual Studio 2008 select File > New > Project to display the New Project dialog box. In the list of project types, expand the Office node. Select Version2007, displaying the list of templates. In the Templates pane, select Excel 2007 Workbook. Name your project AutomobileFinancing and select an appropriate folder for the project. Click OK to create the project. Visual Studio displays the Visual Studio Tools for Office Project Wizard (see Figure 3).

Figure3.jpg

Figure 3. Visual Studio displays this wizard when you create the solution.

You can base the add-in on a new or existing workbook. Select Copy an existing document and click Browse. Navigate to the folder where you downloaded this tutorial’s sample project. Navigate to the Starter Documents and Workbooks folder. Select Automobile Financing.xlsx and click Open. This returns you to the wizard. Click OK. Click OK if Visual Studio displays the message shown in Figure 4.

Figure4.jpg

Figure 4. Visual Studio displays this message when you create the solution.

In the Server Explorer, expand the Data Connections node. If you do not have a connection to the ContosoAuto database, right-click Data Connections and select Add Connection to display the Add Connection dialog box. If the Data source does not display Microsoft SQL Server (SqlClient), click Change. This displays the Change Data Source dialog box. Select Microsoft SQL Server (SqlClient) and click OK.

In the Add Connection dialog box, enter your computer name (if you are using SQL Server) or .\SQLEXPRESS (if you are using SQL Server Express) in the Server name text box. Select ContosoAuto from the database drop-down list. Click OK to close the Add Connection dialog box.

In the Server Explorer, expand the ContosoAuto node. Expand the Tables node to see the tables in the database (see Figure 5).

Figure5.jpg

Figure 5. The ContosoAuto database contains these tables.

The Quotes and QuotePackages tables (see Figure 6) contain the information that comprises existing quotes. The solution will use this data when generating financing information.

Figure6.jpg

Figure 6. The Quotes and QuotePackages tables contain information for existing quotes.

The Excel solution you are building in this tutorial needs a connection to the ContosoAuto database. Select Project > AutomobileFinancing Properties. Select the Settings tab. To add a new setting, enter ContosoAutoConnectionString in the Name column. Select (Connection string) from the Type drop-down list.

Click the ellipsis in the Value text box. This displays the Connection Properties dialog box. Create a connection to the ContosoAuto database. Click OK to close the Connection Properties dialog box. Close the Project Designer, saving your changes.

You will use a DataSet to communicate with the ContosoAuto database. Rather than create the DataSet from scratch, you will use an existing version of it. Select Project > Add Existing Item to display the Open File dialog box. Navigate to the folder where you downloaded this tutorial’s sample project. Navigate to the DataSets\AutoFinancing folder. Then navigate to the VB or CS folder, depending on the language you are using. Select ContosoAutoDataSet.xsd and click OK to add the DataSet to the project.

In the Solution Explorer, double-click ContosoAutoDataSet.xsd to open the DataSet Designer (see Figure 7). Take a few minutes to familiarize yourself with the tables and TableAdapters.

Figure7.jpg

Figure 7. The add-in uses this DataSet to work with the data in the ContosoAuto database.

Close the DataSet Designer. Save your changes. Select Build > Build Solution and verify that the project compiles correctly.

Exercise 2: Generate Financing Information Interactively

In this exercise, you will provide Contoso’s finance manager with the ability to interactively generate financing information for customers. The quotes will be based on the Automobile Financing Excel workbook, which contains a Cover Sheet worksheet and an Automobile Financing worksheet.

Data Bind Financing Information

The Cover Sheet worksheet will display the following information:

  • Vehicle name
  • Customer name
  • List price of the automobile
  • Selected options and their prices
  • Total cost of the automobile

The solution will retrieve this information from the Customers, Quotes and QuotePackages tables in the database.

The finance manager needs to enter the following on the financing worksheet to generate financing information:

  • Total cost of the automobile
  • Down payment amount
  • Loan amount
  • Interest rate
  • Length of the loan
  • Start date of the loan

The solution will retrieve the vehicle cost from the quote. The finance manager will manually enter the interest rate. The solution will suggest default values for the length and start date of the loan.

In the next step, you will add data bound controls to the Cover Sheet worksheet. These controls will contain and display the information for a particular quote.

Double-click Sheet1 in the Solution Explorer to display the Cover Sheet worksheet. Select Data > Show Data Sources to display the Data Sources window. Expand the QuoteHeaders node.

In the previous tutorial, you saw that you could drag and drop table columns from the Data Sources window onto a Word document and create data bound content controls. In Excel you can also create data bound controls on spreadsheets by dragging from the Data Sources window.

The icon next to each table and column in the Data Sources window identifies the default control that you will create when you drag the table or column onto an Excel worksheet. By default, dragging a column from the Data Sources window creates a NamedRange control and dragging a table creates a ListObject control.

Drag the VehicleFullName column from the QuoteHeaders node in the Data Sources window to the Excel worksheet. Drop the column in cell C2 (see Figure 8). This creates a named range bound to that column (see Figure 9). Visual Studio also adds data controls, which you can see in the Component Tray.

Figure8.jpg

Figure 8. Drag the VehicleFullName column from the Data Sources window to the Excel worksheet.

Figure9.jpg

Figure 9. The VehicleFullName named range is bound to the VehicleFullName column in the QuoteHeaders table.

Drag the CustomerFullName column from the Data Sources window to the Excel worksheet. Drop the column in cell C4.

Drag the VehicleQuotePrice column from the Data Sources window to cell C7.

Drag the TotalQuotePrice column from the Data Sources window to cell C9.

Drag the QuoteDetails table from the Data Sources window to cell B12. Make sure you drag the QuoteDetails table that appears as a column of the QuoteHeaders table. This adds a List object to the worksheet.

Click in cell B12 and change the column-heading name to Package. Click in cell D12 and change the column-heading name to Cost.

Right click on the QuoteID column (cell E12) and select Delete > Table Columns. Right click the PackageID column (cell E12) and select Delete > Table Columns.

Right click on cell D13 and select Format Cells from the menu. In the Number tab of the Format Cells dialog select Currency from the Category list. Select 2 from the decimal places list. In the Alignment tab, select Right (indent) from the Horizontal drop-down list. Click OK.

In the Properties window, set the ShowTotals property of QuoteDetailsListObject to True. Click in cell D14. Click the arrow next to the for the Cost column in the total row (cell D14). Select Sum (see Figure 10). The worksheet should look like Figure 11.

Figure10.jpg

Figure 10. Select Sum to display the total cost of the selected options.

Figure11.jpg

Figure 11. The worksheet should look like this.

Save your changes. Select Build > Build Solution and verify that the project compiles correctly. Press F5 to run the application. You should see the Cover Sheet worksheet filled in with quote information for the first quote in the database that you entered in part 2 of the tutorial series. (see Figure 12). Exit Excel, without saving your changes, and return to Visual Studio.

Figure12.jpg

Figure 12. The worksheet displays the information for the first quote.

Use an Actions Pane to Select an Existing Quote

Consider the scenario where the financing manager is meeting with a customer or sales consultant prepping for a meeting with a customer. The financing manager will want to view existing quotes and generate financing information and will potentially want to do this for many quotes.

In the previous tutorial, you added a user control to the Actions Pane in Word to enable the sales representative to view existing quotes and select one for viewing.

In this application, you will add a similar user control that includes two grids. The first grid in the control displays customers with quotes. The second grid displays the saved quotes for each customer. Below the grids, the control displays the automobile’s price, the total cost of selected options and the total quote price.

The amount of the loan, interest rate, length of loan and loan start date are required to generate financing information. The amount of the loan is the total quote price minus the down payment. The financing manager can enter the down payment and the interest rate and click a button to fill out both the Cover Sheet and Select Project > Add Existing Item to display the Open File dialog box. Navigate to the folder where you downloaded this tutorial’s sample project. Navigate to the UI Components folder. Then navigate to the VB or CS folder depending on the language you are using. Select SavedQuotesExcel. Click Add to add the user control to the project.

The user control displays existing quotes (see Figure 13). It contains the following:

  • A grid that lists customers who have quotes.
  • A grid that lists the quotes for each customer.
  • Three labels to display the vehicle price, the total price of selected options and the total price for a quote.
  • Two textboxes to enter the down payment and interest rate.
  • A button to generate financing information.

Figure13.jpg

Figure 13. The SavedQuotesExcel control displays customers and their quotes.

Save your changes. Do not build the solution. It will not compile yet.

In the Solution Explorer, right click ThisWorkbook and select View > Code. Add the following declaration.

' Visual Basic
Public savedQuotesControl As SavedQuotes = Nothing

// C#
public SavedQuotes savedQuotesControl = null;

Add the following method as a placeholder:

' Visual Basic
Public Sub SaveWorkbook(ByVal quoteID As Short)

End Sub

// C#
public void SaveWorkbook(short quoteID)
{

}

Later in the tutorial you will add code to this method to save the workbook as both a PDF file and an Excel workbook.

Replace the code in the ThisWorkbook_Startup method with the following code to create a new instance of the user control and then add the SavedQuotes user control to the Actions Pane.

' Visual Basic
savedQuotesControl = New SavedQuotes
Me.ActionsPane.Controls.Add(savedQuotesControl)
Me.ActionsPane.AutoSize = True

// C#
savedQuotesControl = new SavedQuotes();
this.ActionsPane.Controls.Add(savedQuotesControl);
this.ActionsPane.AutoSize = true;

To have the Actions Pane appear all you need to do is add one or more controls to it. By default, the Visible property of the Actions Pane is set to true. You could set it to false and control when it appears or disappears.

Although it has Height and Width properties, you cannot size the Actions Pane yourself at runtime. You can resize the ActionsPane control at design-time to accommodate the controls on it. However, you have no direct control over the size of the Actions Pane in the document. In the code above the AutoSize property is used to have the Actions Pane size itself at runtime. In the Solution Explorer, right-click on Sheet1 and select View > Code. Add the following method as a placeholder:

' Visual Basic
Public Sub ShowCover(ByVal quoteID As Short)

End Sub

// C#
public void ShowCover(short quoteID)
{

}

Later in the tutorial you will add code to this method to update the cover sheet with the information for a quote.

In the Solution Explorer, right-click on Sheet2 and select View > Code. Add the following method as a placeholder:

' Visual Basic
Public Sub ShowFinancing()

End Sub

// C#
public void ShowFinancing()
{

}

Later in the tutorial you will add code to this method to display the financing information for a quote.

Save your changes and select Build > Build Solution and verify that the project compiles correctly. Press F5 to run the application. The Actions Pane should look like Figure 14. As you scroll through the customers in the top grid, the quotes for that customer should appear in the bottom grid. As you scroll through the quotes, the user control displays the automobile’s price, the total cost of selected options and the total quote price. In addition, it displays the down payment, which defaults to 25% of the quote price. Exit Excel, without saving your changes, and return to Visual Studio.

Figure14.jpg

Figure 14. The finance manager can select an existing quote using the Actions Pane.

Display Cover Sheet and Financing Information for a Quote

To generate financing information, the finance manager can select a quote from the second grid in the SavedQuotes control. He or she can then enter the down payment and interest rate and click the Show financing button.

The code that runs when the finance manager clicks the button first checks that he or she entered an interest rate. The code then performs three tasks. The first task is to show the information on the cover sheet. To do this, the code calls the ShowCover method, passing the quote id as an argument. The second task is to display the financing information. To do this, the code calls the ShowFinancing method. The third task is to save the workbook as both a PDF file and an Excel workbook. To do this, the code calls the SaveWorkbook method, passing the quote id as an argument. You will explore that method later in the tutorial. Double-click on the Show Financing button on the user control to view the code.

' Visual Basic
Globals.Sheet1.ShowCover(quotesSummaryRow.QuoteID)
Globals.Sheet2.ShowFinancing()
Globals.ThisWorkbook.SaveWorkbook(quotesSummaryRow.QuoteID)

// C#
Globals.Sheet1.ShowCover(quotesSummaryRow.QuoteID);
Globals.Sheet2.ShowFinancing();
Globals.ThisWorkbook.SaveWorkbook(quotesSummaryRow.QuoteID);

You will now write the code to show the quote information on the cover sheet. Right-click Sheet1 in the Solution Explorer and select View > Code. Notice the following code in the Sheet1_Startup method:

' Visual Basic
If Me.NeedsFill("ContosoAutoDataSet") Then
  Me.QuoteDetailsTableAdapter.Fill(Me.ContosoAutoDataSet.QuoteDetails)
End If
If Me.NeedsFill("ContosoAutoDataSet") Then
  Me.QuoteHeadersTableAdapter.Fill(Me.ContosoAutoDataSet.QuoteHeaders)
End If

// C#
if (this.NeedsFill("contosoAutoDataSet"))
{
  this.quoteDetailsTableAdapter.Fill(
    this.contosoAutoDataSet.QuoteDetails);
}
if (this.NeedsFill("contosoAutoDataSet"))
{
  this.quoteHeadersTableAdapter.Fill(
    this.contosoAutoDataSet.QuoteHeaders);
}

Visual Studio provides you with the ability to cache the dataset inside the workbook. This enables offline scenarios, which you will explore in the next tutorial in this series. The code above checks if the workbook contains a cached copy of the dataset. It does not, so the code next sends queries to retrieve the needed data from the SQL Server database.

The code retrieves information for all of the existing quotes and displays the first quote on the Cover Sheet worksheet. This is fine for testing data binding, but you will want to retrieve only the necessary information for the quote the finance manager selects. Remove the code from the Sheet1_Startup method.

Add the following code to the ShowCover method to retrieve the quote header and detail rows for the selected quote rather than all quotes:

' Visual Basic
' Retrieve the quote header and detail rows for the selected quote
Me.QuoteHeadersTableAdapter.FillByQuoteID( _
  Me.ContosoAutoDataSet.QuoteHeaders, quoteID)
Me.QuoteDetailsTableAdapter.FillByQuoteID( _
  Me.ContosoAutoDataSet.QuoteDetails, quoteID)

// C#
// Retrieve the quote header and detail rows for the selected quote
this.quoteHeadersTableAdapter.FillByQuoteID(
  this.contosoAutoDataSet.QuoteHeaders, quoteID);
this.quoteDetailsTableAdapter.FillByQuoteID(
  this.contosoAutoDataSet.QuoteDetails, quoteID);

Once these two queries run, the bound data in the cover sheet will display the information for the selected quote.

You will now write the code to display the financing information in the workbook. Right-click Sheet2 in the Solution Explorer and select View > Code.

Add the following method to the ShowFinancing method show the financing information:

' Visual Basic
Total_Vehicle_Cost.Value = _
  Globals.Sheet1.VehicleQuotePriceNamedRange.Value
Down_Payment.Value = Convert.ToDecimal( _
  Globals.ThisWorkbook.savedQuotesControl.downPaymentTextBox.Text)
Interest_Rate.Value = Convert.ToDecimal( _
  Globals.ThisWorkbook.savedQuotesControl. _
  interestRateTextBox.Text) / 100

Loan_Years.Value = 5
Loan_Start.Value = DateTime.Today.AddDays(30)

Me.Cells.Range("C1").Value = _
  String.Format("Financing for {0}'s {1}", _
  Globals.Sheet1.CustomerFullNameNamedRange.Value, _
  Globals.Sheet1.VehicleFullNameNamedRange.Value)

// C#
Total_Vehicle_Cost.Value2 =
  Globals.Sheet1.vehicleQuotePriceNamedRange.Value;
Down_Payment.Value2 = Convert.ToDecimal(
  Globals.ThisWorkbook.savedQuotesControl.downPaymentTextBox.Text);
Interest_Rate.Value2 = Convert.ToDecimal(
  Globals.ThisWorkbook.savedQuotesControl.
  interestRateTextBox.Text) / 100;

Loan_Years.Value2 = 5;
Loan_Start.Value2 = DateTime.Today.AddDays(30);

this.Cells.get_Range("C1", missing).Value2 =
  String.Format("Financing for {0}'s {1}",
  Globals.Sheet1.customerFullNameNamedRange.Value2,
  Globals.Sheet1.vehicleFullNameNamedRange.Value2);

The Cover Sheet worksheet and the user control contain all of the information needed to display financing information. The code above reads the needed information from those two sources and updates the appropriate named ranges and cells on the Automobile Financing worksheet.

Save your changes and select Build > Build Solution and verify that the project compiles correctly. Press F5 to run the application. Both worksheets should be empty. Select a customer and a quote from the user control. Enter 5 in the Interest rate text box and click Show financing. The Cover Sheet worksheet should update and display the quote information. The Automobile Financing worksheet should display the financing information (see Figure 15).

Figure15.jpg

Figure 15. The worksheet displays financing information for the selected quote.

Select a different quote, enter an interest rate and click Show financing. Both worksheets should update. Exit Excel, without saving your changes, and return to Visual Studio.

Save Financing Information as PDF Files

The finance manager can print financing information after generating it, but for additional convenience, he or she may want to email financing information to a customer. A simple way to do this is to save the workbook as a PDF file. To save a workbook as a PDF, you can download and install the Microsoft Save as PDF Add-in for 2007 Microsoft Office programs. You can download this from the MSDN Web site. Once you have installed this add-in, you can manually save an Excel workbook as a PDF file (see Figure 16).

Figure16.jpg

Figure 16. Once you have installed the add-in, you can manually save an Excel workbook as a PDF file.

By default, Excel saves the current worksheet as a PDF. To save the entire workbook, select Save As > PDF or XPS. In the Publish as PDF or XPS dialog box, click Options. In the Options dialog box, select Entire workbook (see Figure 17).

Figure17.jpg

Figure 17. Select this option to save the entire workbook as a PDF file.

You will now add code to save the workbook as a PDF. Return to the code view for the ThisWorkbook file. Add the following code to the SaveWorkbook method:

' Visual Basic
Dim docName As String = String.Format("{0} Quote {1}", _
  Globals.Sheet2.Cells.Range("C1").Value, quoteID)
Dim fileName As String = docName & ".xlsx"

' Save the workbook as a PDF file
Me.ExportAsFixedFormat( _
  Type:=Excel.XlFixedFormatType.xlTypePDF, _   
  Filename:=System.IO.Path.Combine( _
    My.Computer.FileSystem.SpecialDirectories.MyDocuments, docName), _
  Quality:=Excel.XlFixedFormatQuality.xlQualityStandard, _
  IncludeDocProperties:=True, IgnorePrintAreas:=False, _
  OpenAfterPublish:=False)

// C#
string docName = string.Format("{0} Quote {1}",
  Globals.Sheet2.get_Range("C1", missing).Value2, quoteID);
string fileName = docName + ".xlsx";

// Save the workbook as a PDF file
this.ExportAsFixedFormat(
  Excel.XlFixedFormatType.xlTypePDF,
  System.IO.Path.Combine( 
      Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments),
      docName),
  Excel.XlFixedFormatQuality.xlQualityStandard,
  true, false, missing, missing, false, missing);

The code above calls the ExportAsFixedFormat method of the Workbook class. This saves the entire workbook. If you wanted to save only the active worksheet, you could use the following code:

' Visual Basic
CType(Me.ActiveSheet, Excel.Worksheet).ExportAsFixedFormat(…

// C#
((Excel.Worksheet)this.ActiveSheet).ExportAsFixedFormat(…

Save your changes and select Build > Build Solution. Verify that the project compiles correctly. Press F5 to run the application. Select a customer and a quote and enter an interest rate. Click Show financing. Exit Excel, without saving your changes.

In Windows Explorer, navigate to your My Documents folder and confirm there is a PDF file that matches the quote you just viewed. Open the PDF document. The first page is the cover sheet (see Figure 18) and the rest of the document contains the financing information (see Figure 19). Close the PDF document and return to Visual Studio.

Figure18.jpg

Figure 18. The first page of the PDF file displays the quote information.

Figure19.jpg

Figure 19. The rest of the PDF file displays the financing information.

Save Financing Information as Excel Workbooks

Saving financing information as PDF files is appropriate for sending them to customers. For internal use, however, Contoso wants financing information saved as Excel workbooks. You will now write code to perform this task. Return to the code view for the ThisWorkbook file. Add the following code to the SaveWorkbook method:

' Visual Basic
' Save the workbook as an Excel workbook
Me.RemoveCustomization()
Me.ActionsPane.Clear()
With My.Computer.FileSystem
  Me.SaveCopyAs(.CombinePath(.SpecialDirectories.MyDocuments, fileName))
End With
Me.ActionsPane.Show()

// C#
// Save the workbook as an Excel workbook
this.RemoveCustomization();
this.ActionsPane.Clear();
this.SaveCopyAs(System.IO.Path.Combine(
    Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments),
    fileName));
this.ActionsPane.Show();

The workbook contains a reference to customization code. The copy of the workbook also contains that reference. When you open the copied workbook, the VSTO runtime will look for the customization. As with any .NET assembly, it assumes the customization assembly is in the same folder as the Excel workbook. The VSTO runtime will not be able to find the customization assembly. This will result in an error, although Excel will still loads the workbook.

In addition, the copied workbook retains the knowledge that the Actions Pane is used, however the code to populate the Actions Pane with controls is in the customization assembly. Therefore, the workbook will open with an empty Document Actions window.

The obvious solution to this situation is to deploy or install the customization. After developing the solution in Visual Studio, you will want to deploy it for the finance manager. You will see how to do this using ClickOnce deployment in a later tutorial in this series.

However, do the saved workbooks need the customization? They are static views of existing finance information. The customization to generate new financing information does not need to be available in the individual saved workbooks.

The code above calls the RemoveCustomization method of the Workbook class to remove the customization from the workbook when it is saved. The code also detaches the Actions Pane from the workbook before it is saved and then reattaches it so the finance manager can generate additional financing information.

The previous tutorial in this series contains a more detailed explanation of the above.

Save your changes and select Build > Build Solution. Verify that the project compiles correctly. Press F5 to run the application. Select a customer and a quote and enter an interest rate. Click Show financing. Exit Excel, without saving your changes.

In Windows Explorer, navigate to your My Document Folder and confirm there is a XSLX file that matches the quote for which you just generated financing information. Open the workbook. You should not see an error message or an empty Actions Pane.

Conclusion

In this tutorial, you saw how to build a solution in Excel 2007 that makes it easy for finance managers to generate financing information for customers.

The process of generating this information is simple but can be laborious. All of the needed information, except for the interest rate and the length and start date of the loan, is contained in the quotes. Without this solution, the finance manager would need to retype a fair amount of existing data. With this solution, he or she selects a quote, enters the interest rate, adjusts the length and start date as needed and clicks a button.

About the Author

Robert Green ( http://www.mcwtech.com/blogs/rgreen) is a Senior Consultant with MCW Technologies. He is a Microsoft MVP for Visual Studio Tools for Office. Robert has written or co-authored AppDev’s Visual Studio, LINQ, Windows Communication Foundation and Windows Workflow Foundation courseware, and appears in the video training for these courses, as well. Robert is a member of the INETA Speaker Bureau and has been a frequent speaker at technology conferences. Before joining MCW, Robert worked at Microsoft for 8 years, as a Program Manager on the Visual Basic product team and as a Product Manager for Visual Studio, Visual Basic, Visual Studio Tools for Office and Visual FoxPro.