VBA Split HTML String

Question

I have a field in a SQL-Server 2008R2 table which contains values like this (all lines in one field):

<div><font face=""Times New Roman"" size=3 color=black>MyCustomer Name Ltd.</font></div>

<div><font face=""Times New Roman"" size=3 color=black>MyCustomer Adress</font></div>

<div><font face=""Times New Roman"" size=3 color=black>MyCustomer Zip-code MyCustomer City</font></div>

I am working in Access 2010 and have to Export this Content into an Excel sheet with 3 columns: Name, Adress, Zip+City

I tried with

Split(rs("Myfield"), "<div><font face=""Times New Roman"" size=3 color=black>")

but it does not work.

Any idea how to split this string? Thanks Michael

score 0 · Answer 1 · answered Sep 22 '15 at 07:34

0

You are splitting it on the wrong delimiter...

Logic

Replace <div><font face=""Times New Roman"" size=3 color=black> in the main string with blank
Split on </font></div>

Is this what you are trying?

Sub Sample()
    Dim s As String

    's = rs("Myfield")
    s = "<div><font face=""""Times New Roman"""" size=3 color=black>MyCustomer Name Ltd.</font></div>" & _
        "<div><font face=""""Times New Roman"""" size=3 color=black>MyCustomer Adress</font></div>" & _
        "<div><font face=""""Times New Roman"""" size=3 color=black>MyCustomer Zip-code MyCustomer City</font></div>"

    'Debug.Print s

    s = Replace(s, "<div><font face=""""Times New Roman"""" size=3 color=black>", "")

    Debug.Print Split(s, "</font></div>")(0) '<~~ MyCustomer Name Ltd.
    Debug.Print Split(s, "</font></div>")(1) '<~~ MyCustomer Adress
    Debug.Print Split(s, "</font></div>")(2) '<~~ MyCustomer Zip-code MyCustomer City
End Sub

answered Sep 22 '15 at 07:34

Siddharth Rout

147,039
17
206
250

A good solution as long as the font values stay the same. If that's not the case (or you want to look into XML handling anyway) have a look at [this overview](http://stackoverflow.com/a/11325/4600127) on XML handling by rjzii in VBA. – Verzweifler Sep 22 '15 at 07:41
True. I gave a solution based on user data. If you see the question, OP is trying to split on the font values so I can safely assume that they are same ;) – Siddharth Rout Sep 22 '15 at 07:42
I didn't want to criticise your answer, it's great and more importantly simple. I just wanted to provide an outlook for when matters become more complicated :-) – Verzweifler Sep 22 '15 at 07:50
Don't worry. I took it positively ;) – Siddharth Rout Sep 22 '15 at 07:51
Thanks a lot, it works. Yes, fortunately the font values are always the same. Pls help me with a further Problem I discovered: the number of lines differ! That makes it neccessary to Loop, but I can't find how. s() = rs("MyField") s() = Replace(s(), "
", "") For i = LBound(s) To UBound(s) Debug.Print Split(s, "
")(i) next i Fails with an Error at Replace (s().... Argument byRef not matching Thanks! – mak Sep 22 '15 at 08:08
Can you please update your question with your new query? It is difficult reading it in comments @user3480989 – Siddharth Rout Sep 22 '15 at 08:34

score 0 · Answer 2 · answered Sep 22 '15 at 08:52

Sorry Siddharth my incorrect use of comments.

Meanwhile I managed my Problem of variable number of lines by this

Do While Not rs.EOF
m = rs("MyField")
m = Replace(m, "<div><font face=""Times New Roman"" size=3 color=black>", "")
s() = Split(m, "</font></div>")

For i = LBound(s()) To UBound(s()) 
    Debug.Print s(i)

Next i

rs.MoveNext

Loop

Thanks again yr. reply! Michael

VBA Split HTML String

2 Answers2