VBA - PublishObjects HTML Script

[BradH]

I am using the following VBA code to create about 70 Web pages after some other updates are made to the sheet using another script. This worked flawlessly for several weeks and now all the sudden it is taking up to 20 minutes just to generate the first page. My scheduler only allows 20 minutes before force closing the sheet so I don't know how long it would take to complete the entire script. It has gotten progressively worse though because when I first noticed it about 4 days ago, it was completing most of them in that same period of time.

I use this exact script in a couple other workbooks with less pages (7) and it takes about 20 seconds to complete. I have another workbook that is identical to this one that is doing the same thing.

I tried rebooting, I might upgrade to Office365 this weekend, but I am currently doing this in Excel 2016.

        Application.Sheets("5a1").Activate    With ActiveWorkbook.PublishObjects.Add(xlSourceRange, _
        "C:\Users\Brad\Documents\PR\BB\Schedules\Boys\5a1.htm", "5a1", "$A:$E", xlHtmlStatic, _
        "", "")
        .Publish (True)
    End With
    
    Application.Sheets("5a2").Activate
    With ActiveWorkbook.PublishObjects.Add(xlSourceRange, _
        "C:\Users\Brad\Documents\PR\BB\Schedules\Boys\5a2.htm", "5a2", "$A:$E", xlHtmlStatic, _
        "", "")
        .Publish (True)
      End With
    
    Application.Sheets("5a3").Activate
    With ActiveWorkbook.PublishObjects.Add(xlSourceRange, _
        "C:\Users\Brad\Documents\PR\BB\Schedules\Boys\5a3.htm", "5a3", "$A:$E", xlHtmlStatic, _
        "", "")
        .Publish (True)
      End With

This is not the entire code, this same code continues for all 70 pages being created.

Any thoughts on why this is no longer running as it did before? Is there a better way to do this? I used to use the republish on Save feature before with the smaller workbook but it was only 7 sheets, that would be hard to manage on 70.

 

[Leith Ross]

Hello BradH,

The problem is with PublishObjects. This is a collection object that is saved when the workbook is saved and closed. As you pointed it, it keeps growing. Here is macro that will delete the object once it is published.

Sub PublishWorksheets()


    Dim File    As String
    Dim index   As Long
    Dim PubObj  As Object
    Dim rngAddx As String
    Dim Wks     As Worksheet
    
        For index = 1 To 70
            Set Wks = Worksheets("5a" & index)
                File = "C:\Users\Brad\Documents\PR\BB\Schedules\Boys\" & Wks.Name & ".htm"
            Set rngAddx = "A1:E" & Wks.Cells(Rows.Count, "E").End(xlUp).row
            
            Set PubObj = ActiveWorkbook.PublishObjects.Add(xlSourceRange, File, Wks.Name, rngAddx, xlHtmlStatic)
                PubObj.Publish Create:=True
                PubObj.Delete
        Next index
        
End Sub

 

[BradH]

Hello BradH,

The problem is with PublishObjects. This is a collection object that is saved when the workbook is saved and closed. As you pointed it, it keeps growing. Here is macro that will delete the object once it is published.

Leith,

Thanks for helping out. I haven't tried this code, but I am pretty sure it won't work. It looks like you built this based off of Domenic's suggested script a few posts back. Unfortunately his assumption that all pages started with 5a was incorrect. The sheet names vary quite a bit. I am still using my code that I originally posted, minus the Activate rows.

With ActiveWorkbook.PublishObjects.Add(xlSourceRange, _
        "C:\Users\Brad\Documents\PR\BB\Schedules\Boys\BBB_5a1.htm", "5a1", "$A:$E", xlHtmlStatic, _
        "", "")
        .Publish (True)
    End With
    
    With ActiveWorkbook.PublishObjects.Add(xlSourceRange, _
        "C:\Users\Brad\Documents\PR\BB\Schedules\Boys\BBB_5a2.htm", "5a2", "$A:$E", xlHtmlStatic, _
        "", "")
        .Publish (True)
      End With
    
    With ActiveWorkbook.PublishObjects.Add(xlSourceRange, _
        "C:\Users\Brad\Documents\PR\BB\Schedules\Boys\BBB_5a3.htm", "5a3", "$A:$E", xlHtmlStatic, _
        "", "")
        .Publish (True)
      End With

And so on, but not always 5a. But always will have "BBB_". This is a change from original code as well.

 

[BradH]

I figured it out. Added this baby at the beginning of my script:

    With ActiveWorkbook.PublishObjects    
    .Delete
    End With