Guy Starbuck

This is another post that came from a question on StackOverflow – The idea is to load up a drop-down (I used a System.Windows.Forms.ComboBox) using values defined in an Excel document.

As usual, almost all of the Office Interop samples on the web are written in VB.NET – this makes sense, since it is a lot easier to program interop using VBNet and its support for optional parameters.  I got a great start from this forum post, I just simplified and converted it to C#.

using System;
using Excel = Microsoft.Office.Interop.Excel;

namespace CSharpClassLibrary
{
    public class ComboBoxLoader
    {
        // Define an object to pass to the API for missing parameters
        private static object missing = Type.Missing;

        public static void FillDropDown(ref System.Windows.Forms.ComboBox cbo)
        {
            Excel.Application excel = new Excel.Application();
            Excel.Workbook workbook;
            Excel.Worksheet worksheet;

            if (excel == null)
            {
                return;
            }
            else
            {
                // Set Excel to be hidden
                excel.Visible = false;

                // Open the workbook -- note the passing of the "missing" parameter
                // for everything except the file name
                workbook = excel.Workbooks.Open(@"c:\temp\test.xlsx", missing,
                    missing, missing, missing, missing, missing, missing,
                    missing, missing, missing, missing, missing, missing, missing);

                // This example assumes the first worksheet
                worksheet = workbook.Worksheets[1] as Excel.Worksheet;

                // This example assumes that there are five values, in cells
                // A1 to A5, that need to be loaded
                Excel.Range range;
                range = worksheet.get_Range("A1", "A5") as Excel.Range;

                // Loop through the cells in the Range and add their values to
                // the combo box
                foreach (Excel.Range cell in range.Cells)
                {
                    cbo.Items.Add(cell.Value2 as string);
                }

                // Clean up -- important with PIA Interop, as lots of
                // instances of Excel might be left around in memory
                workbook.Close(missing, missing, missing);
                excel.Quit();
            }
        }
    }
}

This is not something I would necessarily do (using Office Interop is a big thing to bite off in a project, and while I can see how feeding drop-down options from Excel could provide ease of use for non-technical admins, there are lots of less heavy-handed approaches), but it was interesting to figure out how to get the code working, and I thought it might help someone with other Excel automation tasks.

This is a little piece of functionality I stumbled across in Visual Studio 2008.

You can set up a “Trace Point”, which is like a breakpoint that doesn’t break, it logs a trace message to the debug trace listener.

Here’s how to set one up:

1: Set a breakpoint on the line where you want to log the trace

2: Right click the breakpoint, and select “When Hit…”

3: In the dialog, enter the message

Variables can be logged using “{}”, as specified in the dialog instructions.

Now the code shows a “diamond” instead of circle for the trace point:

4: Finally, run the code and see the trace messages in the Debug Output (in the Output window in Visual Studio)

This is really useful when you have some complicated looping or recursion that you need to debug; stepping through takes a lot of time and is confusing, you lose the forest for the trees.  But if you can spool out traces you can see exactly what has happened after the code is done running, and run it multiple times with different input to see how it changes.

A few notes:

• The trace message is logged before the line is evaluated – so in the sample code, I am tracing the value of “depth” before 1 is added to it.  If you want to get the value after execution of the line, set the trace to the line below it.
• These trace points are only as stable as breakpoints, as they are stored in your VS user options file.  If the code structure is modified outside of your editor (such as by another developer, then updated through source control), the positions can be thrown off and the traces will not work.  If you need stable tracing, use System.Diagnostics.Trace.Write in an explicit call in your code; trace points are really only for specific debugging efforts.

I hunted down a kind of a tricky error message today, and thought I'd write a quick post about it.

The Scenario:

I was doing some refactoring of a WPF app, encapsulating a section of one large-ish UserControl into a second UserControl. 

• Prior to the refactoring, the single combined UserControl was running directly as the StartupURI of a test harness app.xaml, which resulted in it being hosted in a NavigationWindow when run
• The new extracted User Control contained most of the business functionality of the containing control
• The original UserControl was converted into a Window, containing a Grid, which then contained the extracted UserControl

The Error:

In the designer and at runtime, the following dialog popped up one time:

As soon as the dialog was cleared, both the designer surface and the app worked as expected.

