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
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.
' SaveSheetToWebPage Macro
If Range("cycle_block_print_flag").Value = "stop" Then
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, "", "")
.AutoRepublish = False
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 1
waitTime = TimeSerial(newHour, newMinute, newSecond)
Microsoft is conducting an online survey to understand your opinion of the Technet Web site. If you choose to participate, the online survey will be presented to you when you leave the Technet Web site.