Create a Command Bar in Word 2003





Create a Command Bar in Word 2003

How about a .NET-powered toolbar in Word? You'll want to click it for no good reason at all, just knowing how cool it is that you're calling into managed code.

In this hack, you will be creating a command bar item with a list of customers from the Northwind database.

If your installation of the Visual Studio Tools for Office is complete, you should see some additional project types in Visual Studio's New Project dialog. I'll show you how to create a new Visual C# Project for a Microsoft Word template called CommandBar (as shown in Figure).

New Project dialog


Once you have selected the project template and named the project, click OK and you'll be presented with the Microsoft Office Project Wizard (Figure). Since you are going to be working with a new document template, select the option to Create a New Document. If you want to change the name or location of the document template that will be created, you can change the corresponding text box.

On the Security Settings tab, the wizard will automatically update your security settings to allow the managed code document to run. There is not space here to explain how to set all of the security settings, so until you can spend some time figuring out the Code Access Security model for the project, I suggest that you let the wizard make the necessary changes. When you click Finish, the wizard will create the required document templates and a code-behind class for the document.

Microsoft Office Project Wizard


Create the Command Bar

When the wizard has completed, you will be placed into the main code file named ThisDocument.cs. Most of your code will execute when a user creates a new instance of your document. This event is handled by the following method:

protected void ThisDocument_New( )

{

}

At this point, you are going to need to create some class-level variables to work with as you build your command bar. The main class is OfficeCodeBehind, so your code should look something like this:

public class OfficeCodeBehind

{

    private object oMissing = System.Reflection.Missing.Value;

    private Office.CommandBar CBar;

    private Office.CommandBarComboBox CBarComboBox;

    private Office.CommandBarButton CBarButton;

    // ...

}

Next, you will need to write some code to create the different user interface elements that you plan to place on the command bar. In this example, you will need a combo listbox and a button. (You may need to change the connection string, highlighted in bold, to suit your SQL Server or MSDE installation.)

You will notice that in this section of code, you will be using the oMissing object you defined in the preceding code. This is because these are optional variables in the Word document and C# does not support optional variables, so you must pass in a default value.


private bool SetupCommandBar( )

