Building the Contoso Auto Sales Office Business Application Part 2 – Generating Automobile Quotes

Article
12/07/2009

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

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 addresses each of the scenarios listed above, using Outlook 2007, Word 2007 and Excel 2007. The tutorials contain both Visual Basic and C# code. Read part 1 of the series here: Building the Contoso Auto Sales Office Business Application Part 1 - Scheduling Customer Appointments

Exercise 1: Create the Word Add-in Project

The generation of price quotes is an important step in the automobile sales process. Cars have list prices, representing the cost of the basic model with standard equipment. On top of that are various option packages. Some packages are performance oriented (bigger engine, bigger tires, more sporty suspension, etc). Some are interior oriented (heated seats, in-dash GPS navigation, better sounding audio, etc). Some are exterior oriented (sunroof, racing stripes, etc).

The solution Contoso wants to help sales consultants prepare quotes is primarily a data application with a heavy emphasis on displaying one-to-many relationships. A sales consultant can select an automobile and see all of the option packages that are available for that model. The consultant will select the options the customer wants and have the total cost of the automobile calculated.

Quotes can be saved and later recalled. The quote record identifies the customer, the automobile and its price, the sales consultant and other information such as the date of the quote. Each quote will zero or more detail records. These identify an option package and include the price.

Contoso has several choices for how this system is built. It could be a Windows or Windows Presentation Foundation (WPF) based client application. Models could be selected from a combo box, list box or grid. The available packages could then be displayed in a grid. This interface would also work for seeing quote headers and details. Or the sales consultant could select a customer and see all saved quotes for that customer. It would be easy to build this UI using Windows Forms or WPF and then use the Open XML SDK to programmatically construct Word documents or Excel workbooks containing quotes.

This could also be built using a Word document-level add-in. A benefit of using Word is the support for graphics and printing, as well as the ease of retrieving generated quotes without needing access to the database or to the application that generated the quote.

The code to generate, save and retrieve quotes is the same across all three choices. The same UI paradigm of displaying one-to-many relationships can be presented to the users. The decision on which approach to take can be based on ease of use, flexibility and workflow.

In this lab, you will create a Word 2007 automobile quote generation solution. The Word solution will support the following:

Quotes can be generated and then printed for customers.
Quotes can be saved so they can be easily viewed and reprinted if the sales consultant is on the phone with a customer or the customer returns to the showroom.
Quotes can be saved to PDF files or Word documents on the sales rep’s computer and to a SharePoint site.

The solution you will build will enable sales representatives to generate quotes for an automobile, including selected option packages. You will base this solution on the Quote Generation.dotx template (see Figure 1).

Figure 1. The solution uses this Word template to generate quotes.

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 Word 2007 Template. Name your project QuoteGeneration 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 2).

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

You can base the add-in on a new or existing template. 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 Quote Generation.dotx and click Open. This returns you to the wizard. Click OK. Click OK if Visual Studio displays the message shown in Figure 3.

Figure 3. 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 on 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 4).

Figure 4. The ContosoAuto database contains these tables.

To see the data in the Manufacturers table, right-click on Manufacturers and select Show Table Data. You should see the data shown in Figure 5. Use the same technique to view the sample data in the Models table (see Figure 6) and AvailablePackages (see Figure 7) tables.

The Manufacturers table contains the automobile makers. The Models table contains the automobiles. The AvailablePackages table contains options available for each automobile. The Employees table contains Contoso Auto’s sales representatives and sales manager.

Figure 5. The Manufacturers table contains this data.

Figure 6. The Models table contains this data.

Figure 7. The AvailablePackages table contains this data.

The Quotes and QuotePackages tables (see Figure 8) contain the information that comprises existing quotes.

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

The Word solution you are building in this tutorial needs a connection to the ContosoAuto database. Close the data windows and select Project > QuoteGeneration 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\QuoteGeneration 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. If a warning appears, click OK and then rebuild the project by selecting Build > Rebuild QuoteGeneration.

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

Figure 9. 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 Auto Quotes

In this exercise, you will provide Contoso’s sales representatives with the ability to generate automobile quotes for customers. The quotes will be based on the Quote Generation Word template.

Data Bind Quote Information

