Exercise 2: Embedding an Excel Worksheet in Microsoft Word

In this exercise, you will embed your Excel worksheet in a Microsoft Word document. This exercise will also introduce C# developers to optional parameters.

Task 1 – Embedding your Excel Worksheet into a New Microsoft Word Document

1. Open Microsoft Visual Studio 2010 from Start | All Programs | Microsoft Visual Studio 2010 | Microsoft Visual Studio 2010.
2. Open the OfficeApplication.sln solution file. By default, this file is located in the folder: %TrainingKitInstallFolder%\Labs\Dev10Office\Source\Ex02-EmbeddingWorksheet\Begin\ and choose the language of your preference. Optionally, you can continue working the solution you created in the previous exercise.
3. Copy your Excel worksheet so that it can be pasted into the Word document. The Copy() method accomplishes this for you. Add the following code to the end of the DisplayInExcel method (in the Visual Basic method, this command must be within the With block):

   Visual Basic

   .Range("A1:C4").Copy()

   C#

   x1.get_Range("A1:C4").Copy();

4. Now that your worksheet is copied to the clipboard, you need to create a Word document and past the worksheet into it. In the Module1.vb (VB) or Program.cs (C#) file, create a new method called EmbedInWordDocument as shown in the following code:

   (Code Snippet – Office Programmability Lab - Ex2 EmbedInWordDocument VB)

   Visual Basic

   Private Sub EmbedInWordDocument()
       Dim word As New Word.Application
       word.Visible = True
       word.Documents.Add()
       word.Selection.PasteSpecial(Link:=True, DisplayAsIcon:=False)
   End Sub

   (Code Snippet – Office Programmability Lab - Ex2 EmbedInWordDocument CSharp)

   C#

   private static void EmbedInWordDocument()
   {
       var word = new Word.Application();
       word.Visible = true;
       word.Documents.Add();
       word.Selection.PasteSpecial(Link: true, DisplayAsIcon: false);
   }

   Note:

   C# developers will recognize a new feature to C# 4.0 in the call to PasteSpecial: the use of optional parameters. PasteSpecial actually has seven parameters. In most cases, you do not need to supply values for these parameters, as the defaults are adequate for the application. In previous version of C# these values still had to be provided even if the provided value was of type System.Reflection.Missing.Value, which resulted in called to PasteSpecial that look like this:

   C#

   object iconIndex = System.Reflection.Missing.Value;
   object link = true;
   object placement = System.Reflection.Missing.Value;
   object displayAsIcon = false;
   object dataType = System.Reflection.Missing.Value;
   object iconFileName = System.Reflection.Missing.Value;
   object iconLabel = System.Reflection.Missing.Value;
   word.Selection.PasteSpecial(ref iconIndex,
             ref link,
             ref placement,
             ref displayAsIcon,
             ref dataType,
             ref iconFileName,
             ref iconLabel);

   Note:

   C# developers should also note that while parameters to PasteSpecial are still by reference, it is no longer necessary to specify this with the ref keyword.

5. Call the EmbedInWordDocument method from the end of the Main method in the Module1.vb (VB) or Program.cs (C#) file as show:

   Visual Basic

   Sub Main()

       Dim checkAccounts = CreateAccountList()

       checkAccounts.DisplayInExcel(Sub(account, cell)
               cell.Value2 = account.ID
               cell.Offset(0, 1).Value2 = account.Balance
               cell.Offset(0, 2).Value2 = account.AccountHolder
               If account.Balance < 0 Then
                   cell.Interior.Color = RGB(255, 0, 0)
                   cell.Offset(0, 1).Interior.Color = RGB(255, 0, 0)
                   cell.Offset(0, 2).Interior.Color = RGB(255, 0, 0)
               End If
           End Sub)
    
       EmbedInWordDocument()
   End Sub

   C#

   static void Main(string[] args)

   {

       var checkAccounts = CreateAccountList();

       checkAccounts.DisplayInExcel((account, cell) =>
       {
           cell.Value2 = account.ID;
           cell.get_Offset(0, 1).Value2 = account.Balance;
           cell.get_Offset(0, 2).Value2 = account.AccountHolder;
           if (account.Balance < 0)
           {
               cell.Interior.Color = 255;
               cell.get_Offset(0, 1).Interior.Color = 255;
               cell.get_Offset(0, 2).Interior.Color = 255;
           }
       });
    
       EmbedInWordDocument();
   }

Next Step

Exercise 2: Verification