{

    try

    {

        // Create command bar for this document

        ThisApplication.CustomizationContext=ThisDocument;

        CBar = ThisApplication.CommandBars.Add("Customer Form",

            oMissing, oMissing, (object)true);

   

        // Add a combo box to the command bar

        object objType = Office.MsoControlType.msoControlDropdown;

        CBarComboBox = (Office.CommandBarComboBox)

            ((CBar.Controls.Add(objType, oMissing, oMissing,

            oMissing, (object)true)));

        CBarComboBox.Caption = "Select:";

        CBarComboBox.Style = Office.MsoComboStyle.msoComboLabel;

        CBarComboBox.ListIndex = 0;

        CBarComboBox.Width = 250;

        CBarComboBox.BeginGroup=true;

   

        //Add items to combo box from database

        SqlConnection conn = 

           new SqlConnection("data source=localhost;

            initial catalog=Northwind;integrated security=true");

        SqlCommand cmd = new SqlCommand("select CustomerID, 

           CompanyName from customers", conn);

        SqlDataReader dr;

        conn.Open( );

        dr = cmd.ExecuteReader( );

        int i = 0;

        while (dr.Read( ))

        {

            i += 1;

            CBarComboBox.AddItem(dr["CustomerID"].ToString( ) 

                + "-" + dr["CompanyName"].ToString( ), i);

        }

        dr.Close( );

        conn.Close( );

        

        // Add a button to the command bar to insert data

        CBarButton = (Office.CommandBarButton)

            (CBar.Controls.Add((object)1, oMissing, oMissing, 

            oMissing, oMissing));

        CBarButton.Style=Office.MsoButtonStyle.msoButtonCaption;

        CBarButton.Caption = "Insert";

   

        // Make the new command bar visible.

        CBar.Visible=true;

        return true;

    }

    catch (Exception ex)

    {

        MessageBox.Show("Error creating CommandBar: " + ex.Message, 

            "Customer Form", MessageBoxButtons.OK,

            MessageBoxIcon.Error);

        return false;

    }

}

In the preceding code, the first thing you need to create a command bar is a reference to the current instance of the Microsoft Word application you will be working in. Once you have this reference, you can create a new command bar by calling the Add method of the CommandBars collection.

The next step is to create the drop-down combo box (Office.MsoControlType.msoControlDropdown). Once you have created the combo box, you can set the properties for how you want the control to appear to the user.

After the combo box is created, you are ready to populate the control with values from a database. In this example, the data will be coming from the Northwind database on the SQL Server of the machine that is running the code. Since you will need only the CustomerID and CustomerName from the database, it is a good practice to select only that data. Since you will be using a SqlDataReader to get the data, you will be responsible for opening the connection in your code before executing your SqlCommand object. The ExecuteReader object will create a SqlDataReader object and pass the reference back to the variable we have declared in order to read the data. At this point, you have not read any data.

The while loop begins reading the data by grabbing the next available row. As long as the SqlDataReader successfully returns data, the code that adds the item to the combo box executes. Unfortunately, in this example, the primary key that we need to use to identify the customer is a string-based key, so you cannot use it as the index parameter of the AddItem method. For now, you will make the key part of the display text and retrieve the key value when you handle the button click later. After reading the data, you need to close the SqlDataReader and the SqlConnection.

Next, you need to create the button on the toolbar (Office.CommandBarButton) and set its display properties. Finally, show the command bar to the user with the Show method.

Now that the method to create the command bar is complete, you will need to call the method to initialize the command bar's creation when a new document is created:

protected void ThisDocument_New( )

{

    SetupCommandBar( );

}

Now, test your new command bar by executing the application. Visual Studio should start an instance of Word, create a new document from your blank template, and finally raise the ThisDocument_New event, which will create your command bar as shown in Figure.

Command bar in Word


Handle the Command Bar Events

Now that you have a command bar in Word, you need to listen for the events that the command bar raises. In this section, you will learn how to wire up the command bar events to your custom code.

The first order of business is to create a class-level delegate for the events you want to handle:

public class OfficeCodeBehind

{

    private Office._CommandBarButtonEvents_ClickEventHandler 

        CBarButtonEvent;

    ...

Second, you will need to create a method containing the code you want to run when the command bar raises the event. In this code, you will be taking the combo box selection and looking up the customer record to type the address into the Word document:

private void CBarButton_Click(

    Office.CommandBarButton btn, 

    ref bool Cancel)

{

    // Get Word ready to receive data

    Word.Selection sln = null;

    sln = ThisApplication.Selection;

    ThisApplication.Options.Overtype = false;

   

    // Get customer ID back into individual fields

    string wrkString = CBarComboBox.Text;

    string[  ] Customer = wrkString.Split('-');

       

    // Get customer information from database

    SqlConnection conn = new SqlConnection("data source=localhost;

        initial catalog=Northwind;integrated security=true");

    SqlCommand cmd = new SqlCommand("select * from customers 

        where customerid = @CustID", conn);

    cmd.Parameters.Add("@CustID", Customer[0]);

    SqlDataReader dr;

    conn.Open( );

    dr = cmd.ExecuteReader( );

   

    //Output customer Address Data to Word

    while (dr.Read( ))

    {

        sln.TypeText(dr["CustomerID"].ToString( ) + "\n");

        sln.TypeText(dr["CompanyName"].ToString( ) + "\n");

        sln.TypeText(dr["ContactName"].ToString( ) + "\n");

        sln.TypeText(dr["Address"].ToString( ) + "\n");

        sln.TypeText(dr["City"].ToString( ) + ", ");

        sln.TypeText(dr["Region"].ToString( ) + "  ");

        sln.TypeText(dr["PostalCode"].ToString( ) + "\n");

        sln.TypeText(dr["Country"].ToString( ));

    }

    dr.Close( );

    conn.Close( );

}

In the preceding code, you first create a variable for the current selection in Word. The next step is to take the selection from the combo box and split the text into an array containing two items.

In a scenario in which you are able to use an integer primary key, you would want to use that value as the indexer when calling the AddItem method as you build the command bar:

CBarComboBox.AddItem(dr["CompanyName"].ToString( ),

    dr["CompanyID"]);

If you take this approach, you would be able to query for the key value by using the following code:

int CustomerID = CBarComboBox.Value;


Next, you need to create a SqlConnection, SqlCommand, and SqlDataReader to retrieve the data from the database. Once you have retrieved the record, you will output the records to the Word.Selection variable that you created earlier using the TypeText method.

Now that you have created the method to output the data record to your Word document, you need to wire that code into the button by attaching the delegate to your event handler:

private void WireUpEvents( )

{

    // Set up the Click events for the command bar buttons.

    CBarButtonEvent = 

        new Office._CommandBarButtonEvents_ClickEventHandler

        (CBarButton_Click);

    CBarButton.Click += CBarButtonEvent;

}

One last order of business is to finish the document initialization process by updating the ThisDocument_New method to call the event wire-up code after the command bar has been created:

protected void ThisDocument_New( )

{

    SetupCommandBar( );

    WireUpEvents( );

}

Now, build and test the application once again. This time, when you click the button on the command bar, the name and address for the customer will be typed into the Word document.

Brian Sherwin


     Python   SQL   Java   php   Perl 
     game development   web development   internet   *nix   graphics   hardware 
     telecommunications   C++ 
     Flash   Active Directory   Windows