TextFrame.Characters.Text returns truncated string

[Dennis]

I tried to read the text from an Excel textbox, but the returned string is
always truncated to 255 characters. Did I do anything wrong? Please help!

The following is how to reproduce my problem:
- Create an Excel spreasheet C:\temp\Book1.xls and add a text box shape
object (named it Text Box 1) to sheet1 from the Drawing toolbar.
- Copy or enter some text into the text box with more than 2000 characters.
- Create a Windows project with C# and add a button button1, and a
multi-line textbox textbox1 to form1.
- Add reference to Microsoft Excel 11.0 Object Library from the COM tab.
- The code of button1_Click:
using Excel = Microsoft.Office.Interop.Excel;

private void button1_Click(object sender, System.EventArgs e)
{
Excel.Application oXL;
Excel._Workbook oWB;
Excel._Worksheet xlWs;
object missing = System.Reflection.Missing.Value;

oXL = new Excel.Application();
oWB = oXL.Workbooks.Open(@"C:\temp\Book1.xls",
missing, missing, missing, missing, missing, missing,
missing, missing, missing, missing, missing, missing, missing, missing);
xlWs =(Excel.Worksheet) oWB.Sheets.get_Item("Sheet1");

string s = xlWs.Shapes.Item("Text Box
1").TextFrame.Characters(1,missing).Text;//.Count.ToString();
textBox1.Text = s;

oXL.Quit();

}

 

[Nick Hebb]

I couldn't get it to work in VBA either. I changed it to iteratively
read off the left 255 characters then delete then, and that approach
worked (see code below). However, when I tried to put the characters
back into the text box at the end of the procedure, for some reason it
didn't work.

You could try using the following algorithm on a copy of the text box,
then simply delete the copy at the end of the method.

Sub LongTextBoxVBA()

Dim ws As Worksheet: Set ws = ActiveSheet
Dim tb As Shape
Dim s As String
Dim numChars As Integer

Set tb = ws.Shapes("Text Box 1")
s = ""
numChars = 255
If tb.TextFrame.Characters.Count < 255 Then numChars =
tb.TextFrame.Characters.Count
Do While tb.TextFrame.Characters.Count > 0
s = s & tb.TextFrame.Characters(1, numChars).Text
tb.TextFrame.Characters(1, numChars).Delete
If tb.TextFrame.Characters.Count < 255 Then numChars =
tb.TextFrame.Characters.Count
Loop
ws.Range("B2").Value = s
tb.TextFrame.Characters.Text = s

Set tb = Nothing
Set ws = Nothing

End Sub


-- Nick Hebb
http://www.breezetree.com

 

[Dennis]

Nick

Thanks for your reply. I'm just trying to find the correct method to use. I
did try to hack around this using the characters method, similar to yours. It
looks like the characters method can only handle 255 characters at a time.
That's why it didn't work when you try to put back the whole text to the text
box. You have to insert 255 characters at a time with the characters method.
Isn't that silly?

According to the documentation:

The Characters method is necessary only when you need to change some of an
object’s text without affecting the rest (you cannot use the Characters
method to format a portion of the text if the object doesn’t support rich
text). To change all the text at the same time, you can usually apply the
appropriate method or property directly to the object.

But it doesn't say what is the appropriate method and I can't find it in the
TextFrame object. The TextFrame has only one characters method and there is
no appropriate property to retieve the whole text.

Can anyone from the office team help here!

Dennis