The Solution:

It turns out that I had two lines in the constructor of my original user control (for positioning of the hosting window), which I had moved to the constructor in the extracted UserControl, which referenced the parent cast as a Window:

((Window)Parent.Left = 0;
((Window)Parent).Top = 0;

Obvious -- the Designer and WPF at runtime both evaluated this code, and since the parent was no longer a Window, the cast didn't work.

I moved these lines into the constructor and this resolved the issue.

Summary:

I'm not surprised that the error was thrown, but I was interested in two aspects:

1. The Designer (Cider) evaluated the constructor code in the UserControl to the point that it threw casting errors on the Parent property.  I guess this is necessary to make sure everything renders correctly in the Designer surface, but I shudder to think of the amount of logic that Visual Studio must run to decide what code to run and what to ignore when loading up the Designer surface.
2. At runtime, WPF handled this error and generated its own error dialog, rather than throwing an unhandled exception.  This would've been a lot easier to debug if it had thrown like a normal unhandled exception in Visual Studio.

All in all, it was an interesting exercise.

I posted a quick answer a little while ago on StackOverflow, where the question was "What is the best way to display a status message in WPF?"

My answer:

You may want to consider doing a publish/subscribe ("Observer" pattern) -- define a "status changed" event on a base page, and create a custom control that sets up a delegate and event handler to listen for status updates.

Then you could drop the custom control on any page that inherits from the base, and it would automatically listen for and display status messages whenever the event is fired.

I thought this would be fun to prove out, so last night I threw together a sample app that implements this solution.  Here is a walkthrough.

Custom Event Args

The first thing that is needed is an "EventArgs" class to be able to pass message text through an event.  Here is the class declaration.  It's simply a class derived from "EventArgs" that defines a single string property to hold the message text:

using System;

namespace MessageAreaBlogDemo
{
    public class DisplayMessageEventArgs : EventArgs
    {
        // Constructor
        public DisplayMessageEventArgs(string s) { MessageText = s; }
        
        // Read-only property
        public String MessageText { get; private set; }
    }
}

Creating the Custom Base Class

Next, create a new class for the custom System.Windows.Window-derived base class, which I called "CustomWindowBase", that defines the event.  Here is the code:

using System.Windows;

namespace MessageAreaBlogDemo
{
    public class CustomWindowBase : Window
    {
        // Declare the event handler
        public delegate void DisplayMessageEventHandler(object sender, DisplayMessageEventArgs e);

        // Declare the event
        public event DisplayMessageEventHandler DisplayMessage;

        // Create a method that can be called to publish the message to any listeners
        public void PublishMessage(object sender, string text)
        {
            // Check if the event is null, meaning there are no Message Areas subscribed
            // to it.  If you don't check this, the app will throw an exception when it
            // tries to display a message on a form without a Message Area!
            if (DisplayMessage != null)
            {
                DisplayMessage(sender, new DisplayMessageEventArgs(text));
            }
        }
    }
}

Setting a Window to Inherit From the Custom Base Class

Changing the base class of a WPF Window requires changes in both the code and the xaml.  In the XAML, start by adding a new namespace for the local assembly, and changing the "Window" class definition to your new type.  Note that both files must be changed before you can build.

XAML before:

<Window x:Class="MessageAreaBlogDemo.Window1"
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
    Title="Window1" Height="300" Width="300">
    <Grid>
        
    </Grid>
</Window>

XAML After:

<local:CustomWindowBase x:Class="MessageAreaBlogDemo.Window1"
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
    xmlns:local="clr-namespace:MessageAreaBlogDemo"
    Title="Window1" Height="300" Width="300">
    <Grid>
        
    </Grid>
</local:CustomWindowBase>

Code for Window1.xaml.cs:

namespace MessageAreaBlogDemo
{
    /// <summary>
    /// Interaction logic for Window1.xaml
    /// </summary>
    
    // Modify to inherit from CustomWindowBase
    public partial class Window1 : CustomWindowBase
    {
        public Window1()
        {
            InitializeComponent();
        }
    }
}

Creating the Message Area

Now you can create a new User Control to house the message area.  I just defined a simple user control that contains a grid that holds a label.  I set the color of the grid so that it jumps out on the page.  Also, note the "Loaded" event handler to the control declaration in XAML -- this is used to fire the event that registers the control with the parent Window to receive messages.

<UserControl x:Class="MessageAreaBlogDemo.MessageArea"
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
    Height="30" Width="300">
    <Grid Background="AliceBlue" >
        <Label Name="labelMessage">Message Goes Here</Label>
    </Grid>
</UserControl>

And here is the code behind:

using System.Windows;
using System.Windows.Controls;

namespace MessageAreaBlogDemo
{
    /// <summary>
    /// Interaction logic for MessageArea.xaml
    /// </summary>
    public partial class MessageArea : UserControl
    {
        public MessageArea()
        {
            InitializeComponent();
        }

        // Event that fires after the Window and all controls have been loaded and
        // are about to be rendered
        private void UserControl_Loaded(object sender, System.Windows.RoutedEventArgs e)
        {
            // Get the containing Window using the static "Window.GetWindow" method,
            // then cast it to a CustomWindowBase
            CustomWindowBase parent = (CustomWindowBase)Window.GetWindow(this);

            // Register with the event delegate
            parent.DisplayMessage += new CustomWindowBase.DisplayMessageEventHandler(parent_DisplayMessage);
        }

        // Event handler, called whenever the parent Window raises the custom event
        void parent_DisplayMessage(object sender, DisplayMessageEventArgs e)
        {
            // Display the text in the message area
            this.labelMessage.Content = e.MessageText;
        }
    }
}

Put the Controls on the Form

The last step is to just put the MessageArea on the form, and fire the event to display messages on it.  Without going into how to add the control to the toolbox, we can just add the controls in the xaml editor like so:

<local:CustomWindowBase x:Class="MessageAreaBlogDemo.Window1"
    xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
    xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
    xmlns:local="clr-namespace:MessageAreaBlogDemo"
    Title="Window1" Height="300" Width="300">
    <Grid>
        <Button Height="23" HorizontalAlignment="Left" Margin="12,12,0,0" Name="button1"
                VerticalAlignment="Top" Width="75" Click="button1_Click">Log Message</Button>
        <local:MessageArea VerticalAlignment="Bottom" Width="200"/>
    </Grid>
</local:CustomWindowBase>

Now, in the code behind for the Window, simply have the "Click" handler fire the event with an appropriate message.  I just have it display the date/time so that  you can see the seconds refresh to verify that it is working:

private void button1_Click(object sender, System.Windows.RoutedEventArgs e)
{
    this.PublishMessage(sender, "Date/Time:" + System.DateTime.Now.ToString());
}

Conclusions

Any number of message areas could be added to a Window, and they will all register with the delegate and display the message when the event fires; likewise, windows that inherit from the custom base class don't have to have a message area, the event just won't fire.

I just wanted to mention a great new site for programmers called Stack Overflow that just went into public beta.

It's a mix of a technical question forum and a wiki, focused on software development and architecture, and has a few "hooks" that pull you in -- as you ask and answer questions, you get "reputation", which unlocks more privileges (such as editing the wiki).  You also earn "badges" for doing things like asking a popular question or giving the best answer. 

It ends up being really addictive and also valuable, people are rushing to provide the best answer the fastest to questions you ask, so if you have a focused technical question, it's a great way to get multiple answers fast.  I asked a question on Word 2007 automation, and had two great answers with code samples within 15 minutes.

Anyhow, I say check it out.

Ok, I've been bitten by this twice now, so here is the error and solution:

Error:

TITLE: Microsoft SQL Server Management Studio
------------------------------

Database diagram support objects cannot be installed because this database does not have a valid owner.  To continue, first use the Files page of the Database Properties dialog box or the ALTER AUTHORIZATION statement to set the database owner to a valid login, then add the database diagram support objects.

Solution:

In my case, it's because my domain login is set as the database owner but I am working remotely.  My PC can't validate my login against the domain, so SQL Server errors out with "invalid owner".

The easy solution is to change ownership of the database to sa, using sp_changedbowner.

I had a couple of great questions from my post on automated search-and-replace in Word documents:

Hello

I just want to know what I have to change in order to search in the footer of the page only ?

My goal is to update the version of a document which is written in the footer. I don't need to search in the core of the document.

Thanks for your great code !

Longin Benoit

There is an enumeration that determines the type of each Range, Microsoft.Office.Interop.Word.WdStoryType.  The values available that have to do with footers are:

• wdPrimaryFooterStory -- main footer story range
• wdFirstPageFooterStory -- override for the first page footer
• wdEvenPagesFooterStory -- override for even numbered pages (odd would inherit wdPrimaryFooterStory)

So to run the code in my previous post for the wdPrimaryFooterStory, you could do something like this (omitting the setup and cleanup code):

// Get the primary footer range
Word.Range primaryFooterRange = doc.StoryRanges[Word.WdStoryType.wdPrimaryFooterStory];

// Set the find and replace text (I set a hard-coded token of "Document Version: 2.0" as the starting version,
// but this could be initialized to your previous version programmatically)
primaryFooterRange.Find.Text = "Document Version: 2.0";
primaryFooterRange.Find.Replacement.Text = "Document Version: 2.1";

// Set the option to wdReplace.wdReplaceOne (assuming you only have the version appearing once in the footer)
object replaceAll = Word.WdReplace.wdReplaceOne;
primaryFooterRange.Find.Execute(ref missing, ref missing, ref missing,
                                ref missing, ref missing, ref missing, ref missing,
                                ref missing, ref missing, ref missing, ref replaceAll,
                                ref missing, ref missing, ref missing, ref missing);

Note that f you have differing alternating-page footers, you will have to run this twice, once on the wdPrimaryFooterStory, and a second time on the wdEvenPagesFooterStory.

Second Question:

I've improvised a little into your code as I need to implement an app  to find the occurence of a certain word .

However I don't how to find the Occurence number !!

That's my Code

public void Search(Document docObj)

       {

           int Counter = 0;

           foreach (Range tmpRange in docObj.StoryRanges)

           {

tmpRange.Find.Text = "Blob";

tmpRange.Find.Wrap = FindWrap.wdFindContinue;

               // Here should kind of property or method that should check if "Blob" 's been found then Counter++;

}

           System.Windows.Forms.MessageBox.Show(Counter.ToString());

}

Thanks .

Fatla

This one is a little trickier -- I wasn't able to find any property after the "Find" that tells you how many were found.  I did find a good hack on this website where they suggest doing a count of characters before and after, divided by the difference in length of the two strings, to get a count of replacements (by Bart Verbeek and Dave Rado):

http://word.mvps.org/FAQs/MacrosVBA/GetNoOfReplacements.htm

For situations where the search and replace strings are the same length, they suggest replacing with an additional "#" character, getting the count, and then doing another replace to strip out the "#".  This seems like a lot of work, but I wasn't able to find anything in the API to do this, so it's probably a decent option if you abstract it into a method.  Anyone who has any other ideas, please let me know.

David Cumps has started a very nice series of posts on design patterns which I have been following -- well written and clear:

• Strategy Pattern
• Observer Pattern
• Observer/Event Pattern

A former coworker, Dan Sniderman, has published an article in Dot Net Developers Journal on Continuous Integration in TFS 2008 -- nice work Dan! 

I worked on an interesting problem last night and thought I'd post the code.  I'm working on a software conversion project which has a new requirements/use case structure, and I had a list of about 700 requirement numbers that each needed to be replaced with a new requirement number, throughout 20 Word documents that averaged 20 pages apiece.

Going through each document and doing 700 "Replace Alls" didn't sound like much fun, and there are lots more documents and requirements coming down the pike that will need this same operation done to them, so I embarked on a VSTO expedition.

I created a console app in Visual Studio to run the code, and the first thing I noticed is that the Office 12 (Office 2007) Primary Interop Assemblies were not registered on my PC.  A quick search came up with this Microsoft download that lets you install these to your GAC with an MSI.

Next, I found a great VB.Net code snippet in a Microsoft forum (it's the second post in the thread, from "Spotty") that gives the basic code needed to do this for a single file.

I would say that if you are going to do a lot of interop work, it may be worthwhile to use VB.Net; the support for optional parameters saves a lot of time.  But my initial conversion of Spotty's VB code looks like this:

Spotty's original VB.Net code:

Dim word As New Microsoft.Office.Interop.Word.Application
Dim doc As Microsoft.Office.Interop.Word.Document
Try
doc = word.Documents.Open("c:\test.doc")
doc.Activate()
Dim myStoryRange As Microsoft.Office.Interop.Word.Range
For Each myStoryRange In doc.StoryRanges
With myStoryRange.Find
.Text = "findme"
.Replacement.Text = "findyou"
.Wrap = Microsoft.Office.Interop.Word.WdFindWrap.wdFindContinue
.Execute(Replace:=Microsoft.Office.Interop.Word.WdReplace.wdReplaceAll)
End With
Next myStoryRange
doc.SaveAs("c:\test1.doc")
Catch ex As Exception
MessageBox.Show("Error accessing Word document.")
End Try

My conversion to C#:

(note: add a reference to Microsoft.Office.Interop.Word (version 12) and the Using statement below)

using Word = Microsoft.Office.Interop.Word;

        public static void DoSearchAndReplaceInWord()
        {
            // Create the Word application and declare a document
            Word.Application word = new Word.Application();
            Word.Document doc = new Word.Document();

            // Define an object to pass to the API for missing parameters
            object missing = System.Type.Missing;

            try
            {
                // Everything that goes to the interop must be an object
                object fileName = @"C:\myDocument.doc";

                // Open the Word document.
                // Pass the "missing" object defined above to all optional
                // parameters.  All parameters must be of type object,
                // and passed by reference.
                doc = word.Documents.Open(ref fileName,
                    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);

                // Activate the document
                doc.Activate();

                // Loop through the StoryRanges (sections of the Word doc)
                foreach (Word.Range tmpRange in doc.StoryRanges)
                {
                    // Set the text to find and replace
                    tmpRange.Find.Text = "findme";
                    tmpRange.Find.Replacement.Text = "findyou";

                    // Set the Find.Wrap property to continue (so it doesn't
                    // prompt the user or stop when it hits the end of
                    // the section)
                    tmpRange.Find.Wrap = Word.WdFindWrap.wdFindContinue;

                    // Declare an object to pass as a parameter that sets
                    // the Replace parameter to the "wdReplaceAll" enum
                    object replaceAll = Word.WdReplace.wdReplaceAll;

                    // Execute the Find and Replace -- notice that the
                    // 11th parameter is the "replaceAll" enum object
                    tmpRange.Find.Execute(ref missing, ref missing, ref missing,
                        ref missing, ref missing, ref missing, ref missing,
                        ref missing, ref missing, ref missing, ref replaceAll,
                        ref missing, ref missing, ref missing, ref missing);
                }

                // Save the changes
                doc.Save();

                // Close the doc and exit the app
                doc.Close(ref missing, ref missing, ref missing);
                word.Application.Quit(ref missing, ref missing, ref missing);
            }
            catch (Exception ex)
            {
                doc.Close(ref missing, ref missing, ref missing);
                word.Application.Quit(ref missing, ref missing, ref missing);
            }
        }

After this was up and running, setting up the data reader and looping though the directory to operate on all files was pretty straightforward -- the biggest tricks were declaring the "missing" object variable for Type.Missing, and adding the code to close the doc and exit the application.

If you set up a VSTO project, you get the "missing" object declared as a global variable, so you don't need to declare it.  But for stand-alone Word interop, I think this is pretty clean.

Okay, so Ctl-C, Ctl-X and Ctl-V are really the most powerful, but at the patterns & practices summit in Redmond a couple of years ago, Peter Provost wowed the room with a keyboard shortcut that many of us had never seen before, and which I have used nearly every day since then.

It's Shift-Alt and the arrow keys.  This lets you re-order text, bullets, or numbered lists.

So if you have a bulleted list or numbered list in Word or PowerPoint that you need to re-sort, this is a no-brainer -- get the cursor on the row you want to move (or select multiple rows to move together), hold down Shift-Alt, and drive those rows around with the arrow keys.  Left and Right arrows indent or promote the text in the list.

This also works for tables in Word and PowerPoint.  I've found it to be invaluable when doing BA work, it's a lot easier to re-organize bullets in presentations and outline numbered lists.

Give it a try!