Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA: Worksheet cell .Text property: 1024 bytes text len limit
Hi!
My case: I have an xls file with the only one worksheet. The first cell of this worksheet contains a text more then 4 kb length. The .Text property for that Cell returns only first 1024 characters. I see this in the following example: For Each ws In Worksheets MsgBox ws.Cells(1, 1).Value MsgBox ws.Cells(1, 1).Text Next ws Q: How can I get whole text from this cell? Thanks in advance! Bye. -- Alexei Baskakov |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA: Worksheet cell .Text property: 1024 bytes text len limit
Cells can contain text with length up to 32k. But a msgbox can only display
1024 characters. Sub test() Dim s As String Dim i As Long Const SomeText As String = "This text is 32 characters long " s = SomeText For i = 1 To 1000 s = s & SomeText Next Range("a1") = s s = "" s = Range("a1") MsgBox Len(s) ' 32032 MsgBox Mid(s, 32000, 32) End Sub Regards, Peter T "loyso" wrote in message ... Hi! My case: I have an xls file with the only one worksheet. The first cell of this worksheet contains a text more then 4 kb length. The .Text property for that Cell returns only first 1024 characters. I see this in the following example: For Each ws In Worksheets MsgBox ws.Cells(1, 1).Value MsgBox ws.Cells(1, 1).Text Next ws Q: How can I get whole text from this cell? Thanks in advance! Bye. -- Alexei Baskakov |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA: Worksheet cell .Text property: 1024 bytes text len limit
Cells can contain text with length up to 32k. But a msgbox can only
display 1024 characters. No! The problem isn't msgbox-related! I see in integrated excel debugger that Len function returns 1024 for my cell with 4k of text! For Each ws In Worksheets MsgBox Len(ws.Cells(1, 1).Text) Next ws Also displayes 1024 in message box. Somebody - help me, please! :) -- Alexei Baskakov |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA: Worksheet cell .Text property: 1024 bytes text len limit
Is the cell a formula beginning with =. If so you can neither Read nor Write
a formula with length 1024 including the "=". Otherwise, as I said previously a cell can contain text up to the 32K character length limit, as my example routine demonstrated. Regards, Peter T "loyso" wrote in message ... Cells can contain text with length up to 32k. But a msgbox can only display 1024 characters. No! The problem isn't msgbox-related! I see in integrated excel debugger that Len function returns 1024 for my cell with 4k of text! For Each ws In Worksheets MsgBox Len(ws.Cells(1, 1).Text) Next ws Also displayes 1024 in message box. Somebody - help me, please! :) -- Alexei Baskakov |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA: Worksheet cell .Text property: 1024 bytes text len limit
Why can't you use the .value property?
loyso wrote: Hi! My case: I have an xls file with the only one worksheet. The first cell of this worksheet contains a text more then 4 kb length. The .Text property for that Cell returns only first 1024 characters. I see this in the following example: For Each ws In Worksheets MsgBox ws.Cells(1, 1).Value MsgBox ws.Cells(1, 1).Text Next ws Q: How can I get whole text from this cell? Thanks in advance! Bye. -- Alexei Baskakov -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA: Worksheet cell .Text property: 1024 bytes text len limit
This may illustrate loyso's problem:
Option Explicit Sub testme() Dim myCell As Range Set myCell = ActiveSheet.Range("a1") myCell.Formula = "=rept(""asdf "",500)" With myCell Debug.Print "Value length: " & Len(myCell.Value) Debug.Print "Text Length: " & Len(myCell.Text) End With End Sub Returned: Value length: 2500 Text Length: 1024 Peter T wrote: Is the cell a formula beginning with =. If so you can neither Read nor Write a formula with length 1024 including the "=". Otherwise, as I said previously a cell can contain text up to the 32K character length limit, as my example routine demonstrated. Regards, Peter T "loyso" wrote in message ... Cells can contain text with length up to 32k. But a msgbox can only display 1024 characters. No! The problem isn't msgbox-related! I see in integrated excel debugger that Len function returns 1024 for my cell with 4k of text! For Each ws In Worksheets MsgBox Len(ws.Cells(1, 1).Text) Next ws Also displayes 1024 in message box. Somebody - help me, please! :) -- Alexei Baskakov -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA: Worksheet cell .Text property: 1024 bytes text len limit
Perhaps I misunderstood loyso's problem, and loyso - apologies if I did.
As you say, why not return myCell.Value, or as Value is the default property simply: myVar = myCell Regards, Peter T "Dave Peterson" wrote in message ... This may illustrate loyso's problem: Option Explicit Sub testme() Dim myCell As Range Set myCell = ActiveSheet.Range("a1") myCell.Formula = "=rept(""asdf "",500)" With myCell Debug.Print "Value length: " & Len(myCell.Value) Debug.Print "Text Length: " & Len(myCell.Text) End With End Sub Returned: Value length: 2500 Text Length: 1024 Peter T wrote: Is the cell a formula beginning with =. If so you can neither Read nor Write a formula with length 1024 including the "=". Otherwise, as I said previously a cell can contain text up to the 32K character length limit, as my example routine demonstrated. Regards, Peter T "loyso" wrote in message ... Cells can contain text with length up to 32k. But a msgbox can only display 1024 characters. No! The problem isn't msgbox-related! I see in integrated excel debugger that Len function returns 1024 for my cell with 4k of text! For Each ws In Worksheets MsgBox Len(ws.Cells(1, 1).Text) Next ws Also displayes 1024 in message box. Somebody - help me, please! :) -- Alexei Baskakov -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel VBA: Worksheet cell .Text property: 1024 bytes text len limit
This may illustrate loyso's problem: Thank you, friends! I've found the problem. The following repro illustrates my issue: Option Explicit Sub test() Dim s As String Dim i As Long Const SomeText As String = "This text is 32 characters long " Dim myCell As Range Set myCell = ActiveSheet.Range("a1") s = SomeText For i = 1 To 1000 s = s & SomeText Next Debug.Print "Original length: " & Len(s) ActiveSheet.Cells(1, 1).Value = s s = "" s = ActiveSheet.Cells(1, 1).Text With ActiveSheet.Cells(1, 1) Debug.Print "Value length: " & Len(myCell.Value) Debug.Print "Text Length: " & Len(myCell.Text) End With End Sub Output: Original length: 32032 Value length: 32032 Text Length: 1024 Resolution: I'm using .Value everywhere instead of .Text and all is ok. -- Alexei Baskakov |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel text export limit - 1024 per line (not cell), workaround? | Excel Discussion (Misc queries) | |||
Importing text--cell text limit? | Excel Discussion (Misc queries) | |||
Display text 1024 characters in a cell | Excel Worksheet Functions | |||
Range.Formula in Excel 2003 gives Error reading formulas just below 1024 bytes | Excel Programming | |||
Help with text box limit (1024) | Excel Programming |