PublishObject.Publish Error

[Adam Darcy]

I've written a routine to republish previously published ranges in an Excel
workbook as web pages. Basically, it changes the filename of the webpage to
use the current date:

Private Sub ExportHTML(ByVal dateEffective As Date)
Dim objPubOb As PublishObject
Dim strDate As String

strDate = Format$(dateEffective, "ddmmyy")

For Each objPubOb In ThisWorkbook.PublishObjects
With objPubOb
If .SourceType = xlSourceRange Then
.HtmlType = xlHtmlStatic
.Filename = Left(.Filename, Len(.Filename) - 10) & strDate &
".htm"
.Publish (True)
End If

End With

Next objPubOb

End Sub

This throws up a run-time error 1004: Method 'Publish' of object
'PublishObject' failed. Weirdly, if I debug, it then works. I've tried
trapping the error and calling the Publish method again, but that doesn't
work either.

I know I could create new PublishObjects, but they would have different
DivID numbers, which I would like to avoid.

Any suggestions would be much appreciated.

Adam

 

[Adam Darcy]

In case anyone else comes across this problem, I found a solution -
activating the sheet containing the range to be published:

Private Sub ExportHTML(ByVal dateEffective As Date)
Dim objPubOb As PublishObject
Dim strDate As String

strDate = Format$(dateEffective, "ddmmyy")

For Each objPubOb In ThisWorkbook.PublishObjects
With objPubOb
If .SourceType = xlSourceRange Then
.HtmlType = xlHtmlStatic
.Filename = Left(.Filename, Len(.Filename) - 10) & strDate &
".htm"
ThisWorkbook.Names(.Source).RefersToRange.Parent.Activate

.Publish (True)
End If

End With

Next objPubOb

End Sub