A quote includes the following information:

The customer’s name
The quote number and date
The sales rep’s name
Information about the automobile, including a picture and list price
One or more optional packages
The total price
Notes

In the next step, you will use three user controls to enable the sales rep to select most of this information from tables in the database. You will add data bound content controls to the Word document. These controls will contain and display the various components of the quote.

If you do not see the Word document in Visual Studio, double-click ThisDocument in the Solution Explorer.

Select Data > Show Data Sources to display the Data Sources window. Expand the QuoteHeaders node.

Drag the FirstName column from the QuoteHeaders node in the Data Sources window to the Word template. Drop the column to the right of the space after Quote for but to the left of the paragraph mark (see Figure 10). This creates a content control bound to that column (see Figure 11). Visual Studio also adds data controls, which you can see in the Component Tray.

Figure 10. Drag the FirstName column from the Data Sources window to the Word template.

Figure 11. The FirstName content control is bound to the FirstName column in the QuoteHeaders table.

Content controls are a new feature in Word 2007, and are an improvement over bookmarks, offering a more structured way of storing information in a document. Content controls are regions of a document that contain various types of content, including text, dates, lists and pictures. They can be mapped to XML, making it easy for you to import content into a Word document or export content to an XML file. You can also use programmatically change the data in a document by modifying the contents of the XML.

In the Properties window, rename the content control to firstNameContentControl. Click in the content control and press the right arrow key until the content control is deselected. Add a space after the content control. Drag the LastName column from the Data Sources window to the Word template. Drop the column to the right of the space after firstNameContentControl. Rename the new content control to lastNameContentControl.

Drag the QuoteID column from the Data Sources window to the Word template. Drop the column to the right of the space after Quote number but to the left of the paragraph mark. Rename the content control to quoteIDContentControl.

In the Data Sources window, expand the drop-down list next to the QuoteDate column. Select PlainTextContentControl from the list. Drag the QuoteDate column from the Data Sources window to the Word template. Drop the column to the right of the space after Created but to the left of the paragraph mark. Rename the content control to quoteDateContentControl.

Drag the EmpFirstName column from the Data Sources window to the Word template. Drop the column to the right of the space after By but to the left of the paragraph mark. Rename the content control to empFirstNameContentControl.

Click in the content control and press the right arrow key until the content control is deselected. Add a space after the content control. Drag the EmpLastName column from the Data Sources window to the Word template. Drop the column to the right of the space after the EmpFirstName bookmark. Rename the content control to empLastNameContentControl.

Drag the Year column from the Data Sources window to the Word template. Drop the column to the left of the paragraph mark two lines below the line with By. Rename the content control to yearContentControl. Click in the content control and press the right arrow key until the content control is deselected. Add a space after the content control.

Drag the Manufacturer column from the Data Sources window to the Word template. Drop the column to the right of the space you just added and to the left of the paragraph mark on the second line. Rename the content control to manufacturerContentControl. Click in the content control and press the right arrow key until the content control is deselected. Add a space after the content control.

Drag the Model column from the Data Sources window to the Word template. Drop the column to the right of the space you just added and to the left of the paragraph mark on the second line. Rename the content control to modelContentControl.

Drag the Picture column from the Data Sources window to the Word template. Drop the column in the second paragraph below the year, manufacturer and model. Rename the content control to pictureContentControl.

Drag the Doors column from the Data Sources window to the Word template. Drop the column to the left of the space before door. Rename the content control to doorContentControl.

Drag the BodyStyle column from the Data Sources window to the Word template. Drop the column to the right of the space after door but to the left of the paragraph mark. Rename the content control to bodyStyleContentControl.

Drag the Transmission column from the Data Sources window to the Word template. Drop the column to the left of the space before transmission. Rename the content control to transmissionContentControl.

Drag the VehicleQuotePrice column from the Data Sources window to the Word template. Drop the column in the second column of the first table. Drop the column to the left of the end of cell mark (see Figure 12). Rename the content control to vehicleQuotePriceContentControl.

Figure 12. Drag the VehicleQuotePrice column from the Data Sources window to the Word template.

Drag the TotalQuotePrice column from the Data Sources window to the Word template. Drop the column in the second column of the third table. Drop the column to the left of the end of cell mark (see Figure 13). Rename the content control to totalQuotePriceContentControl.

