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

 

[288enzo]

Using Debug.Print I can see what looks to be correct with the structure.
C:\Users\krichmp\AppData\Local\Temp\1\20-09-22 13-25-48.htm Sheet1 $A$1:$B$2
I just don't understand what could be wrong. Thank you.

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
    
    Debug.Print TempFile
    Debug.Print TempWB.Sheets(1).Name
    Debug.Print TempWB.Sheets(1).UsedRange.Address
    
    With TempWB.PublishObjects.Add( _
        xlSourceRange, _
        TempFile, _
        TempWB.Sheets(1).Name, _
        TempWB.Sheets(1).UsedRange.Address, _
        xlHtmlStatic)
        .Publish (True)
    End With
    
    Application.EnableEvents = False
    TempWB.Close savechanges:=False

    Set TempWB = Nothing
    
End Function

 

[Dan_W]

So I've used PublishObjects.Add a lot in the past as a really useful way to generate high-quality PNG files. I haven't used/looked at it in a while, so I went back to see if my code might have any hints in it that might help you with your error.... and I think I found something. Rather uncharacteristically for me, my code was very well-commented, and I found that I had written this:

'Configure the PublishObject settings       
Set ThePublisher = Application.ActiveWorkbook.PublishObjects.Add(SourceType:=xlSourceSheet, Filename:=HTMLOutput, sheet:=TempWS.Name, HtmlType:=xlHtmlStatic, Title:="PNG")
        
' VBA will throw errors if it tries to move too quickly through this process. Here, VBA will pause for approx 1.5 seconds.
Pause 1.5
        
' Generate files
ThePublisher.Publish True

So I think the culprit could be the speed at which VBA is running versus Excel not being able to keep up. Maybe try the Pause approach (I've put the code below), and see if that works for you? Looking at your code, I wonder if that means you may need to call the Pause routine just before the .Publish line? If that still doesn't work, maybe try pausing it for a bit longer? (2 or 3 seconds?)

Sub Pause(Optional ByVal Period As Single = 1)
        
        Dim TimeOut             As Single
        TimeOut = Timer + Period
        Do
            DoEvents
        Loop Until TimeOut < Timer
        
End Sub

Hopefully this helps, but please let me know how it goes.

 

[288enzo]

So I've used PublishObjects.Add a lot in the past as a really useful way to generate high-quality PNG files. I haven't used/looked at it in a while, so I went back to see if my code might have any hints in it that might help you with your error.... and I think I found something. Rather uncharacteristically for me, my code was very well-commented, and I found that I had written this:

'Configure the PublishObject settings      
Set ThePublisher = Application.ActiveWorkbook.PublishObjects.Add(SourceType:=xlSourceSheet, Filename:=HTMLOutput, sheet:=TempWS.Name, HtmlType:=xlHtmlStatic, Title:="PNG")
       
' VBA will throw errors if it tries to move too quickly through this process. Here, VBA will pause for approx 1.5 seconds.
Pause 1.5
       
' Generate files
ThePublisher.Publish True

So I think the culprit could be the speed at which VBA is running versus Excel not being able to keep up. Maybe try the Pause approach (I've put the code below), and see if that works for you? Looking at your code, I wonder if that means you may need to call the Pause routine just before the .Publish line? If that still doesn't work, maybe try pausing it for a bit longer? (2 or 3 seconds?)

Sub Pause(Optional ByVal Period As Single = 1)
       
        Dim TimeOut             As Single
        TimeOut = Timer + Period
        Do
            DoEvents
        Loop Until TimeOut < Timer
       
End Sub

Hopefully this helps, but please let me know how it goes.

I was hoping that was the issue. I made the changes as you suggested -

    With wb.PublishObjects.Add( _
         SourceType:=xlSourceRange, _
         Filename:=File, _
         Sheet:=wb.Sheets(1).Name, _
         Source:=wb.Sheets(1).UsedRange.Address, _
         HtmlType:=xlHtmlStatic)
         Pause 2
        .Publish (True)
    End With

But, I'm still getting the run-time error highlighting -

    With wb.PublishObjects.Add( _
         SourceType:=xlSourceRange, _
         Filename:=File, _
         Sheet:=wb.Sheets(1).Name, _
         Source:=wb.Sheets(1).UsedRange.Address, _
         HtmlType:=xlHtmlStatic)

What really confuses me is that it was working a few days ago, then it doesn't, then I'll restart my laptop and maybe it'll start working again.

 

[288enzo]

So what I think the issue is/was is the data in a cell didn't fit in the cell and I had to resize the column. Temperamental

 

[Dan_W]

To be honest, I don't think that was the problem. If it happens again, I suggest the following:
1. When the error message appears, press debug. You will still be in break mode and the problematic line of code will be highlighted in yellow.
2. Instead of pressing F5 (to execute the code), try pressing F8 instead. If you're not already familiar with this, this is called 'stepping through code' and allows you to work your way through the code on a line by line basis.
I'm curious to see if you can progress without errors using this method. But if it's working it's working, so let's not rock the boat! LOL

 

[288enzo]

Well, you're right. The same error came back this morning.

This is highlighted:

    With wb.PublishObjects.Add( _
         SourceType:=xlSourceRange, _
         Filename:=File, _
         Sheet:=wb.Sheets(1).Name, _
         Source:=wb.Sheets(1).UsedRange.Address, _
         HtmlType:=xlHtmlStatic)

When I step through the code, everything seems to be working normal up until the above line. The temp worksheet is created, and the copied range is pasted in perfectly.

For the life of me, I just don't understand why it works, and then doesn't.

 

[Dan_W]

I think it's time to say goodbye to With. It might be the culprit. See if my approach below works better?

'Configure the PublishObject settings       
Dim ObjPad as PublishObject
Set ObjPub = wb.PublishObjects.Add(SourceType:=
xlSourceRange, Filename:=File, sheet:=wb.Sheets(1).Name, Source:=wb.Sheets(1).UsedRange.Address, HtmlType:=xlHtmlStatic)
        
' VBA will throw errors if it tries to move too quickly through this process. Here, VBA will pause for approx 1.5 seconds.
Pause 1.5
        
' Generate files
ObjPub.Publish True

Dont forget to include the Pause routine I post earlier.

 

[288enzo]

I think it's time to say goodbye to With. It might be the culprit. See if my approach below works better?

'Configure the PublishObject settings      
Dim ObjPad as PublishObject
Set ObjPub = wb.PublishObjects.Add(SourceType:=
xlSourceRange, Filename:=File, sheet:=wb.Sheets(1).Name, Source:=wb.Sheets(1).UsedRange.Address, HtmlType:=xlHtmlStatic)
       
' VBA will throw errors if it tries to move too quickly through this process. Here, VBA will pause for approx 1.5 seconds.
Pause 1.5
       
' Generate files
ObjPub.Publish True

Dont forget to include the Pause routine I post earlier.

Thank you for the suggestion. It seems to be working again, which is good. If it starts to fail again I'll give your idea a go. At this point, I'd rather not touch it.

 

[yuliuyan07]

Had you solved you problem?