Excel convert range to HTML? Excel 2010

Question

I’m looking to take a range of A1:I26 on sheet "Desc" and convert it to HTML. That HTML then show in Cell H2 on sheet "-Listings-"

The cells in range A1:I26 has a graph with formatting I want kept (i.e., font style, color, layout) I want it to include the values of the text, not the formulas behind it.

Here's a screen shot of what I’m doing:

Answer 1

I figured out a way around this.  Instead of using a VBA code to convert a selected range to HTML, I went an alternative route using formulas and one single VBA code.

First thing I did was make a Sheet5, then I took the raw HTML data from the table I was trying to replicate (mapping Rows Desc!1:16 to Columns Sheet5!A:K) and divided it up, putting breaks into the code were I would enter the new information. I took each chunk and pasted it in A2:K2. Then I took the information that I manually change and linked that to my book in A4:K4. (These are using links to cells on my Log page that change with each listing I do.)

In Cell L2, I put the formula

=CONCATENATE(A2,A4,B2,B4,C2," ",C4,D2," ",D4,E2," ",E4,F2," ",F4,
                         G2," ",G4,H2," ",H4,I2," ",I4,J2," ",J4,K2)

(the line break is for presentation only).  This took each chunk of raw HTML and the information from my log and combined it showing a full HTML code. In cell C11 of my Log sheet, I put =Sheet5!$L$2 to show the HTML data on my main page to copy and paste the value.

After this, I noticed Excel would add double quotes to everything that was already quoted, and single quotes to other areas. After doing some searching, I found some code that would copy the values of a selected cell so I would be able to paste the HTML code without quotes. Here's the Macro I used for that:

Sub CopyCellContents()
    Range("C11").Select
    'create a reference in the VBE to Microsft Forms 2.0 Lib
    Dim objData As New DataObject
    Dim strTemp As String
    strTemp = ActiveCell.Value
    objData.SetText (strTemp)
    objData.PutInClipboard
End Sub

I used Pearson Software Consulting (i.e., Chip Pearson)’s Using The Windows Clipboard In VBA site for the macro.

I ran into a problem with User defined data.  To solve this, I had to exit the code debugger, reopen the module with the macro in it, go to “Tools” → “References”, and then browse and type FM20.DLL into “File name:”.  Otherwise I could not find the “Microsoft Forms 2.0 Object Library” in the available references.

I assigned this to a button next to cell C11, and now, when I click it, the code is copied to my clipboard without quotes. This doesn't work exactly the way I was originally planning, but the outcome is exactly what I wanted. Hope this helps some others.

Here are some screen shots of what I accomplished:

This is where I needed the code for each listing.  Sheet5 is the raw data broken up into cells.

Here's the finished product created by these formulas.  And there is my log page with the button.  When clicked, it copies the code in the box to the right without quotes.