Figure 13. Drag the TotalQuotePrice column from the Data Sources window to the Word template.

Drag the Notes column from the Data Sources window to the Word template. Drop the column two lines below the last table. Rename the content control to notesContentControl. In the Properties window, remove the text in the PlaceholderText property. That way, nothing will appear in the document if you do not add notes to a quote.

The document should look like Figure 14.

Figure 14. The Word template should look like this.

From the QuoteGeneration Components tab of the Toolbox, drag a QuoteDetailsTableAdapter component onto the Word template. The TableAdapter is added to the Component Tray. Change the Name property to QuoteDetailsTableAdapter if you are using Visual Basic and quoteDetailsTableAdapter if you are using C#.

From the Data tab of the Toolbox, drag a BindingSource component onto the Word template. The BindingSource is added to the Component Tray. Change the Name property to QuoteDetailsBindingSource if you are using Visual Basic and quoteDetailsBindingSource if you are using C#.

In the Properties window, click the arrow to the right of the DataSource property. Expand the node for Other Data Sources. Expand the node for Project Data Sources. Select ContosoAutoDataSet (see Figure 15). Set the DataMember property to QuoteDetails.

Figure 15. Set the DataSource property of the OrderDetailsBindingSource component.

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

Use an Actions Pane to Assist Quote Generation

Consider the sales consultant working with a customer. Typically, the sales consultant will generate several quotes in succession for the customer. The customer may want a car priced with varying combinations of options. The customer may want quotes on more than one car. The sales consultant needs to be able to open the quote generation template once and then generate as many quotes as needed.

A good way to accomplish this is to use the Actions Pane. The Actions Pane can contain Windows Forms or Windows Presentation Foundation controls and is therefore essentially a Windows or WPF form that appears as a task pane. Task panes dock on the right by default, but they can be docked on any border and can be undocked by the user. You will add to the project’s Word document an Actions Pane to display automobiles and available options. You will add three user controls in the Actions Pane, one to generate new quotes, one to view existing quotes and one to switch between these two views.

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 UIComponents folder. Then navigate to the VB or CS folder depending on the language you are using. Select GenerateQuote, SavedQuotes and ControlPicker. Click Add to add the three user controls to the project.

The GenerateQuote control generates new quotes (see Figure 16). It contains the following:

A grid that lists available cars.
A PictureBox control that displays the car.
A grid listing that lists option packages for each car and provides a check box for selecting an option.
A text box for notes.
A label that displays the total quote price.
Combo boxes for selecting the customer and the sales consultant.
A button to generate the quote.
A button to save financing information. You will write code for this in the next tutorial in this series.

Figure 16. The GenerateQuote control generates new quotes.

The SavedQuotes control displays existing quotes (see Figure 17). 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 each quote.
A button to display the quote.
A button to save financing information. You will write code for this in the next tutorial in this series.

Figure 17. The SavedQuotes control displays existing quotes.

The ControlPicker control contains a button that enables the user to switch between the other two controls.

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

In the Solution Explorer, right click on ThisDocument and select View > Code. Add the following declarations.

' Visual Basic
Public generateQuoteControl As GenerateQuote = Nothing
Public savedQuotesControl As SavedQuotes = Nothing
Public controlPickerControl As ControlPicker = Nothing

// C#
public GenerateQuote generateQuoteControl = null;
public SavedQuotes savedQuotesControl = null;
public ControlPicker controlPickerControl = null;

Replace the code in the ThisDocument_Startup method with the following code to create new instances of the user controls and then add the ControlPicker and GenerateQuote user controls to the Actions Pane.

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

// C#
generateQuoteControl = new GenerateQuote();
savedQuotesControl = new SavedQuotes();
controlPickerControl = new ControlPicker();
this.ActionsPane.Controls.Add(controlPickerControl);
this.ActionsPane.Controls.Add(generateQuoteControl);
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.

Add the following methods as placeholders.

' Visual Basic
Public Sub PopulateDoc(ByVal quoteID As Integer)
End Sub
Public Sub SaveQuoteDoc()
End Sub
Public Sub SaveFinancing()
End Sub

