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.
![](../../I/static/images/affe93857de19b270f11854efa9e900cefb739d3a09c96eb2fbf17288312b666.png)
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.
![](../../I/static/images/2057893b8360c9a26890d7fa59453e8f635f7c242c1e2e8657f15b0b364fc748.png)
Welcome to Super User. Unfortunately, we are not a code-writing service. Instead of simply asking for code to perform a particular task, please show us what you've tried so far (including any code you currently have) and where you're stuck so that we can help you with your specific problem. Questions that only ask for code are too broad and are likely to be put on hold or closed. Please read How do I ask a good question?.
– DavidPostill – 2015-06-25T16:32:22.183I do not have a code. I've used multiple codes i've found online, but none work. Most are for what i want, with the end result being for email. I just want the first half. – Ryan Hubbard – 2015-06-25T16:55:10.613
Read my comment again "please show us what you've tried so far (including any code you currently have) and where you're stuck" – DavidPostill – 2015-06-25T16:57:26.390
Why do you think you need convert a range to HTML? Graphs in Excel are not normally assigned to a range of cells. Although, they may float over (cover) a range of cells. Graphs are usually an object which can be selected, copied and pasted elsewhere. Have you tried just copying the graph to the other sheet? – CharlieRB – 2015-06-25T17:14:08.813
The worksheet im using is a log for all the listings i put on ebay. The program i list with has a description area were i can either A) edit the tables values (multiple edits) or B) Copy and paste the html code and it be done. – Ryan Hubbard – 2015-06-25T17:48:38.033
Updated the post – Ryan Hubbard – 2015-07-22T20:41:27.950
2
@RyanHubbard: would you mind posting your solution as a separate answer, rather than in the question body? (see this help page article). Thanks!
– nc4pk – 2015-07-22T20:54:44.493What is this all about? The question (and the comments under it) mention a “graph” — I don’t see one. The question talks about preserving formatting (i.e., font style, color, and layout) from an Excel worksheet (I guess that’s what it means); where in the answer is this handled? The code in the answer puts something into the clipboard; where is this ever used? What part of this is useful to anybody? – Scott – 2017-05-11T21:51:34.253