Range of Cell to exported as html website

NORRILLOUS

New Member
Joined
Apr 10, 2014
Messages
36
Office Version
  1. 365
Platform
  1. Windows
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:

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
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
 
Upvote 0

Forum statistics

Threads
1,224,737
Messages
6,180,643
Members
452,992
Latest member
TokugawaIesuma

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top