Range of Cell to exported as html website

[NORRILLOUS]

Hello, I am trying to export a specific range of cells (A1:E18 for one and I1:M18 for another) on a specific sheet and save it in a HTML table, separately. The code I have below is what I found and have been working with to try to modify but as I make changes I don't seem to be affecting anything not to mention I am not sure how to tell what range to select and export. Also I first tried it in a test file that has a single sheet and nothing else, It actually worked but I still couldn't tweak it to my needs.

So I then tried to advance into my actual file where there are several worksheets with other code and I start getting a Sub Script out of range error. I then thought to myself "Change the .PublishObjects(1) to .PublishObjects(11)" because my sheet number is 11...Didn't work...

I also thought i might be that particular file so I added it to another file for gits and shiggles...well same problem...Didn't work

Sub Convert_Excel_Table_To_HTML()

    With Application.DefaultWebOptions
         .RelyOnVML = True
         .AllowPNG = True
         .PixelsPerInch = 96
    End With
        With ActiveWorkbook
         .WebOptions.AllowPNG = False
            With .PublishObjects(1)
            .Filename = "\Test.htm"
            .Publish
         End With
    End With
End Sub

Any help would be greatly appreciated

 

[Rijnsent]

Hi Norrillous,
your macro seems to be using this method to save the HTML: https://www.ablebits.com/office-addins-blog/2014/03/21/convert-excel-html/ , so you'll have to set up your ranges in that way. After that, you could run a loop, like so (untested):

Sub Convert_Excel_Table_To_HTML()

    With Application.DefaultWebOptions
         .RelyOnVML = True
         .AllowPNG = True
         .PixelsPerInch = 96
    End With
    With ActiveWorkbook
        .WebOptions.AllowPNG = False
        n = 1
        For Each p In .PublishObjects
           p.Filename = "\Test" & n & ".html"
           p.Publish
           n = n + 1
        Next p
    End With
End Sub

Alternatively you could check out the macro in this tutorial: https://analystcave.com/excel-export-excel-to-html-convert-tables-html/

Cheers,
Koen