Script to save as web page but remain in workbook

[xphile2]

I have this vba script (see line below with --->) that publishes your workbook to a single file web page (*.mht). I had problems with the Activeworkbook.PublishObjects command below a little bit. My first question is why wouldn't "ActiveWorkBook.PublishObjects(1)" work instead? 2nd question is where does the "_7235" come from at the end of my file name? I found it by manually performing the action and recording it as a macro and using it at the end like you see there to get the script to work. My feeling is that number may change but when and how often? Can't I assign that number as a variant to a string and use it if it does change? Kind of lost in this part of my code. Thanks in advance


Private Sub CommandButton1_Click()

Set fs = CreateObject("Scripting.FileSystemObject")
fn = Replace(ActiveWorkbook.Name, ".", "-")
fn = Replace(fn, "-xls", ".xls")
savefn = fn
bfn = fs.getbasename(fn)


fileSaveName = Application.GetSaveAsFilename( _
InitialFileName:=fn, _
fileFilter:="Single File Web Page (*.mht), *.mhtl", _
Title:="Publish as Single File Web Page")


If fileSaveName <> False Then
Range("Print_Area").Select

With ActiveWorkbook
With .WebOptions
.RelyOnCSS = False
.RelyOnVML = False
End With

---> With ActiveWorkbook.PublishObjects("Infrastructure Alternative Solution Cost Comparison Tool v5.7_7235")
.Filename = fileSaveName
.HtmlType = xlHtmlStatic
.Publish (True)
.AutoRepublish = False
End With


End With

Range("a1").Select

End If

End Sub

 

[combo]

(1) You can refer to PublishObject either by its number in PublishObjects collection or DivID, associated with <DIV> tag. PublishObjects(1) should work if its DivID="Infrastructure...v5.7_7235", otherwise, if you have more previously published items stored in workbook, the index can be different from 1.
(2) PublishObjects Collection is saved with the workbook, DivIDs are read-only strings, consisting of description and number, so the number should be fixed as long as you do not delete the item in previously published list, by code or manually.

To avoid accidental delete of PublishObject, you could add new in your code, set its properties, publish and delete. You could also use more stable reference than ActiveWorkbook (ThisWorkbook if you publish a part of workbook that runs the code), there is no need to select "Print_Area".

combo

 

[xphile2]

This helps, thank you. I have tried using PublishObjects(1) and I get a 1004 error. I tried using 0, 2, 3, 4 as well with no success. I am glad the number is static but when is it assigned exactly? Can I set it to what I want? I seem to find these thing out by accident but I feel I am missing something in terms of understanding of the whole Object thing. I guess in my mind I have this mystical thing called ObjectCollections but am not quite sure how to manipulate it exactly. How are items added into this bucket and how can I Identify them or force their identity so I know what they are?
Can I change the DivID? How does one delete a publishobject?

Big thank you for the help.

 

[combo]

PublishObjects collection is a collection of workbook's published items currently stored in a workbook. Assuming that the code is in the same workbook, you can count the items:
MsgBox ThisWorkbook.PublishObjects.Count
If the result>0, you can use any of objects without creating a new one.

You can access each object in this collection with standard loop: For ... Next:
Dim oPO as PublishObject
For Each oPO in ThisWorkbook.PublishObjects
....
Next
or:
For Each i=1 To ThisWorkbook.PublishObjects.Count
set oPO=Thisworkbook.PublishObjects(i)
....
Next
With oPO, within the loop, you can test any property or method.

At least in excel xp, when you try to save a workbook as web page, you can click 'Publish' button in the SaveAs dialog. In the 'select' drop-down, one can access previously saved items. This is current PublishObjects collection. Items can be manually deleted, in that case the collection is empty.
You can also delete and add PublishObject to a workbook by code. See the help file for details.

As for the 1004 error, there may be a lot of reasons, just do standard debugging. Do you have any PublishObject in the collection? What is the active workbook? Do you have range "Print_Area" set? Can you select "a1"? What are DivIDs and other properties of your PublishObjects? Clear your code. In any procedure set reference to a workbook and assign to it a workbook you work with. Put a breakpoint anywhere inside or run code step by step, display 'Locals' window and see what happens with PublishObjects.

And, static, is rather DivID than number in collection, anyway you can create PublishObject from scratch.

combo