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.

The main procedures of class PublishObject are Delete, PublishObjects.Add and PublishObjects.Delete

Set

To use a PublishObject class variable it first needs to be instantiated, for example

Dim strFilename As String: strFilename = 
Dim wb As Workbook: Set wb = 
Dim pblobj As PublishObject
Set pblobj = wb.PublishObjects.Add(SourceType:=xlSourceAutoFilter, Filename:=strFilename, Title:="")

The following procedures can be used to set variables of type PublishObject: PublishObjects.Item, PublishObjects.Add and Workbook.PublishObjects

For Each

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

Dim wb As Workbook: Set wb = 
Dim pblobjPublishObject As PublishObject
For Each pblobjPublishObject In wb.PublishObjects
	
Next pblobjPublishObject

Methods

These are the main methods of the PublishObject class

Delete - Deletes the object.

PublishObjects.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.

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

PublishObjects.Delete - Deletes the object.

Properties

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.

Sub PublishToWeb() 
 
 With ActiveWorkbook.PublishObjects.Add( _ 
 SourceType:= xlSourceRange, _ 
 Filename:="C:\Work.htm", _ 
 Sheet:="Sheet1", _ 
 Source:="A1:D10", _ 
 HtmlType:=xlHtmlStatic, _ 
 DivID:="Book1.xls_130489") 
 .Publish 
 .AutoRepublish = True 
 End With 
 
End Sub

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.

Set objPO = ActiveWorkbook.PublishObjects.Add( _ 
 SourceType:=xlSourceRange, _ 
 Filename:="\\Server1\Reports\q198.htm", _ 
 Sheet:="Sheet1", _ 
 Source:="C2:D6", _ 
 HtmlType:=xlHtmlStatic) 
objPO.Publish 
strTargetDivID = objPO.DivID 
Open "\\Server1\Reports\q198.htm" For Input As #1 
Open "\\Server1\Reports\newq1.htm" For Output As #2 
While Not EOF(1) 
 Line Input #1, strFileLine 
 If InStr(strFileLine, strTargetDivID) > 0 And _ 
 InStr(strFileLine, " 0 Then 
 Print #2, "" 
 End If 
 Print #2, strFileLine 
Wend 
Close #2 
Close #1

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.

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.

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

Parent returns the parent object for the specified object. Read-only.

Sheet returns the sheet name for the specified PublishObject object.

blnSheetFound = False 
For Each objPO In Workbooks(1).PublishObjects 
 If objPO.HtmlType = xlHTMLStatic Then 
 strFirstPO = objPO.Sheet 
 blnSheetFound = True 
 Exit For 
 End If 
Next objPO 

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.

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

SourceType returns an XlSourceType value that represents the type of item being published.

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

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

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

PublishObjects.Count returns a Long value that represents the number of objects in the collection.

PublishObjects.Item returns a single PublishObject object from the collection.

strTargetDivID = ActiveWorkbook.PublishObjects.Item(1).DivID 
Open "\\server1\reports\q198.htm" For Input As #1 
Open "\\server1\reports\newq1.htm" For Output As #2 
While Not EOF(1) 
 Line Input #1, strFileLine 
 If InStr(strFileLine, strTargetDivID) > 0 And _ 
 InStr(strFileLine, " 0 Then 
 Print #2, "" 
 End If 
 Print #2, strFileLine 
Wend 
Close #2 
Close #1

PublishObjects.Parent returns the parent object for the specified object. Read-only.