Problem with publishing MHT files from Excel 2010 quickly and in volume


  • I have a VB powered Excel application that on a daily basis needs to process a high volume of portfolio risk data and save the output into hundreds of MHTML files so users can access them via a website.  The code below seems to be working fine but a few of the published MHTML files for each processing cycle (3 - 5 files out of 400 files ) have incomplete names that prevent them from being opened.  If I rename each file manually  with the correct extension it opens fine.  Cycle 97 and 98 are examples of the problem.  You can see from the time stamps how fast these files are being published.

    I tried adding a delay timer in my code to space the publishing cycles by two or three sconds and that helped reduce but not eliminate the problem.

    My suspicion is that the publishing process cannot keep up with how fast the data is being processed and exported but I am not 100% sure about that.  I have seen other forum posts on the web where others have posted questions about cases of incomplete MHT files but I could not find any of the corresponding answers.  I used to previously publish the output to PDF file where I did not have this problem but the process took two hours instead of ten minutes which was not commercially practical.

    Has anyone else had this problem?  Any ideas for a solution?  Below is my VB MHTML export code.  Is there a flag that I can add in the code to confirm the MHT file has been saved successfully before moving on to the next one?  The timer delay that I am using now is a crude tool that adds minutes to the processing time and does not 100% solve the problem.

    Thanks in advance for your feedback.


    Sub SaveSheetToWebPageStress()
    ' SaveSheetToWebPage Macro
            If Range("cycle_block_print_flag").Value = "stop" Then
                Exit Sub
            End If
        Range("file_save_type_flag") = 2
        On Error Resume Next
        FileToSave = Range("control!scenario_file_save_path").Value & ".mht"
        Kill FileToSave       ' Delete any old file before saving a new one.
        With ActiveWorkbook.PublishObjects.Add(xlSourceSheet, _
            FileToSave, "Autoprocess Stress", "", _
            xlHtmlStatic, "", "")
            .Publish (True)
            .AutoRepublish = False
        End With
        newHour = Hour(Now())
        newMinute = Minute(Now())
        newSecond = Second(Now()) + 1
        waitTime = TimeSerial(newHour, newMinute, newSecond)
        Application.Wait waitTime
    End Sub

    Saturday, April 28, 2012 12:55 PM