Class PublishObjects (Excel VBA)

A collection of all PublishObject objects in the workbook. To use a PublishObjects class variable it first needs to be instantiated, for example

Dim pos as PublishObjects
Set pos = ActiveWorkbook.PublishObjects

For Each

Here is an example of processing the PublishObjects items in a collection.

Dim pblobj As PublishObject
For Each pblobj In ActiveWorkbook.PublishObjects
	
Next pblobj

Add

Creates an object that represents an item in a document saved to a webpage. Such objects facilitate subsequent updates to the webpage while automated changes are being made to the document in Microsoft Excel. Returns a PublishObject object.

Add (SourceType, Filename, Sheet, Source, HtmlType, DivID, Title)

With ActiveWorkbook.PublishObjects.Add(SourceType:=xlSourceRange, _ 
    Filename:="\\Server\Stockreport.htm", Sheet:="First Quarter", Source:="$G$3:$H$6", _ 
    HtmlType:=xlHtmlStatic, DivID:="Book1_4170") 
        .Publish (True) 
        .AutoRepublish = False 
End With

Arguments

The following arguments are required:

SourceType (XlSourceType) - The source type.

Possible values are

Filename (String) - The URL (on the intranet or the web) or path (local or network) to which the source object was saved.

Optional arguments

The following arguments are optional

Sheet - The name of the worksheet that was saved as a webpage

Source (XlSourceType) - A unique name used to identify items that have one of the following constants as their SourceType argument: xlSourceAutoFilter, xlSourceChart, xlSourcePivotTable, xlSourcePrintArea, xlSourceQuery, or xlSourceRange. If SourceType is xlSourceRange, Source specifies a range, which can be a defined name. If SourceType is xlSourceChart, xlSourcePivotTable, or xlSourceQuery, Source specifies the name of a chart, PivotTable report, or query table.

Possible values are

HtmlType (XlHtmlType) - Specifies whether the item is saved as an interactive Microsoft Office Web component or as static text and images. Can be one of the XlHTMLType constants: xlHtmlCalc, xlHtmlChart, xlHtmlList, or xlHtmlStatic.

DivID - The unique identifier used in the HTML DIV tag to identify the item on the webpage

Title (String) - The title of the webpage.

Count

Returns a Long value that represents the number of objects in the collection.

Dim lngCount As Long
lngCount = ActiveWorkbook.PublishObjects.Count

Delete

Deletes the object.

ActiveWorkbook.PublishObjects.Delete

Item

Returns a single PublishObject object from the collection.

Item (Index)

Index: The name or index number of the object.

Dim pblobjItem As PublishObject
Set pblobjItem = ActiveWorkbook.PublishObjects(Index:=1)

Publish

Saves a copy of the item or items in the spreadsheet that have been added to the PublishObjects collection to a webpage.

ActiveWorkbook.PublishObjects.Publish