// C#
public void PopulateDoc(Int16 quoteID)
{
}
public void SaveQuoteDoc()
{
}
public void SaveFinancing()
{
}

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 18. As you scroll through the automobiles in the top grid, the picture and the available options should change. Exit Word, without saving your changes, and return to Visual Studio.

Figure 18. The sales rep can generate a new quote using the Actions Pane.

Generate and Displays Quotes

To generate a quote, the sales consultant can select an automobile from the first grid in the GenerateQuotes control. The picture of the automobile will display. The sales consultant can then select any number of options packages. The total cost of the automobile is updated as options are selected or deselected. The consultant can select the customer’s name from the Quote for list and his or her name from the Sales rep list. When the quote is ready to be generated, the sales rep clicks the Generate quote button.

The code that runs when the sales consultant clicks the button performs two tasks. The first task is to generate the quote information and save it to the database. The code in the button’s Click event handler adds a new row to the Quotes table, representing the quote itself. The code then adds a new row to the QuotePackages table for each selected option.

The second task that occurs when the sales rep clicks the button is to display the quote in the document. To do this, the code calls the PopulateDoc method, passing the quote id as an argument.

' Visual Basic
Globals.ThisDocument.PopulateDoc(quoteRow.QuoteID)

// C#
Globals.ThisDocument.PopulateDoc(quoteRow.QuoteID);

You will now write the code to display the quote in the document. Return to the code view for the ThisDocument file. Add the following declarations.

' Visual Basic
Private quoteHeaderRow As ContosoAutoDataSet.QuoteHeadersRow = Nothing
Private quoteDetailRow As ContosoAutoDataSet.QuoteDetailsRow = Nothing
Private headerTable As Word.Table = Nothing
Private detailTable As Word.Table = Nothing
Private footerTable As Word.Table = Nothing

// C#
private ContosoAutoDataSet.QuoteHeadersRow quoteHeaderRow = null;
private ContosoAutoDataSet.QuoteDetailsRow quoteDetailRow = null;
private Word.Table headerTable = null;
private Word.Table detailTable = null;
private Word.Table footerTable = null;

Add the following code to the PopulateDoc method to retrieve from the database the header and options information for the just generated quote.

' Visual Basic
QuoteHeadersTableAdapter.FillByQuoteID( _
  ContosoAutoDataSet.QuoteHeaders, quoteID)
quoteHeaderRow = CType(CType( _
  QuoteHeadersBindingSource.Current, DataRowView).Row, _
  QuoteGeneration.ContosoAutoDataSet.QuoteHeadersRow)
QuoteDetailsTableAdapter.FillByQuoteID( _
  ContosoAutoDataSet.QuoteDetails, quoteHeaderRow.QuoteID)

// C#
quoteHeadersTableAdapter.FillByQuoteID(
  contosoAutoDataSet.QuoteHeaders, quoteID);
quoteHeaderRow =
  ((QuoteGeneration.ContosoAutoDataSet.QuoteHeadersRow)
  (((DataRowView)(quoteHeadersBindingSource.Current)).Row));
quoteDetailsTableAdapter.FillByQuoteID(
  contosoAutoDataSet.QuoteDetails, quoteHeaderRow.QuoteID);

Add the following code to get a reference to the first table in the Word document. This table contains the vehicle’s list price (see Figure 19).

' Visual Basic
headerTable = Me.Tables(1)

// C#
headerTable = this.Tables[1];

Figure 19. This is the first table in the Word document.

Add the following code to display the quoted list price for the car in the table.

' Visual Basic
headerTable.Cell(1, 2).Range.Text = String.Format("{0:C}", _
  quoteHeaderRow.VehicleQuotePrice)

// C#
headerTable.Cell(1, 2).Range.Text = string.Format("{0:C}",
  quoteHeaderRow.VehicleQuotePrice);

Add the following code to get a reference to the second table. This table contains the selected options (see Figure 20).

' Visual Basic
detailTable = Me.Tables(2)

// C#
detailTable = this.Tables[2];

Figure 20. This is the second table in the Word document.

The sales consultant can open the template and create any number of quotes, so the table must be emptied before a new quote is generated. The first row contains the column headers so that row is not deleted. Add the following code to empty the second table in the document, if necessary, so it can then be filled with the selected option packages.

