HTMLProjectItems Collection Object

                  Properties        

A collection of HTMLProjectItem objects that represent the HTML project items contained in the HTMLProject object.

Using the HTMLProjectItems Collection

Use the HTMLProjectItems property of the HTMLProject object to return the HTMLProjectItems collection. Use the Count property of the HTMLProjectItems collection to return the number of project items in the HTML project for the specified workbook. The following example returns the number of project items in the HTMLProjectItems collection for the active workbook.

Select Case _
    ActiveWorkbook.HTMLProject.HTMLProjectItems.Count
    Case 0
        strCount = "are no"
    Case 1
        strCount = "is 1"
    Case Else
        strCount = "are " & _
            ActiveWorkbook.HTMLProject _
                .HTMLProjectItems.Count
End Select

MsgBox ("There " & strCount & _
    " HTML project items in this workbook.")

Use the Item method of the HTMLProjectItems collection to return an individual project item in the Microsoft Script Editor. The following example returns the name of the first project item in the HTMLProjectItems collection for the active workbook.

MsgBox ("The first item is " & _
    ActiveWorkbook.HTMLProject _
    .HTMLProjectItems.Item(1).Name)

When you save a workbook containing a single worksheet as a Web page, Microsoft Excel saves the HTML in a single file. The Project Explorer in the Script Editor shows only one workbook project item, which represents both the workbook and the worksheet. However, when the workbook contains two or more worksheets, Excel saves the HTML in several files. The Project Explorer shows several project items, which represent the workbook, the worksheet tab control, and each worksheet. A workbook item or worksheet item has the same name as its corresponding workbook or worksheet. The following example inserts HTML at the bottom of each worksheet, but only if the active workbook contains more than one worksheet.

With ActiveWorkbook.HTMLProject
    If .HTMLProjectItems.Count > 1 Then
        For Each objHTMLItem In .HTMLProjectItems
            ' Loop through the workbooks and worksheets
            ' in the project.
            With objHTMLItem
                If .Name <> ActiveWorkbook.Name And _
                    .Name <> "Tab" Then
                    ' Insert the HTML only if it's not
                    ' a workbook or a tab.
                    Call InsertHTML(.Text, strHTML)
                End If
            End With
        Next objHTMLItem
    End If
End With