Excel hangs while exporting excel data to html page

Question

With below code I'm trying to export excel data to html page but excel hangs. Any help please. 1st code used to work earlier, but now it doesn't. I've ensured that the same sheet is activated and sheet has data in 11 columns and 216 rows.

Sub PublishOnWeb1()
Dim objPub As Excel.PublishObject
Set objPub = ThisWorkbook.PublishObjects.Add( _
SourceType:=xlSourceSheet, _
Filename:="C:\SLED\SLED_Time_Teams.htm", Sheet:="Teams", _
HtmlType:=xlHtmlStatic, Title:="SLED Time Teamwise")
objPub.Publish True
End Sub


Sub PublishOnWeb2()

 Dim rng As Range
 Sheets("Teams").Activate
 Set rng = Sheets("Teams").UsedRange
 file1 = "C:\SLED\SLED_Time_Teams.htm"

    With ActiveWorkbook.PublishObjects.Add(SourceType:=xlSourceRange, Filename:=file1, Sheet:=rng.Worksheet.Name, Source:=rng.Address, HtmlType:=xlHtmlStatic)
   .Publish (True)
    End With
    MsgBox "Done"
End Sub

score 1 · Accepted Answer · answered Aug 03 '19 at 12:32

sheet has data in 11 columns and 216 rows.

This works for me.

There is no need to activate the sheet. I am not using .Activate/UsedRange Just using what is actually required. Check if this works for you? If your range varies then we can later find the last row and last column and then construct the range address.

Option Explicit

Sub Sample()
    Dim wb As Workbook
    Dim ws As String
    Dim rng As String
    Dim file As String

    Set wb = ThisWorkbook
    ws = "Teams"
    rng = "A1:K216"

    file = "C:\SLED\SLED_Time_Teams.htm"

    With wb.PublishObjects.Add(SourceType:=xlSourceRange, Filename:=file, _
                               Sheet:=ws, Source:=rng, HtmlType:=xlHtmlStatic)
        .Publish (True)
    End With

    MsgBox "Done"
End Sub

Yes, I just checked with other spreadsheet and it works fine. Seems something wrong with spreadsheet. Has it anything to do with autopublish ON or OFF? Yes I will have rows changing. — Experimenter, Aug 03 '19 at 12:50
To find the last row/column, see [THIS](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba/11169920#11169920). — Siddharth Rout, Aug 03 '19 at 12:55

Excel hangs while exporting excel data to html page

1 Answers1