' Visual Basic
If detailTable.Rows.Count > 1 Then
  For i As Integer = detailTable.Rows.Count To 2 Step -1
    detailTable.Rows(i).Delete()
  Next
End If

// C#
if (detailTable.Rows.Count > 1)
{
  for (int i = detailTable.Rows.Count; i >= 2; i -= 1)
  {
    detailTable.Rows[i].Delete();
  }
}

When you insert a row in a table it uses the formatting of the row above it. The first row in the table is bolded so each of the added rows would be bold. The first row is unbolded before new rows are added to prevent this. The row is then bolded after the table is filled with the selected options. Add the following code to unbold the header row.

' Visual Basic
detailTable.Rows(1).Range.Bold = 0

// C#
detailTable.Rows[1].Range.Bold = 0;

Add the following code to populate the table with the quote details. There will be one for each option package in the quote.

' Visual Basic
For i As Integer = 1 To QuoteDetailsBindingSource.Count
  quoteDetailRow = CType(CType( _
    QuoteDetailsBindingSource.Current, DataRowView).Row, _
    QuoteGeneration.ContosoAutoDataSet.QuoteDetailsRow)
  detailTable.Rows.Add()
  detailTable.Cell(i + 1, 1).Range.Text = _
    quoteDetailRow.PackageName
  If quoteDetailRow.IsDescriptionNull Then
    detailTable.Cell(i + 1, 2).Range.Text = String.Empty
  Else
    detailTable.Cell(i + 1, 2).Range.Text = _
      quoteDetailRow.Description
  End If
  detailTable.Cell(i + 1, 3).Range.Text = String.Format("{0:C}", _
   quoteDetailRow.QuotePrice)
  QuoteDetailsBindingSource.MoveNext()
Next

// C#
for (int i = 1; i <= quoteDetailsBindingSource.Count; i+)
{
  quoteDetailRow =
    ((QuoteGeneration.ContosoAutoDataSet.QuoteDetailsRow)
    (((DataRowView)
    (quoteDetailsBindingSource.Current)).Row));
  detailTable.Rows.Add(ref missing);
  detailTable.Cell(i + 1, 1).Range.Text =
    quoteDetailRow.PackageName;
  if (quoteDetailRow.IsDescriptionNull())
  {
    detailTable.Cell(i + 1, 2).Range.Text = string.Empty;
  }
  else
  {
    detailTable.Cell(i + 1, 2).Range.Text =
      quoteDetailRow.Description;
  }
  detailTable.Cell(i + 1, 3).Range.Text = string.Format("{0:C}",
    quoteDetailRow.QuotePrice);
  quoteDetailsBindingSource.MoveNext();
}

Add the following code to bold the header row in the table.

' Visual Basic
detailTable.Rows(1).Range.Bold = 1

// C#
detailTable.Rows[1].Range.Bold = 1;

Add the following code to get a reference to the third table. This table contains the total cost of the automobile (see Figure 21).

' Visual Basic
footerTable = Me.Tables(3)

// C#
footerTable = this.Tables[3];

Figure 21. This is the third table in the Word document.

Add the following code to display the total quote price in currency format in the third table.

' Visual Basic
footerTable.Cell(1, 2).Range.Text = String.Format("{0:C}", _
  quoteHeaderRow.TotalQuotePrice)

// C#
footerTable.Cell(1, 2).Range.Text = string.Format("{0:C}",
  quoteHeaderRow.TotalQuotePrice);

Save your changes and select Build > Build Solution. Verify that the project compiles correctly. Press F5 to run the application. To construct a quote, select an automobile from the first grid in the Actions Pane. Select any number of options from the second grid. Enter notes in the text box below the second grid. Select a customer name from the Quote for drop-down list. Select a sales consultant name from the Sales rep drop-down list. Click Generate quote. The Word document should contain the quote information (see Figure 22).

Figure 22. The Word document should look like this.

Create two additional quotes using the same technique. Use different automobiles and different customers. Click Saved quotes. The code that runs removes the GenerateQuote user control from the Actions Pane and adds the SavedQuote user control. You should now see two grids, one listing customers with quotes and another showing the quotes for each customer (see Figure 23).

