Deborah's Developer MindScape

Some of the collections in the Microsoft Office object models implement IEnumerable. The IEnumerable interface provides the ability to perform a for/each against the collection. With .NET 3.5, a Cast extension method of IEnumerable allows you to work with these collections using Linq.

Microsoft Word

For example, say you want to bind the set of open Word document names in a ComboBox.

First, set a reference to the desired version of the Microsoft Word Object Library from the COM tab of the Add Reference dialog. The resulting reference appears as Microsoft.Office.Interop.Word.

In C#:

// Add to the top of the code file
using Word = Microsoft.Office.Interop.Word;

// Add to a subroutine
Word.Application Wd;
Word.Document doc;
Word.Document doc2;
object missingValue = Missing.Value;

// Start Word and get Application object
Wd = new Word.Application();

// Define documents
doc = Wd.Documents.Add(ref missingValue,ref missingValue, 
                       ref missingValue,ref missingValue );
doc2 = Wd.Documents.Add(ref missingValue, ref missingValue, 
                        ref missingValue, ref missingValue);

// Use Linq to access the document names.
var query = from d in Wd.Documents.Cast<Word.Document>()
            select d.Name;
comboBox1.DataSource = query.ToList();

// Or use Lambda expressions
var query2 = Wd.Documents.Cast<Word.Document>().Select(d=> d.Name);
comboBox1.DataSource = query2.ToList();

// Close
doc.Close(ref missingValue, ref missingValue, ref missingValue);
doc = null;
doc2.Close(ref missingValue, ref missingValue, ref missingValue);
doc2 = null;
Wd.Quit(ref missingValue, ref missingValue, ref missingValue);

// Clean up
// NOTE: When in release mode, this does the trick
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect() ;

In VB:

' Add to the top of the code file
Imports Word = Microsoft.Office.Interop.Word

' Add to a subroutine
Dim Wd As Word.Application
Dim doc As Word.Document
Dim doc2 As Word.Document

' Start Word and get Application object
Wd = New Word.Application

' Define documents
doc = Wd.Documents.Add
doc2 = Wd.Documents.Add

' Use Linq to access the document names.
Dim query = From d In Wd.Documents.Cast(Of Word.Document)() _
            Select d.Name
ComboBox1.DataSource = query.ToList

' Or use Lambda expressions
Dim query2 = Wd.Documents.Cast(Of Word.Document) _
                    .Select(Function(d) d.Name)
ComboBox1.DataSource = query2.ToList

' Close
doc.Close()
doc = Nothing
doc2.Close()
doc2 = Nothing
Wd.Quit()

' Clean up
' NOTE: When in release mode, this does the trick
GC.WaitForPendingFinalizers()
GC.Collect()
GC.WaitForPendingFinalizers()
GC.Collect()

In both of these examples, the code starts Word, creates two Word documents, uses either Linq or a Lambda expression to define a query and then binds the resulting set of document names to a Combo Box.

Notice the missingValue variable in the C# code that is not in the VB code. VB supports default parameters, but C# does not. So any time a parameter is defined for a Word method, C# must provide it. VB will use the default parameter values.

NOTE: A new feature in C# 4.0 (Visual Studio 2010) allows for default parameters in C# as well, dramatically simplifying the C# code that interacts with Word or Excel.

As another example, the following code retrieves all of the words from the defined Word document.

In C#:

In VB:

Dim query = From w In doc.Words.Cast(Of Word.Range)() _
             Select w.Text
ComboBox1.DataSource = query3.ToList

This code retrieves all of the words in the document defined by the doc variable. Instead of selecting the list of words, you could use any Linq feature such as finding only a specific set of words that match a criteria or counting the number of occurrences of a given word.

Microsoft Excel

This technique works with Excel as well. Say you want to bind the list of spreadsheets in an Excel workbook.

First, set a reference to the desired version of the Microsoft Excel Object Library from the COM tab of the Add Reference dialog. The resulting reference appears as Microsoft.Office.Interop.Excel.

In C#:

// Add to the top of the code file
using Excel = Microsoft.Office.Interop.Excel;

// Add to a subroutine
Excel.Application oXL;
Excel.Workbook oWB;
Excel.Worksheet oSheet;

// Start Excel and get Application object.
oXL = new Excel.Application();

// Get a new workbook.
oWB = oXL.Workbooks.Add(Missing.Value);

// Get the active sheet and change its name
oSheet = (Excel.Worksheet)oWB.ActiveSheet ;
oSheet.Name = "Test";

// Use Linq to access the spreadsheet names.
var query = from s in oXL.Worksheets.Cast<Excel.Worksheet>()
            select s.Name;
comboBox1.DataSource = query.ToList();

// Close
oSheet = null;
oWB.Close(Missing.Value, Missing.Value, Missing.Value);
oWB = null;
oXL.Quit();

// Clean up
// NOTE: When in release mode, this does the trick
GC.WaitForPendingFinalizers();
GC.Collect();
GC.WaitForPendingFinalizers();
GC.Collect();

In VB:

' Add to the top of the code file
Imports Excel = Microsoft.Office.Interop.Excel

' Add to a subroutine
Dim oXL As Excel.Application
Dim oWB As Excel.Workbook
Dim oSheet As Excel.Worksheet

' Start Excel and get Application object.
oXL = New Excel.Application

' Get a new workbook.
oWB = oXL.Workbooks.Add

' Get the active sheet and change its name
oSheet = DirectCast(oWB.ActiveSheet, Excel.Worksheet)
oSheet.Name = "Test"

' Use Linq to access the spreadsheet names.
Dim query = From s In oXL.Worksheets.Cast(Of Excel.Worksheet)() _
            Select s.Name
ComboBox1.DataSource = query.ToList

' Or use Lambda expressions
Dim query2 = oXL.Worksheets.Cast(Of Excel.Worksheet) _
                            .Select(Function(s) s.Name)
ComboBox1.DataSource = query2.ToList

' Close
oSheet = Nothing
oWB.Close()
oWB = Nothing
oXL.Quit()

' Clean up
' NOTE: When in release mode, this does the trick
GC.WaitForPendingFinalizers()
GC.Collect()
GC.WaitForPendingFinalizers()
GC.Collect()

In both examples, the code starts Excel, changes the name of the active sheet, uses either Linq or a Lambda expression to define a query and then binds the resulting set of sheet names to a Combo Box.

Enjoy!

EDITED 11/16/09: Added information on setting the appropriate reference.