PDA

View Full Version : RTF field in Excel by keeping formatting



jigar1276
08-24-2017, 02:34 AM
Hello,

I have a following string in one cell:
<p1:Project_Purpose xmlns:p1="http://schemas.microsoft.com/office/infopath/2003/myXSD/2008-08-25T10:33:10"><div xmlns="http://www.w3.org/1999/xhtml"><div><font face="comic sans ms">This is the purpose of the project</font></div><div><font face="comic sans ms">rich text data to test 21-08-2017</font></div><div><p>R<u><font color="#336600">ichtext 22-08-2017</font></u></p><p><u><font color="#336600">ffsdfdsfsdf sdfsdf</font></u></p><p> </p><p>f<font size="5">s<span style="background-color: rgb(204, 0, 153);">dfsfsdf</span></font></p></div><div><div><font face="helvetica">This is the purpose of the project</font></div><div><font face="helvetica">rich text data to test 21-08-2017</font></div><div><div><font face="comic sans ms"><br/></font></div><div><font face="verdana">This is the purpose of the project</font></div><div><font face="verdana">rich text data to test 21-08-2017</font></div><div><div><font face="comic sans ms"><br/></font></div><div><font face="georgia">This is the purpose of the project</font></div><div><font face="georgia">rich text data to test 21-08-2017</font></div><div><font face="comic sans ms"><p> </p></font><p><font face="comic sans ms"> asdasdasdas asdas asdasdas</font></p><p><font face="comic sans ms">adas</font></p><p><font face="comic sans ms">das</font></p><p><font face="comic sans ms">d asas</font></p></div></div><div><font face="comic sans ms"><p> </p></font><p><font face="comic sans ms"> </font></p></div></div><div><font face="comic sans ms"><p> </p></font><p><font face="comic sans ms"> </font></p></div></div></div></p1:Project_Purpose>




This is nothing but the RTF format for following:
20165

I want vba code to read the rtf string and give me the actual look (i.e. fonts, colors, styles etc.) in other cell.

Thanks in advance,
Jigar

offthelip
08-24-2017, 02:59 AM
try this
I assume the cell with the string was A1
I then put the following in A2 to B5:



start
end


font face=
>


font color=
>


font size=
>



the code writes what it finds in column C onwards depending on what it finds for the strings in column A


Sub expand()
inarr = Cells(1, 1)
sercharr = Range(Cells(3, 1), Cells(5, 2))


For i = 1 To 3
srch = sercharr(i, 1)
endstr = sercharr(i, 2)
n = 1
ind = 1
outcol = 3
Do Until ind = 0
ind = InStr(n, inarr, srch)
If ind > 0 Then
nd = InStr(ind, inarr, endstr)
Cells(i + 2, outcol) = Mid(inarr, ind + Len(srch), nd - ind - Len(srch))
n = ind + 1
outcol = outcol + 1
End If
Loop

Next i




End Sub

jigar1276
08-24-2017, 03:06 AM
Thanks a lot for answering.

I tried your code and found that font names, colors and sizes are listed in column C on wards. However, I was looking for the output as per the image i have attached in my question. So I have rtf string in A1, I need output as per the image in B1. Apologies for any misunderstanding.

offthelip
08-24-2017, 05:51 AM
So you want a routine to parse rtf and convert it into excel formatting, that is not difficult but it is a lot of work.

mdmackillop
08-24-2017, 12:35 PM
You could save the text as an HTM file and insert that as an object which could be double clicked to show the formatting in a browser window. I suppose that could be saved as an image, but that bit is beyond me.

offthelip
08-24-2017, 02:50 PM
Following on from mdmack's idea, what about saving the text as a rtf file and then opening it using word and embedding the Word document within excel, which you can do all within excel vba .
Best of luck