Figure 23. The sales rep can view existing quotes using the Actions Pane.

Select an existing quote and click Display quote. The Word document should contain the information for the quote. Exit Word and return to Visual Studio.

Exercise 3: Save Quotes

The sales rep can print a quote after generating it, but for additional convenience, he or she will typically want to save a copy of the quote to disk. In this section, you will explore several ways of saving a quote. You will save it to a PDF file, to a Word document and to SharePoint.

Save Quotes as PDF Files

The sales rep may want to email one or more quotes to a customer. A simple way to do this is to save the document as a PDF file.

To save a document 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 (https://www.microsoft.com/downloads/details.aspx?FamilyId=F1FC413C-6D89-4F15-991B-63B07BA5F2E5&displaylang=en). Once you have installed this add-in, you can manually save a Word document as a PDF file (see Figure 24).

Figure 24. Once you have installed the add-in, you can manually save a Word document as a PDF file.

The code that runs when the sales consultant clicks the Generate Quote button calls the SaveQuoteDoc method in the ThisDocument class. You will now write code in that method to save the quote as a PDF. Return to the code view for the ThisDocument file. Add the following code to the SaveQuoteDoc method:

' Visual Basic
Dim dirName As String = _
     Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) _
     & "\Auto Quotes"
System.IO.Directory.CreateDirectory(dirName)
Dim docName As String = String.Format( _
  "{0}_{1}_Quote_{2}_{3:MM}_{3:dd}_{3:yy}", _
  lastNameContentControl.Text, firstNameContentControl.Text, _
  quoteIDContentControl.Text, _
  System.Convert.ToDateTime(quoteDateContentControl.Text))
Me.SaveAs(System.IO.Path.Combine(dirName, docName), _
  Word.WdSaveFormat.wdFormatPDF)

// C#
string dirName =
   Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) +
   @"\Auto Quotes";
System.IO.Directory.CreateDirectory(dirName);
string docName = String.Format(
  "{0}_{1}_Quote_{2}_{3:MM}_{3:dd}_{3:yy}",
  lastNameContentControl.Text, firstNameContentControl.Text,
  quoteIDContentControl.Text,
  System.Convert.ToDateTime(quoteDateContentControl.Text));
object docFile = System.IO.Path.Combine(dirName, docName);
object saveFormat = Word.WdSaveFormat.wdFormatPDF;
this.SaveAs(ref docFile, ref saveFormat,
  ref missing, ref missing, ref missing, ref missing, ref missing,
  ref missing, ref missing, ref missing, ref missing, ref missing,
  ref missing, ref missing, ref missing, ref missing);

Save your changes and select Build > Build Solution. Verify that the project compiles correctly. Press F5 to run the application. Construct a quote and click Generate quote. Exit Word.

In Windows Explorer, navigate to your My Documents\Auto Quotes folder and confirm there is a PDF file that matches the quote you just generated. Open the saved quote document (see Figure 25). Close the PDF document and return to Visual Studio.

Figure 25. The sales rep can save the quote as a PDF file.

Save Quotes as Word Documents

Saving quotes as PDF files is appropriate for sending them to customers. For internal use, however, Contoso wants quotes saved as Word documents. You will now write code to save the quote as a Word document. Return to the code view for the ThisDocument file. Add the following code to the SaveQuoteDoc method:

' Visual Basic
Me.SaveAs(System.IO.Path.Combine(dirName, docName), _
  Word.WdSaveFormat.wdFormatPDF)
Me.SaveAs(System.IO.Path.Combine(dirName, docName))

// C#
this.SaveAs(ref docFile, ref saveFormat,
  ref missing, ref missing, ref missing, ref missing, ref missing,
  ref missing, ref missing, ref missing, ref missing, ref missing,
  ref missing, ref missing, ref missing, ref missing);
this.SaveAs(ref docFile, ref missing,
  ref missing, ref missing, ref missing, ref missing, ref missing,
  ref missing, ref missing, ref missing, ref missing, ref missing,
  ref missing, ref missing, ref missing, ref missing);

Save your changes and select Build > Build Solution. Verify that the project compiles correctly. Press F5 to run the application. Construct a quote and click Generate quote. Exit Word.

