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.

 

[John_w]

One idea. You're saving whole columns (A:E), so instead try saving A1 to the last populated row in column E:

    Dim lastRow As Long
    
    Application.Sheets("5a2").Activate
    lastRow = Cells(Rows.Count, "E").End(xlUp).Row
    With ActiveWorkbook.PublishObjects.Add(xlSourceRange, _
        "C:\Users\Brad\Documents\PR\BB\Schedules\Boys\5a2.htm", "5a2", "$A$1:$E$" & lastRow, xlHtmlStatic, _
        "", "")
        .Publish (True)
    End With

 

[BradH]

One idea. You're saving whole columns (A:E), so instead try saving A1 to the last populated row in column E:

John, I did think of that, I made it $A$1:$E$400 because not all sheets have the same number of rows, and these rows can vary from day to day when I run updates. I will however test this theory by using the exact numbers for the first couple sheets and then run in Debug to see if it makes a difference.

 

[BradH]

I tried that, it didnt work. Anyone have any ideas? Is there another way to accomplish this?

 

[Domenic]

Just like John, I thought the whole column reference might be a problem as well. But since it turned out not to be the case, I opened a new workbook, named a worksheet 5a1, and ran the first part of your code. It ran successfully. The only change I made to your code was the path. So check to make sure that the path is a valid one. For example, what do you get if you enter the following line in the Immediate Window (Ctrl+G), and press ENTER?

? Dir("C:\Users\Brad\Documents\PR\BB\Schedules\Boys", vbDirectory)

 

[BradH]

When I hit enter it returns "Boys". I assume that means its valid. I really didn't think that was the problem anyway because up until now, it was publishing some of the sheets. At this point it is not publishing anything...it just sits until I force close it.

So I thought this might mean something on the sheet I am trying to save is causing the problem, I do have some conditional formatting on it. So I cleared the sheet, removed all conditional formatting, went back to the script and stepped into it, it stops responding at the .Publish (True) line and doesn't create the page.

I use this for Basketball schedules, I have one for Soccer, one for Football and one for Baseball, all with identical scripts to create the HTML pages. I ran the baseball one just to test and see if it was an excel issue, it ran perfectly. I chose the baseball because it has nearly the same number of pages.

I have an identical sheet for Girls Basketball and it publishes, but pretty slow, and since I only allow about 20 minutes for it to run before it gets cancelled, it only gets about half of them published. This one (Boys BB) used to do that, but now it doesn't even get the first page published. I am not sure if that has anything to do with my modifications or not.....

I modified the script so it was using xlSourceSheet instead of Range, and I tried this on another PC. I have tried it with Excel 2010, 2016 and now 365. At this point I don't know what to do other than to maybe rebuild this sheet from scratch, which will take a lot of time...its over 150 sheets. (Although about half of those are populated with a copy and paste script) But still about 10hrs plus of work.

I can share this workbook with anyone on OneDrive (if that is allowed) if nobody has any idea what may be causing this and would like to see it in action.

 

[BradH]

Does anyone know of another method of saving multiple sheets to html than what I have used above? This is frustrating, as it makes no sense why it doesn't work.

 

[Domenic]

I don't know whether this will help, but first I would avoid activating your sheets before publishing them. This should help a bit with efficiency, but not likely solve the issue by itself. Then I would try pausing the macro for a few seconds after each publish to allow the process to finish before continuing to the next one. So first copy/paste the following procedure that will be called to pause the macro for a few seconds...

Sub WaitAFewSeconds(SecondsToWait As Integer)
    Dim EndTime As Single
    EndTime = Timer + SecondsToWait
    While Timer < EndTime
        DoEvents
    Wend
End Sub

Then your code would look like this...

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

    WaitAFewSeconds SecondsToWait:=3
    
    'etc
    '
    '

By the way, if your sheets are named 5a1 through 5a70, your code could be re-written as follows...

    Dim strPath As String
    Dim i As Long
    
    Const SECONDS_TO_WAIT As Integer = 3
    
    strPath = "C:\Users\Brad\Documents\PR\BB\Schedules\Boys\"
    If Right(strPath, 1) <> "\" Then
        strPath = strPath & "\"
    End If


    For i = 1 To 70
        With ActiveWorkbook.PublishObjects.Add(xlSourceRange, strPath & "5a" & i & ".htm", "5a" & i, "$A:$E", xlHtmlStatic, "", "")
            .Publish (True)
        End With
        WaitAFewSeconds SecondsToWait:=3
    Next i

Actually, if you are not going to be setting any other properties or invoking any other methods for your publish object, you don't need to use the "With/End With" statement...

        ActiveWorkbook.PublishObjects.Add(xlSourceRange, strPath & "5a" & i & ".htm", "5a" & i, "$A:$E", xlHtmlStatic, "", "").Publish (True)

Does avoiding activating your sheets and pausing the macro help at all?

 

[BradH]

Does avoiding activating your sheets and pausing the macro help at all?

I had actually tried removing the activating prior to your response, but I had put it back. This seemed to do the trick, although I could tell it was still running slower than it should, but it was finishing the sheets in about 10 minutes. This lasted about a day. (I use scheduler to open this sheet every 3 hours and update) Now it won't even run all 65 html pages in an hour. The weirdest thing is, I have 2 different sheets that are nearly identical (One is boys basketball, one is girls basketball). They both produce 65 html pages. The boys one was running all 65 up until Friday, and now it is taking more than an hour and still not completing all pages.

I thought maybe this was a memory or PC resource issue, but I installed Windows 7 Professional, and MS Office, and a couple small programs that I use to manage what I do with these sheets. (SyncBack, Paralells, and an FTP program) This is all this PC is used for, nothing else. I opened the resource monitor while one of these sheets were running, and it was only running at about 30% memory and Processor was running about the same. The PC has an Intel Core I3-2120 CPU @ 3.30GHz with 8GB RAM, so i'm fairly certain it is not the PC.

My sheets are not all named 5a, so I couldn't use your other suggestion. I dread that in order to get this to work, I might have to manually publish and use the Autorepublish on save feature.

 

[BradH]

This problem has resurfaced again. Unfortunately, the only fix I can figure out is to go through the Previously published items on the Publish as Web Page Window and delete all the items. There are thousands for each page. I assume this is somehow causing the publish to slow and eventually stop working all together. It can take me a couple hours to clear this list because I can only select about 10-15 at a time because it locks up if I select a large amount.

Does anyone know:

A: How to clear this list using VBA or some other method?
B: How to prevent this list from accumulating? There are literally 1000+ for each page with the same range.