PDA

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&lt;ecslst&gt;, 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