In Windows Explorer, navigate to your My Documents\Auto Quotes folder and confirm there is a .docx file that matches the quote you just generated. Open the saved quote document. You should see an error message informing you that the customization assembly could not be installed (see Figure 26). Click Close to dismiss the message. You should then see an error message informing you that the customization assembly could not be found or loaded (see Figure 27). Click OK to dismiss that message.

Figure 26. The VSTO runtime cannot install the customization associated with this document.

Figure 27. The VSTO runtime cannot find or load the customization associated with this document.

The template used to create quotes contains a reference to customization code. Any document created by the template also contains that reference. When you open the document, the VSTO runtime looks for the customization. As with any .NET assembly, it assumes the customization assembly is in the same folder as the Word document. When you build the Word add-in solution, Visual Studio builds the customization assembly and copies the Word template into the output folder, so they are in the same location. You can then start Word from Visual Studio and the customization works. You then saved the quote document to a different folder. When you open that document, the VSTO runtime cannot find the customization assembly.

Word still loads the document even if the customization doesn’t load. The quote information is in the document, as well as an empty Actions Pane (see Figure 28). The document retains the knowledge that the Actions Pane is used, however the code to populate the Actions Pane with controls is in the customization assembly. Close the document.

Figure 28. The Actions Pane appears even though the customization did not load.

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 sales representatives. You will see how to do this using ClickOnce deployment in a later tutorial in this series.

However, do the saved quotes need the customization? They are static views of existing quote information. The customization to generate a new quote or retrieve existing quotes does not need to be available in the individual quote documents. It only needs to be available in the Quote Generation template.

You will next write code to remove the customization and the Actions Pane from the document when it is saved. Return to the code view for the ThisDocument file. Add the following code to the SaveQuoteDoc method:

' Visual Basic
Me.SaveAs(System.IO.Path.Combine(dirName, docName), _
  Word.WdSaveFormat.wdFormatPDF)
Me.RemoveCustomization()
Me.ActionsPane.Clear()
Me.XMLSchemaReferences("ActionsPane3").Delete()
Me.SaveAs(System.IO.Path.Combine(dirName, docName))
Me.ActionsPane.Show()

// C#
this.SaveAs(ref docFile, ref saveFormat,
  ref missing, ref missing, ref missing, ref missing, ref missing,
  ref missing, ref missing, ref missing, ref missing, ref missing,
  ref missing, ref missing, ref missing, ref missing);
this.RemoveCustomization();
this.ActionsPane.Clear();
foreach (Word.XMLSchemaReference schemaRef in
  this.XMLSchemaReferences)
{
  if (schemaRef.NamespaceURI == "ActionsPane3")
  {
    schemaRef.Delete();
    break;
  }
}
this.SaveAs(ref docFile, ref missing,
  ref missing, ref missing, ref missing, ref missing, ref missing,
  ref missing, ref missing, ref missing, ref missing, ref missing,
  ref missing, ref missing, ref missing, ref missing);
this.ActionsPane.Show();

When you create a document level customization, Visual Studio adds two custom properties to the document. The _AssemblyLocation property contains information about the customization’s deployment manifest. The deployment manifest specifies the location and version of the customization assembly. The _AssemblyName property contains the CLSID of the VSTO loader.

If these properties exist, the Visual Studio Tools for Office runtime will look for the assembly and attempt to run the code when a user opens the document. The solution used here calls the RemoveCustomization method of the Document class. This removes the two properties.

An additional step is required. To display the Actions Pane and the controls you added to it, the Microsoft Actions Pane XML expansion pack is attached to the document by the Visual Studio Tools for Office runtime. Removing the customization code does not detach this expansion pack. The solution is to detach the Actions Pane from the document before it is saved and to then reattach the Actions Pane after saving the document so the sales consultant can generate or view another quote.

Save your changes and select Build > Build Solution. Verify that the project compiles correctly. Press F5 to run the application. Construct a quote and click Generate quote. Exit Word.

In Windows Explorer, navigate your My Documents\Auto Quotes folder and confirm there is a .docx file that matches the quote you just generated. Open the saved quote document. You should not see an error message or an empty Actions Pane. The sales representative can now forward the quote to customers or other sales reps.

