View Full Version : Solved: Displaying Cells with HTML-code properly
Marcke
11-18-2008, 07:38 AM
I have a sheet where two columns have Cells with HTML Code in it.
How can you make sure that the tags aren't showed?
<html><body><font color="#000080"><b>Name<ecslst>, 10/10/2007: </b></font> <br><br>Description</body></html>
Thanks in advance for the help.
EDIT: The tags in all the cells are not always the same.
lucas
11-18-2008, 10:35 AM
You should avoid the select but this will show you how to put the text into the cell without the tags I think:
Range("F13").Select
ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:=False
Stripping html can get pretty involved so I think it would be better if you can figure out a way to enter the text without the tags to begin with if possible.
Marcke
11-18-2008, 01:48 PM
Hi, thanks for your suggestion, I'll try it tomorrow since I don't have a testing environment here.
I'm quite new to vba; why should you avoid the select? For performance issues?
Next to that: I'm extracting some values through an API to Excel, and the extract is in html.
I'll try to do the following:
1)Extracting
2)Copying
3)Pasting Special
If anyone has other suggestions, feel free to add them.
Marcke
11-19-2008, 01:25 AM
I tried the following code with the fields that allready had HTML tags in them, but VBA gives me an error. Does anyone have an idea why?
'Select Start Cell
shPrintout.Range("G4:G9999").Select
shPrintout.Range("G4:G9999").Copy
shPrintout.Range("G4:G9999").Select
ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:=False
Marcke
11-19-2008, 02:58 AM
Some more information that might help:
The following code is within a loop that extracts the HTML fields from an Object I create through an API
shPrintout.Cells(iRow, iCol) = ExtractedObject.field(shPrintout.Cells(jRow, jCol))
Marcke
11-25-2008, 05:16 AM
Hi all,
I've let this task rest for a while, but will continue on it as of today.
Does anyone have any suggestions?
Thanks in advance for your help,
Wannes
Marcke
12-01-2008, 06:16 AM
Anybody want to give it another try?
Marcke
12-23-2008, 09:00 AM
Hi all,
So my problem still is not solved.
However, I found the following link but there's still one issue that I cannot solve.
http://www.dailydoseofexcel.com/archives/2005/02/23/html-in-cells-ii/
The problem is that when a textfield has <br> it's spread over multiple cells, while I'd like a new line within the cell.
However, I don't understand much of the code, so don't know where to begin;
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
Marcke
12-24-2008, 02:47 AM
You should avoid the select but this will show you how to put the text into the cell without the tags I think:
Range("F13").Select
ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:=False
Stripping html can get pretty involved so I think it would be better if you can figure out a way to enter the text without the tags to begin with if possible.
When I try this I get an error with the Format parameter of pastespecial. I've included a test file in appendix
rbrhodes
12-24-2008, 05:19 AM
Hi Marcke,
Well if the tags are written properly e.g: < tag>... some stuff...</end tag> then brute force should work... <g>...</g>
Marcke
12-24-2008, 06:48 AM
Thanks for the help rbrhodes, it works indeed
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.