PublishObjects Run-time error 1004

[288enzo]

I'm trying to better understand how PublishObjects works and am getting an error. Application-defined or object-defined error.

Everything works fine until it gets to PublishOjects, that's when I get the error.

It looks okay when compared to PublishObjects.Add method (Excel)

Function RangetoHTML2(rng As Range)
    Dim TempFile As String
    Dim TempWB As Workbook
    
    TempFile = Environ$("temp") & "\" & Format(Now, "dd-mm-yy h-mm-ss") & ".htm"
    Set TempWB = Workbooks.Add(1)
    
    With TempWB.Sheets(1).Cells(1)
        rng.Copy
        .PasteSpecial Paste:=8
        .PasteSpecial xlPasteColumnWidths
        .PasteSpecial xlPasteValues
        .PasteSpecial xlPasteFormats
    End With
    
    With TempWB.PublishObjects.Add(SourceType:=xlSourceRange, _
        Filename:=TempFile, _
        Sheet:=TempWB.Sheets(1).Name, _
        Source:=TempWB.Sheets(1).UsedRange.Address, _
        HtmlType:=xlHtmlStatic)
        .Publish (True)
    End With
        
    Application.EnableEvents = False
    TempWB.Close savechanges:=False

    Set TempWB = Nothing
    
End Function

 

[RandomExceller01]

For any future visitors to this page, I had the same error, and it was due to having the "R1C1 Reference Style" checked. Looks like the xlSourceRange method wasn't jiving with the range defined earlier in the sub.

Unchecking the R1C1 reference style solved my issues.

Hope this helps.