Capturing Cell Text formatting

[sheetaljain]

Hi All,

I have a Excel Template that will allow users to enter maximum of 1000 rows with maximum allowed characters of 2000.
These details can be formatted with "Bold", "Italic", etc. I need to write a logic so all the these formatting information can be captured and stored.

I am trying to achieve this by using Excel Object Model - Characters.Font Object and it is working as well. But the performance is too bad it takes about 30 mins for processing 1000 rows.

I need some help to improve the performance or some other logic to achieve the above requirement.

thanks,
Sheetal

 

[Peter_SSs]

Welcome to the MrExcel board!

Could I ask what you are trying to capture and store it for?
That is, what are you subsequently going to do with this information?
Where are you storing the information?

Could we see your current code?

 

[sheetaljain]

Thanks Peter.
Ok the requirement is that User will enter the Formatted Data in Excel and the same will be uploaded to SQL Server. This formatted information will be used for displaying on the Web Form in the same way they have formatted it.
Below is the logic that i have written but it is taking 30 mins for processing which will not be approved -

I am checking that if the character is "Bold" then I am storing tag in a string and so on. this html string along with the text will be saved in SQL Server and will be pulled on UI for displaying.

For Each cell In cellRange
bold = False
superscript = False

htmlStr = ""
strlen = cell.Characters.Count
For j = 1 To strlen
Set charInfo = cell.Characters(Start:=j, Length:=1)
Set fontInfo = charInfo.Font
bold = fontInfo.bold
[INDENT]If fontInfo.bold Then
If bold = False Then
bold = True
htmlStr = htmlStr & ""
End If
Else
If bold Then
bold = False
htmlStr = htmlStr & ""
End If
End If
If charInfo.Text = vbLf Then
htmlStr = htmlStr & "
"
End If
[/INDENT]Next
If (bold) Then
htmlStr = htmlStr & ""
bold = False
End If
If superscript Then
htmlStr = htmlStr & "</SUP>"
End If
htmlStr = htmlStr & "
"

 

[Peter_SSs]

Thanks Peter.
Ok the requirement is that User will enter the Formatted Data in Excel and the same will be uploaded to SQL Server. This formatted information will be used for displaying on the Web Form in the same way they have formatted it.

I'm afraid that this is in an area I am unable to help with.
Hopefully somebody else will step in.

 

[Comfy]

Could you store the string along with the formatting like this?

For j = 1 To strlen
Set charInfo = cell.Characters(Start:=j, Length:=1)
Set fontInfo = charInfo.Font

htmlstr = htmlstr & Left(CStr(fontInfo.Bold), 1) & Left(CStr(fontInfo.Italic), 1) & Left(CStr(fontInfo.Superscript), 1) & Left(CStr(fontInfo.Underline), 1)
Next j

In this example each character would have 4 stored values (F - False, T - True) for Bold, Italic, Superscript, Underline.

AB

The resulting formatting string for this would be:

TFFFFFFT

 

[sheetaljain]

But this logic doesnt reduces the processing time. As ultimately I am checking the formatting character by character that takes time.