Appending html files in VBA

Question

I have an Excel report with a bunch of sheets, each with a table of data. I need to make a button that will create one html page containing the tables, one after the other.

I don't know much about html, so my first approach was to have a macro that creates another worksheet with the tables on it, and then saves that sheet as html using ActiveWorkbook.PublishObjects. The problem is that I don't want the column widths to be the same for all of the tables in the exported file.

I think the solution is to set the column widths on each sheet and export the sheets to html individually, then append them all into one html file. Is there any easy way to accomplish this, or will I need to write code to go into each html file and pick out the relevant sections?

Answer 1

In the June 2001 issue of VBPJ, I wrote a column that would probably serve as a good starting point for you. It showed how to iterate through a Range, converting it to an HTML table, which was then put on the clipboard for pasting into something else (eg, FrontPage). The article:

Ask the VB Pro, June 2001: Soup Up Office VBA

Specifically, take a look at the second Q/A pair, and Listing 2 which shows a RangeToHtml() function. My code basically strips out all the formatting - that was the goal, for me. I don't know enough about the Excel object model to say for sure, but I'd imagine you could embed width= attributes in the 's if that's important.