Working with the Windows Collections

The Application.Windows property returns a Windows collection that lets you iterate and access all the windows that are open in Excel. Similarly, the Workbook.Windows property lets you access windows that are associated with a particular workbook. These collections provide methods to arrange the open windows. Windows collections do not have a method to add a new window. Instead, you must call the Workbook.NewWindow method.

Iterating over the Open Windows

The Windows collection has a GetEnumerator method that allows it to be iterated over using the foreach keyword in C#, as shown in Listing 5-18.

Listing 5-18. A VSTO Customization That Iterates over the Windows Collection

private void ThisWorkbook_Startup(object sender, EventArgs e)
{
 Excel.Workbooks workbooks = this.Application.Workbooks;

 Excel.Workbook workbook1 = workbooks.Add(missing);
 Excel.Workbook workbook2 = workbooks.Add(missing);

 for (int i = 0; i < 10; i++)
 {
 workbook1.NewWindow();
 workbook2.NewWindow();
 }

 foreach (Excel.Window window in workbook1.Windows)
 {
 MessageBox.Show(String.Format(
 "Workbook1 Window: {0}", window.Caption));
 }

 foreach (Excel.Window window in this.Application.Windows)
 {
 MessageBox.Show(String.Format(
 "Application Window: {0}", window.Caption));
 }
}

 

Accessing a Window in the Collection

To access a Window in the Windows collection, you use a method called get_Item, which returns a Window. The get_Item method takes an Index parameter that is of type object. You can pass a string representing the caption of the Window or you can pass a 1-based index into the Windows collection. You can check how many items are in a given collection by using the Count property. Listing 5-19 shows both getting a window by a 1-based index and by passing in the caption of the window.

Listing 5-19. A VSTO Customization That Gets a Window from the Windows Collection Using get_Item

private void ThisWorkbook_Startup(object sender, EventArgs e)
{
 string caption = "";
 Excel.Windows windows = this.Windows;

 if (windows.Count >= 1)
 {
 Excel.Window window = windows.get_Item(1);
 caption = window.Caption as string;
 MessageBox.Show(caption);
 }

 if (!String.IsNullOrEmpty(caption))
 {
 Excel.Window window2 = windows.get_Item(caption);
 string caption2 = window2.Caption as string;
 MessageBox.Show(caption2);
 }
}

 

Arranging Windows

Excel has various ways of arranging windows and synchronizing those windows so that when one window scrolls, the others scroll as well. The Arrange method lets you arrange a collection of windows as tiled, horizontal, vertical, or cascaded. This method also lets you synchronize two or more windows that are showing the same workbook so that when one window scrolls, the other windows scroll the same amount. Table 5-11 shows the optional parameters passed to the Arrange method.

Table 5-11. Optional Parameters for the Arrange Method

Property Name

Type

What It Does

ArrangeStyle

XlArrange-Style

Sets the style to use when arranging the windows: xlArrangeStyleCascade, xlArrangeStyleTiled, xlArrangeStyleHorizontal, xlArrangeStyleVertical.

ActiveWorkbook

bool

If set to TRue, only arranges the windows for the active workbook. If set to false, arranges all open windows.

SyncHorizontal

object

If set to true, when one window associated with a workbook scrolls horizontally, the other windows associated with the workbook will also scroll.

SyncVertical

object

If set to true, when one window associated with a workbook scrolls vertically, the other windows associated with the workbook will also scroll.

The CompareSideBySideWith method allows you to synchronize the scrolling of two windows showing the same workbook or two windows showing different workbooks. This method takes a string that represents the caption of the window to compare the currently active window with. The window you want to compare to the currently active window must be a member of the Windows collection you are usingso to be safe, you should use the Application.Windows collection because it contains all open windows.

As Listing 5-20 shows, it is important to activate the workbook whose windows you want to arrange. If you do not do this, the windows of the active workbook will be arranged rather than the workbook associated with the Windows collection. Listing 5-20 also illustrates the issue where the Activate method and the Activate event collide on the Workbook object. To get the compiler to not complain and IntelliSense to work, we cast the Workbook to an Excel._Workbook interface to let the compiler know we want the method and not the event.

Listing 5-20. A VSTO Customization That Arranges and Synchronizes Windows

private void ThisWorkbook_Startup(object sender, EventArgs e)
{
 Excel.Workbooks workbooks = this.Application.Workbooks;

 Excel.Workbook workbook1 = workbooks.Add(missing);
 Excel.Workbook workbook2 = workbooks.Add(missing);

 Excel.Window workbook1Window = workbook1.NewWindow();
 workbook2.NewWindow();

 ((Excel._Workbook)workbook1).Activate();

 workbook1.Windows.Arrange(
 Excel.XlArrangeStyle.xlArrangeStyleTiled,
 true, true, true);

 MessageBox.Show(String.Format(
 "Workbook {0} has its windows arranged tiled.",
 workbook1.Name));

 ((Excel._Workbook)workbook2).Activate();

 this.Application.Windows.CompareSideBySideWith(
 workbook1Window.Caption);

 MessageBox.Show(String.Format(
 "The windows {0} and {1} are synchronized",
 this.Application.ActiveWindow.Caption,
 workbook1Window.Caption));
}



Part One. An Introduction to VSTO

An Introduction to Office Programming

Introduction to Office Solutions

Part Two. Office Programming in .NET

Programming Excel

Working with Excel Events

Working with Excel Objects

Programming Word

Working with Word Events

Working with Word Objects

Programming Outlook

Working with Outlook Events

Working with Outlook Objects

Introduction to InfoPath

Part Three. Office Programming in VSTO

The VSTO Programming Model

Using Windows Forms in VSTO

Working with Actions Pane

Working with Smart Tags in VSTO

VSTO Data Programming

Server Data Scenarios

.NET Code Security

Deployment

Part Four. Advanced Office Programming

Working with XML in Excel

Working with XML in Word

Developing COM Add-Ins for Word and Excel

Creating Outlook Add-Ins with VSTO



Visual Studio Tools for Office(c) Using C# with Excel, Word, Outlook, and InfoPath
Visual Studio Tools for Office(c) Using C# with Excel, Word, Outlook, and InfoPath
ISBN: 321334884
EAN: N/A
Year: N/A
Pages: 214

Flylib.com © 2008-2020.
If you may any questions please contact us: flylib@qtcs.net