Class PublishObject (Excel VBA)

The class PublishObject represents an item in a workbook that has been saved to a webpage and can be refreshed according to values specified by the properties and methods of the PublishObject object. To use a PublishObject class variable it first needs to be instantiated, for example


Dim pblobj as PublishObject
Set pblobj = ActiveWorkbook.PublishObjects(Index:=1)

For Each

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


blnChartFound = False 
For Each objPO In Workbooks(1).PublishObjects 
 If objPO.SourceType = xlSourceChart Then 
 strFirstPO = objPO.Source 
 blnChartFound = True 
 Exit For 
 End If 
Next objPO

AutoRepublish

When a workbook is saved, Microsoft Excel determines if any item in the PublishObjects collection has the AutoRepublish property set to True, and if so, republishes it. The default value is False.


ActiveWorkbook.PublishObjects(1).AutoRepublish = True

Delete

Deletes the object.


ActiveWorkbook.PublishObjects(1).Delete

DivID

Returns the unique identifier used for identifying an HTML

tag on a webpage. The tag is associated with an item in a document that you have saved to a webpage. An item can be an entire workbook, a worksheet, a selected print range, an AutoFilter range, a range of cells, a chart, a PivotTable report, or a query table.


Dim strDivID As String
strDivID = ActiveWorkbook.PublishObjects(1).DivID

Filename

Returns or sets the URL (on the intranet or the web) or the path (local or network) to the location where the specified source object was saved.

The FileName property generates an error if a folder in the specified path doesn't exist.


ActiveWorkbook.PublishObjects(1).FileName = _ 
 "\\Server2\Q1\StockReport.htm"

HtmlType

Returns or sets the type of HTML generated by Microsoft Excel when you save the specified item to a webpage. Can be one of the XlHtmlType constants specifying whether the item is static or interactive on the webpage. The default value is xlHtmlStatic.


ActiveWorkbook.PublishObjects(1).HtmlType = xlHtmlStatic

Publish

Saves an item or a collection of items in a document to a webpage.

The FileName property returns or sets the location and name of the HTML file.

Publish (Create)

Create: If the HTML file exists, setting this argument to True replaces the file, and setting this argument to False inserts the item or items at the end of the file. If the file does not exist, the file is created regardless of the value of the Create argument.


With ActiveWorkbook.PublishObjects.Add(xlSourceRange, _ 
 "\\Server1\sharedfolder\Stockreport.htm", "First Quarter", _ 
 "$D$5:$D$9", xlHtmlStatic, "Book2_25082", "") 
 .Publish (True) 
 .AutoRepublish = True 
End With

Sheet

Returns the sheet name for the specified PublishObject object.


Dim strSheet As String
strSheet = ActiveWorkbook.PublishObjects(1).Sheet

Source

Returns a value that represents the unique name that identifies items that have a SourceType property value of xlSourceRange, xlSourceChart, xlSourcePrintArea, xlSourceAutoFilter, xlSourcePivotTable, or xlSourceQuery.

If the SourceType property is set to xlSourceRange, this property returns a range, which can be a defined name. If the SourceType property is set to xlSourceChart, xlSourcePivotTable, or xlSourceQuery, this property returns the name of the object, such as a chart name, a PivotTable report name, or a query table name.


Dim strSource As String
strSource = ActiveWorkbook.PublishObjects(1).Source

SourceType

Returns an XlSourceType value that represents the type of item being published. Possible return values are xlSourceAutoFilter - An AutoFilter range, xlSourceChart - A chart, xlSourcePivotTable - A PivotTable report, xlSourcePrintArea - A range of cells selected for printing, xlSourceQuery - A query table (external data range), xlSourceRange - A range of cells, xlSourceSheet - An entire worksheet, xlSourceWorkbook - A workbook.


Dim xstSourceType As XlSourceType
xstSourceType = ActiveWorkbook.PublishObjects(1).SourceType

Title

Returns or sets the title of the webpage when the document is saved as a webpage.

The title is usually displayed in the window title bar when the document is viewed in the web browser.


Workbooks(1).PublishObjects(1).Title = "Sales Forecast"