Save Quotes to SharePoint

It is handy for the sales reps to save quotes to their local drives. They may want to review quotes when they are at home or meeting with a customer. It would also be handy to store quote documents in a more central location. That way, sales reps could easily view not only their own quotes, but quotes generated by other reps. You will now write code to save quote documents to a SharePoint site.

First, use either Office SharePoint Server or Windows SharePoint Services to create a new team site named ContosoAuto. You will store quote documents in the Shared Documents list.

Next, you will manually save a quote document to the SharePoint site. This will serve as a visual walkthrough of the code you will write shortly. Open one of the saved quote documents. Select the Office button in the upper left of Word and then select Publish > Document Management Server (see Figure 29). In the Save As dialog box, prepend the address of the SharePoint site’s Shared Documents library to the file name (see Figure 30) and click Save. In your Web browser, navigate to the ContosoAuto site’s Shared Documents list. You should see the quote document (see Figure 31).

Figure 29. Select Publish > Document Management Server to save a Word document to SharePoint.

Figure 30. Prepend the address of the SharePoint site’s Shared Documents library to the file name.

Figure 31. You have published the quote document to SharePoint.

You will now write code to automate this process. Exit Word. In Visual Studio, return to the code view for the ThisDocument file. Add the following code to the SaveQuoteDoc method, substituting the name of your Web server for <Server>:

' Visual Basic
Me.SaveAs(System.IO.Path.Combine(dirName, docName))
Me.SaveAs(System.IO.Path.Combine( _
  "\\<Server>\davwwwroot\contosoauto\Shared Documents", docName), _
  Word.WdSaveFormat.wdFormatXMLDocument)
Me.ActionsPane.Show()

// C#
this.SaveAs(ref docFile, ref missing,
  ref missing, ref missing, ref missing, ref missing, ref missing,
  ref missing, ref missing, ref missing, ref missing, ref missing,
  ref missing, ref missing, ref missing, ref missing);
docFile = System.IO.Path.Combine(
  @"\\graney\davwwwroot\contosoauto\Shared Documents", docName);
saveFormat = Word.WdSaveFormat.wdFormatXMLDocument;
this.SaveAs(ref docFile, ref saveFormat,
  ref missing, ref missing, ref missing, ref missing, ref missing,
  ref missing, ref missing, ref missing, ref missing, ref missing,
  ref missing, ref missing, ref missing, ref missing);
this.ActionsPane.Show();

Each document library in SharePoint has a corresponding WebDAV folder. WebDAV is a network service that allows users access to files. The WebDVA address for http:<Server>/ContosoAuto/Shared Documents is \\<Server>\davwwwroot\contosoauto\Shared Documents. The code above uses that as the location for saving the quote document.

Save your changes and select Build > Build Solution. Verify that the project compiles correctly. Press F5 to run the application. Construct a quote and click Generate quote. Exit Word. In your Web browser, navigate to the ContosoAuto site’s Shared Documents list. You should see both quote documents.

Conclusion

In this tutorial, you saw how to build a solution in Word 2007 that makes it easy for sales consultants to generate, print and save quotes for customers.

There are three main parts to this solution: code to access data, UI to display the data and code to work with the Word documents. If you have used Visual Studio to build data based Windows applications you are already familiar with the first two of these. The ability to use the same data techniques and UI controls in both your Windows and Office solutions significantly lowers the barrier to entry to building Office solutions.

Developers often use “forms over data” to describe data applications. This description accounts for the fact that the data they work with is fairly constant and over time and across scenarios a variety of front-ends are built to work with that data. With Visual Studio Tools for Office you can add Word and other Office applications to the list of front-ends you can use to work with data.

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.

Building the Contoso Auto Sales Office Business Application Part 2 – Generating Automobile Quotes

Introduction

Exercise 1: Create the Word Add-in Project

Exercise 2: Generate Auto Quotes

Data Bind Quote Information

Use an Actions Pane to Assist Quote Generation

Generate and Displays Quotes

Exercise 3: Save Quotes

Save Quotes as PDF Files

Save Quotes as Word Documents

Save Quotes to SharePoint

Conclusion

About the Author

Additional resources