While I was working on this newsgroup post to copy formatted text from Word to Excel while preserving formatting and maintaining line breaks, I stumbled on a way to enter HTML in cells.
becomes
with this macro
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
Private Sub Worksheet_Change(ByVal Target As Range) Dim objData As DataObject Dim sHTML As String Dim sSelAdd As String Application.EnableEvents = False If Target.Cells.Count = 1 Then If LCase(Left(Target.Text, 6)) = "<html>" Then Set objData = New DataObject sHTML = Target.Text sHTML = Replace(sHTML, "<html>", "<html><style>br{mso-data-placement:same-cell;}</style>") objData.SetText sHTML objData.PutInClipboard sSelAdd = Selection.Address Target.Select Me.PasteSpecial "Unicode Text" Me.Range(sSelAdd).Select End If End If Application.EnableEvents = True End Sub |
I haven’t quite got the line break part worked out here. You may notice that I try to put a style tag in the string before I put it in the clipboard. This doesn’t seem to have any effect. The style tag worked with the aforementioned newsgroup post, but didn’t work in this situation. In this case, text after a br tag is moved to the cell below, when I would prefer that it act like an Alt+Enter and create a new line in the same cell. I’m not quite sure why there’s a difference.
I’ve used the PasteSpecial method of the Worksheet object instead of the Range object. It gives me the “format” argument that I need to specify Unicode Text where the Range’s method doesn’t have that argument. That means I had to account for any “move selection after entry” situation. I couldn’t think of a better way to do that, but I’m sure there was.
You may also notice that I, in a most cowardly fashion, limited the event to only work when one cell was being changed. I like to allow Change events to work on multiple cells and I had originally set this up that way. Then I deleted about 20 columns, which means the Change event was checking 1.2 million cells. Ugh. I may change it to loop though the cells using the Find method instead of checking with the Left function. That would solve the problem I had deleting a bunch of columns, but who knows what other problems it would create.
Now I need to figure out a way to do the reverse. I suppose a lengthy SaveAs HTML will be in order for that.
Cool idea, Dick. That might be a useful SmartTag – recognise any cell starting with <html> and pop up a smart tag offering to change it to html text. I’ll see if I can knock one up…
(A short while later)
Yep, maybe the first useful SmartTag I’ve come across. Download the installation file while it’s hot!
Stephen,
It installed correctly, and it does recognize a cell that starts with < html >. However, if I put later in another cell something different, like:
< html >< b >Testing < i >more< /i > here< /b >
It puts the “original” text (the first cell that was converted).
Is there a public variable or something that might be “memorizing” the previous entries ?
That html did work there… I put the spaces and everything… oh well, it’s the html representation of that text that I’m using.
This is fantastic.
Using Paste operations is what I had discovered too…
If only there were a way to avoid the use of the clipboard.
Hi Juan Pablo
I didn’t clear the clipboard after using it, so the original text was still in it. I’ve uploaded an update, which also handles the br issue using an after-paste replace. If anyone’s going to use it, I guess it should try to store and restore the clipboard contents…
Stephen,
I get the following error when I use your smart tag:
“Microsoft Office Excel cannot find any data to replace. Check if your search formatting and criteria are defined correctly….blah, blah, blah”
I don’t know much about smart tags, so maybe I did something wrong?
D’Oh! *!&%!* DisplayAlerts!
I’ve updated it…
Regards
Stephen Bullen
Stephen,
How can I use your code? It seems HTML in a cell is recognized by your program but I cannot convert it.
Please have a look at the screen capture.
http://puremis.net/excel/SmartTag.avi
Regards,
Colo
Colo,
The ‘action’ taken by the smart tag is exactly the same as Dick started this article with – the dll just wraps that with the Smart Tag infrastructure to recognise the and show the tag in the cell. When you click the ‘Convert it’ item, you’re running Dick’s code. So does Dick’s work in your case?
Stephen, Thanks for your help. It was my misunderstanding.
How about a single process to convert the selected range? Something like:
=====================================================
Private Declare Function RegisterClipboardFormat Lib “user32? _
Alias “RegisterClipboardFormatA” (ByVal lpString As String) As Long
Sub ConvertHTMLToText()
‘ Adapted from http://www.aboutvb.de/vba/artikel/vbawdhtmltodoc.htm
nCFHTML = RegisterClipboardFormat(“HTML Format”)
For Each oCell In Selection
nClipboardText = “Version:0.9? & vbCrLf
nClipboardText = nClipboardText & “StartHTML:-1? & vbCrLf
nClipboardText = nClipboardText & “EndHTML:-1? & vbCrLf
nClipboardText = nClipboardText & “StartFragment:000081? & vbCrLf
nClipboardText = nClipboardText & “EndFragment:°°°°°°” & vbCrLf
nClipboardText = nClipboardText & oCell.Value & vbCrLf
nClipboardText = Replace(nClipboardText, “°°°°°°”, Format$(Len(nClipboardText), “000000?))
With New DataObject
.Clear
.SetText StrConv(nClipboardText, vbFromUnicode), nCFHTML
.PutInClipboard
End With
ActiveSheet.Paste Destination:=oCell
Next oCell
End Sub
=====================================================
Dear Sir,
I want to do esactly an opposite way I am having data in an excel sheet with different colour fromaty . I need to convert that data into HTML format. HJow can we achieve this. This is an exactly reverrse case that you have explained
Hi Dick,
Man, this is incredibly timely for me. Thanks to you and Stephen.
I might need, still, to use your code above; I can’t compile it for lack of knowing what type DataObject is – where it is described?. In fact, I just don’t see what’s going on at all. What’s with the “br{ms0-…}” line? Why the need for the Select statements?
And I second Amogh’s request. Please.
Hey Dave: DataObject allows you to place text directly in the clipboard. Set a reference to MS Forms 2.0 to get access to it. If you have a userform in your project, the reference will already be there. MSDN probably has some info on the object, but I think once you see its methods in the Object Browser, you won’t require any further instruction.
You don’t need the mso- stuff if you just want to format the text. This all came about because I was trying to copy multiple lines into one cell (as opposed to having them distributed one per line). That particular tag came from inspecting MS’s html when a workbook is saved and there’s a control+enter in a cell. If your cell has multiple lines, you may need that tag, but I don’t remember if I ever got that to work properly.
Re Select: The Unicode argument to PasteSpecial is only avialable from the Worksheet Object, so I had to select the original cell to paste the clipboard text as unicode, then go back and select the cell that was active after the change event fired. I’d love to hear of a better way to do this. I had an opportunity to use this recently, but ended up leaving the formatting for all the characters the same because I didn’t want to introduce Selecting into an already complicated procedure.
Amogh: That would be nice, but I haven’t found an easy way to do it. I can think of two approaches, neither of which I like. I could loop through the characters of the cell, determine their formatting, and build html tags into the value. No matter how thorough I was, there would always be something missing.
I could also save a copy of the workbook as html, dig through the html file for that cell, and copy the html that Excel creates. There would probably be a lot of extra crap in there and I hate to create temporary documents just for that procedure. Ideas are welcome.
Dick: You might find it easier to save it as a XML spreadsheet file and parse that. It’s a lot easier than dealing with a HTML document.
In the former, the contents of B4 that is formatted very wierdly look like:
<Row ss:Index=”4? ss:Height=”15.75?>
<Cell ss:Index=”2? ss:StyleID=”s21?><ss:Data ss:Type=”String”
xmlns=”http://www.w3.org/TR/REC-html40?><B><I>h</I></B><Font>e</Font><B>ll</B><Sub>o</Sub><Font> </Font><I><S>t</S></I><S>h</S><U>e</U><Font>r</Font><Sup>e</Sup></ss:Data></Cell>
</Row>
Hopefully, the above will come through correctly. ;-)
The latter (i.e., the HTML) is much more difficult to parse. Each different format combination results in a different style. For example, I had 7 styles such as:
.font01169
{color:windowtext;
font-size:10.0pt;
font-weight:400;
font-style:normal;
text-decoration:none;
font-family:Arial;
mso-generic-font-family:auto;
mso-font-charset:0;}
.font51169
{color:windowtext;
font-size:10.0pt;
font-weight:700;
font-style:normal;
text-decoration:none;
font-family:Arial, sans-serif;
mso-font-charset:0;}
.font61169
{color:windowtext;
font-size:10.0pt;
font-weight:400;
font-style:normal;
text-decoration:underline;
text-underline-style:single;
font-family:Arial, sans-serif;
mso-font-charset:0;}
And, the actual cell contents look like:
<td height=21 class=xl221169 style=’height:15.75pt’>h<font class=”font01169?>e</font><font
class=”font51169?>ll</font><font class=”font81169?><sub>o</sub></font><font
class=”font01169?> </font><font class=”font91169?><s>t</s></font><font
class=”font101169?><s>h</s></font><font class=”font61169?>e</font><font
class=”font01169?>r</font><span style=’display:none’><font class=”font71169?><sup>e</sup></font></span></td>
Also, unlike the XML, the html has no direct indication of what row or column a cell corresponds to. So, one would have to parse the *entire* file to reconstruct the table.
The downside with the XML is that at least one of the formatting codes is different from the HTML. The HTML for strikthrough is <strike> whereas in the XML is appears as <S> Of course, there might be other differences. And, I don’t know how to find or interpret the schema for the XML spreadsheet.
Of course, it might be a lot more elegant if one figured out how to use the ExportXML method. I don’t know enough about how XL handles XML to figure it out.
I have searched in vain for tutorials on creation of smart tags / smart documents. (I have found some but usually in C# / .NET) And no mention in any of the books I have. Any sources out there Stephen, that you would recommend?
Have downloaded the SDK. I have Visual Basic 6.0 as well as Office 2003 Professional SP2.
What did you use to create the .dll ?
TIA
doco
hi
i am getting an error while runningthis macro as VBA cann’tunderstand “DataObject” object.
Please Help………
Can i use excel as database in web page?
I’ve been looking at the main post here and seeing it it solves my issue, but I can’t get it to work. What I would like to do is aving something like “Hello as data in one of my cells and when I do SaveAsWebPage, I want that HTML from the cell to be in the HTML table generated by Excel. It doesn’t work because when I save as webpage, Excel is converting the html data in my cell into “<font colot=blue>
How can I get it to preserve my html data in my cells when I do the saveaswebpage?
Thanks so much!
-Larry
Larry. Try escaping any html tag characters to have it render properly in these comments. When I put <strong>Hello</strong> into A1 and save as a web page, it preserves the tags – that is it shows exactly what’s in the cell rather than Hello
Heya:
You wrote:
> I haven’t quite got the line break part worked out here.
Never have I! We’ll let’s just say I’m getting a bit anti-microsoft.
What I tried is as follows:
Replace the ‘s with Chr(30), special-paste this as “Unicode”
into a cell.
Then run the following macro (executes on ActiveCell):
Sub ReplaceChr30asNewLines()
For n = 1 To Len(ActiveCell.Text)
If (Mid(ActiveCell.Text, n, 1) = Chr(30)) Then
‘ This simply refuses to work when > 255
ActiveCell.Characters(n, 1).Text = Chr(10)
End If
Next
End Sub
The above works if the cell has less than 255 char’s.
It’s the old “Microsoft sacked the guy who wrote the code and
nobody here can fix it in 5 years” problem!
Brr…Microsoft!
I wrote:
> Replace the ‘s with Chr(30), special-paste this as
> “Unicode” into a cell.
Should read as:
Replace the newlines as Chr(30), then special-paste this
as “Unicode” into a cell.
I have an html code like this:
hello worldthis is a div tag
Is it possible to paste this code on a single cell in excel? when I try to paste the code I got something like this:
a hello world
b this is a div tag
I need to display this code in a single cell.
Hi Dick,
This looks really helpful for me. Only problem I have with this is that the should create a new line within the cell. I don’t really understand your code, so don’t know how to change it.
Have you been able to do so by now?
Kind Regards,
Wannes
Dim objData As DataObject
Dim sHTML As String
Dim sSelAdd As String
Const sTEMP As String = “||||”
Application.EnableEvents = False
If Target.Cells.Count = 1 Then
If LCase(Left(Target.Text, 6)) = “<html>” Then
Set objData = New DataObject
sHTML = Target.Text
sHTML = Replace(sHTML, “<br />”, sTEMP)
objData.SetText sHTML
objData.PutInClipboard
sSelAdd = Selection.Address
Target.Select
Me.PasteSpecial “Unicode Text”
Me.Range(sSelAdd).Select
Target.Value = Replace(Target.Value, sTEMP, Chr$(10))
End If
End If
Application.EnableEvents = True
End Sub
This seems to work for multi-line stuff.
Hi.
I have a cell comment. the comment is formated with some bolds and paracgraph/breaklin(exel formatting).
can you please help me to do two thinks:
1. copy the formated text to a adiacent cell(i found only to copy text – after copy the text looses the “bold formatting”)
2. transform excel bold and breakline into html tags.
thanx a lot.
Sub ShowCommentsNextCell()
‘based on code posted by Dave Peterson 2003-05-16
Application.ScreenUpdating = False
Dim commrange As Range
Dim mycell As Range
Dim curwks As Worksheet
Set curwks = ActiveSheet
On Error Resume Next
Set commrange = curwks.Cells _
.SpecialCells(xlCellTypeComments)
On Error GoTo 0
If commrange Is Nothing Then
MsgBox “no comments found”
Exit Sub
End If
For Each mycell In commrange
If mycell.Offset(0, 1).Value = “” Then
mycell.Offset(0, 1).Value = mycell.Comment.Text
End If
Next mycell
Application.ScreenUpdating = True
End Sub
this code copy the text but i loose formating and i need to transform bold and “line break” to html tags.
sry for my english
hi all i need some help…
if i product a table on excel, e.g. a table of months and days for excel, how do i turn that information into html and make the whole html code appear in one cell.. thanx.. i really need to know how to do this thanx.
Great post man Thanks
Hi,
(forget my last post. It could not be understood, because tags I wrote were replaced :) )
nice approach.
It just seems the
leads to a display that looks to me like
What do I do wrong?
(have added a line, that replaces just as ,
but that should not have caused the misbehaviour)
Gunnar
Gunnar: It worked for me – put italic on one line and bold on the next. Is there something missing from your comment that got interpreted as HTML?
Hi,
I need to convert html text into formatted text. Unfortunately it’s a big file with over 500 rows. I need to convert the text in each cell. Because of the huge number of rows it’s not possible to copy it manually. Additionally, the text of each cell should convert into one cell again and not in different cells.
Has anybody an idea? I really don’t know, how I can solve that problem:-(
Thanks for all ideas in advance!
Hi – I tried converting this to an arbitrary subroutine:
but it doesn’t seem to work. The paste just pastes back the original HTML. Any ideas?
Never mind – figured it out – the cell must start with
.
On multi-line issue:
Rich text formatted cells in MS Access use DIV tags, but when these field values are sent to Excel using the methodology described above, each DIV tag ends up in its own cell in Excel no matter what I try. I can’t remove the DIV tags because MS Access embeds some styling instructions in the tag (e.g. “align=center”). How can I get the code above to put all this DIV data into one cell in Excel?
Ken: I can’t figure out a way to fix that short of rebuilding the string. Maybe try replacing the
with
and
with
. I would try it, but I can’t figure out how to get the html out of access.
Guys were you able to solve the problem of putting the parsed html text into one single cell?
hello guys, you got me completly lost, please help me
i have programm that can generate csv lists of my music. i make one csv per column (like artist or year) then i paste them into excel, do a nice page layout which i save as html. so far so good.
now i made 3119 screenshots of waveforms that i would like to include to the page. i can easily manipulate the script that makes the csv to give them any form or syntax i want, so i went with:
but when i save the sheet as html my browser shows me this exact same text instead of loading the image in that spot.
this is what the editor tells me:
320
<img src="file:\\W:\wf\Yoetc & Zombeats – Hold Your Breath.jpg"
alt="waveform" width="200" height="20">
W:\AAATTTGGG\X-Z\Yoetc
& Zombeats – Hold Your Breath.mp3
W:\AAATTTGGG\X-Z\Yoetc & Zombeats – Hold Your
Breath.mp3
is there an easy solution and can somebody explain it to me so i understand it? if not i might try to write the script in a way that it generates the whole html “oneshot” but i would miss out on conditional formating, probably never get the layout right, plus it’s a lot of work ;)…