PublishObject.Publish Error
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 |
PublishObject.Publish Error
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.A ctivate .Publish (True) End If End With Next objPubOb End Sub |
All times are GMT +1. The time now is 10:59 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com