Range of Cell to exported as html website

NORRILLOUS

New Member
Joined
Apr 10, 2014
Messages
7
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

Code:
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
 
Last edited:

Rijnsent

Well-known Member
Joined
Oct 17, 2005
Messages
1,112
Office Version
365
Platform
Windows
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):
Code:
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
 

Forum statistics

Threads
1,077,955
Messages
5,337,391
Members
399,144
Latest member
Lauren Ward

Some videos you may like

This Week's Hot Topics

Top