Excel COM Automation, 255 limit-CodePudding

I use Excel.Application COM object to automate the generation of documentation that relies on Excel.

In essence, the input is a simple HTML string that only has span, bold and italic tags.

I erase the contents of the relevant cell, I parse this HTML string and I append characters to the relevant cell adjusting the font and font color, like so:

offset = Range.Characters().Count
Range.Characters(offset   1).Insert("Hello");
Range.Characters(offset   1).Font.Bold = True

It works fine for content less than 255 characters, but reaching that number, nothing is appended to the cell and the offset is always 255.

Any ideas? If I open excel and I write stuff manually into the cell I do not have this limitation.

CodePudding user response：

Here's a workaround which relies on Excel being able to open a file which just contains HTML formatted into a table:

Sub Tester()
    Dim ws As Worksheet
    HTMLtoRange ActiveSheet.Range("B2").Value, ActiveSheet.Range("B4")
End Sub

'Take some HTML, write it to a file as a table,
'   open the file in Excel, and copy A1 to `c`
Sub HTMLtoRange(HTML As String, c As Range)
    Dim fName As String
    With CreateObject("scripting.filesystemobject")
        fName = .getSpecialFolder(2).Path & "\" & .getTempName() & ".xls"
        .opentextfile(fName, 2, True).write _         
                          "<html><table><tr><td>" & HTML & _
                          "</td></tr></table></html>"
    End With
    Application.ScreenUpdating = False
    With Workbooks.Open(fName)
        .Sheets(1).Range("A1").Copy c
        .Close False
    End With
End Sub

Input and output:

CodePudding user response：

I will summarize what worked for me:

First, extract from the HTML string the text and set the value of the cell to that text, this is the HTML without tags and unescaped, like so:

Range.Value = html_text

Use the Characters object to modify the format as necessary, Characters.Count will not truncate to 255, now it will return the real length of the string, like so:

Range.Characters(offset, format_length).Font.Bold = True

This way, I do not use the Insert method in characters, which seemed to be the one causing issues. Tried this with HTML strings up to 1000 characters with success. Also note that I am using COM (Excel.Application) from Python rather than VBA, in the latter this might not be trivial to do.