Listing Using the Workbooks Collection

Public Sub WorkbookDemo() ' Holds the output data. Dim Output As String

' Get the test workbook. Dim CurrBook As Workbook

Set CurrBook =


' Get the workbook name and location.

Output = "Name: " + CurrBook.Name + vbCrLf + _

"Full Name: " + CurrBook.FullName + vbCrLf + _

"Path: " + CurrBook.Path + vbCrLf + vbCrLf

' Holds the current sheet. Dim CurrSheet As Worksheet

' Look for every sheet.

Output = "Worksheet List:" + vbCrLf

For Each CurrSheet In CurrBook.Worksheets

Output = Output + CurrSheet.Name + vbCrLf Next

' Holds the current chart. Dim CurrChart As Chart

' Look for every chart.

Output = Output + vbCrLf + "Chart List:" + vbCrLf For Each CurrChart In CurrBook.Charts

Output = Output + CurrChart.Name + vbCrLf Next

' Display the output.

MsgBox Output, vblnformation Or vbOKOnly, "Object List" End Sub

The code begins by using the Application.Workbooks collection to retrieve a single Workbook object. Notice that you must use the full name of the Excel file, including the file extension, as an index into the collection. The resulting Workbook object contains the name and path information for the document. It also contains settings such as the summary information. You can use this object to control windows and add new main elements, such as a worksheet.

After the code has access to the workbook, it uses the CurrBook object to access the list of worksheets. As usual, the code relies on a For Each...Next statement. You can also use an index to access individual worksheets in your code. The Worksheet, CurrSheet, contains properties and methods for manipulating any data that the Worksheet contains, including embedded objects, such as charts or even pictures. Every worksheet appears in the CurrBook object list by its object name (not the friendly name that you give it), so you can access them without using the Worksheets collection.

Unlike worksheets, only independent charts appear as part of CurrBook. You use the same technique to access a Chart object as a Worksheet object. The only difference is that you must use the Charts collection. Note that chart names appear in the list of objects presented by CurrBook, so you can also access the chart directly as an object without using the Charts collection. Figure 14-1 shows typical output from this program.

Figure 14-1:

Retrieving a list of worksheets and charts.

Figure 14-1:

Retrieving a list of worksheets and charts.

Although you must use the object name to access a worksheet or chart directly in CurrBook, the name property used for a collection is actually the friendly name. It's easy to become confused. The Project Explorer window always lists both the friendly name and the object name, so always look to it for guidance when it's unclear which name you should use.

0 0

Post a comment