i'm trying extract text content excel using macro. code:
dim integer, j integer dim v1 variant dim txt string v1 = range("a2:c15") = 1 ubound(v1) j = 1 ubound(v1, 2) txt = txt & v1(i, j) next j txt = txt & vbcrlf next msgbox txt but showing raw characters meaning doesn't show formatting information bold, italic, underline, etc..
i want extract text along formatting information.
example: sample text
expected output: sample text
actual output: sample text
can explain what's wrong code , tell if wrong?
ok, let's have algorithm @stucharo little bit simpler extend.
public function gethtmlformattedstring(r range) string isbold = false isitalic = false isunderlined = false s = "" ccount = 0 on error resume next ccount = r.characters.count on error goto 0 if ccount > 0 = 1 ccount set c = r.characters(i, 1) if isunderlined , c.font.underline = xlunderlinestylenone isunderlined = false s = s & "</u>" end if if isitalic , not c.font.italic isitalic = false s = s & "</i>" end if if isbold , not c.font.bold isbold = false s = s & "</b>" end if if c.font.bold , not isbold isbold = true s = s + "<b>" end if if c.font.italic , not isitalic isitalic = true s = s + "<i>" end if if not (c.font.underline = xlunderlinestylenone) , not isunderlined isunderlined = true s = s + "<u>" end if s = s & c.text if = ccount if isunderlined s = s & "</u>" if isitalic s = s & "</i>" if isbold s = s & "</b>" end if next else s = r.text if r.font.bold s = "<b>" & s & "</b>" if r.font.italic s = "<i>" & s & "</i>" if not (r.font.underline = xlunderlinestylenone) s = "<u>" & s & "</u>" end if gethtmlformattedstring = s end function to clear, function works range containing single cell. should easy calling function each cell in bigger range , concatenating returned strings one.
edit op:
i called function below code:
sub replaceformattingtags() dim integer, j integer dim rng range dim txt string set rng = range("a2:c15") = 1 rng.rows.count j = 1 rng.columns.count txt = txt & gethtmlformattedstring(rng(i, j)) & " " next j txt = txt & vbcrlf next debug.print txt end sub 