Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
TextFrame.Characters.Text returns truncated string
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 = ", 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(); } -- Dennis |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
TextFrame.Characters.Text returns truncated string
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
TextFrame.Characters.Text returns truncated string
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 objects text without affecting the rest (you cannot use the Characters method to format a portion of the text if the object doesnt 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 -- Dennis "Nick Hebb" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I return a text string when a calculation returns a non who | Excel Worksheet Functions | |||
Help formula that returns a text string from another sheet | Excel Worksheet Functions | |||
Macro that returns a specific number of characters from a text str | Excel Discussion (Misc queries) | |||
cell to textframe using characters object | Excel Programming | |||
.TextFrame.Characters.Text property readOnly in function?? | Excel Programming |