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 a For Each loop in Visual Basic, as shown in Listing 5.18.

Listing 5.18. A VSTO Customization That Iterates over the Windows Collection

Public Class ThisWorkbook   Private Sub ThisWorkbook_Startup(ByVal sender As Object, _     ByVal e As System.EventArgs) Handles Me.Startup     Dim workbooks As Excel.Workbooks = Me.Application.Workbooks     Dim workbook1 As Excel.Workbook = workbooks.Add()     Dim workbook2 As Excel.Workbook = workbooks.Add()     Dim i As Integer     For i = 1 To 10       workbook1.NewWindow()       workbook2.NewWindow()     Next     Dim window As Excel.Window     For Each window In workbook1.Windows       MsgBox(String.Format("Workbook1 Window: {0}", _          window.Caption))     Next     For Each window In Me.Application.Windows       MsgBox(String.Format("Application Window: {0}", _          window.Caption))     Next   End Sub End Class 


Accessing a Window in the Collection

To access a Window in the Windows collection, you use a property called Item, which returns a Window. The Item property 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 passing in a 1-based index and by passing in the caption of the window. In the first use of Item, Item is specified explicitly (windows.Item(1)). In the second use, Item is omitted, as it is the default property of the Windows collection and Visual Basic knows how to call it if it is omitted (windows(caption)). Either usagespecifying Item explicitly or omitting Item and letting Visual Basic call it as the default property of the collectionis acceptable.

Listing 5.19. A VSTO Customization That Gets a Window from the Windows Collection Using Item

Public Class ThisWorkbook   Private Sub ThisWorkbook_Startup(ByVal sender As Object, _     ByVal e As System.EventArgs) Handles Me.Startup      Dim caption As String = ""      Dim windows As Excel.Windows = Me.Windows      If windows.Count >= 1 Then       Dim window As Excel.Window = windows.Item(1)       caption = window.Caption       MsgBox(caption)      End If      If Not String.IsNullOrEmpty(caption) Then       Dim window2 As Excel.Window = windows(caption)       Dim caption2 As String = window2.Caption       MsgBox(caption2)      End If   End Sub End Class 


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

XlArrangeStyle

Sets the style to use when arranging the windows: xlArrangeStyleCascade, xlArrangeStyleTiled, xlArrange-StyleHorizontal, xlArrange-StyleVertical.

ActiveWorkbook

Boolean

If set to true, arranges the windows only 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 also scroll.

SyncVertical

Object

If set to true, when one window associated with a workbook scrolls vertically, the other windows associated with the workbook 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 active window with. The window you want to compare with the 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 those of the workbook associated with the Windows collection.

Listing 5.20. A VSTO Customization That Arranges and Synchronizes Windows

Public Class ThisWorkbook   Private Sub ThisWorkbook_Startup(ByVal sender As Object, _     ByVal e As System.EventArgs) Handles Me.Startup     Dim workbooks As Excel.Workbooks = Me.Application.Workbooks     Dim workbook1 As Excel.Workbook = workbooks.Add()     Dim workbook2 As Excel.Workbook = workbooks.Add()     Dim workbook1Window As Excel.Window = workbook1.NewWindow()     workbook2.NewWindow()     workbook1.Activate()     workbook1.Windows.Arrange( _       Excel.XlArrangeStyle.xlArrangeStyleTiled, True, True, True)     MsgBox(String.Format( _       "Workbook {0} has its windows arranged tiled.", _       workbook1.Name))     workbook2.Activate()     Me.Application.Windows.CompareSideBySideWith( _       workbook1Window.Caption)     MsgBox(String.Format( _       "The windows {0} and {1} are synchronized", _       Me.Application.ActiveWindow.Caption, _       workbook1Window.Caption))   End Sub End Class 





Visual Studio Tools for Office(c) Using Visual Basic 2005 with Excel, Word, Outlook, and InfoPath
Visual Studio Tools for Office: Using Visual Basic 2005 with Excel, Word, Outlook, and InfoPath
ISBN: 0321411757
EAN: 2147483647
Year: N/A
Pages: 221

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