Read/Copying text directly from formula bar rather than cell

[alucas]

Hi,

Using Excel 2003, I have some cells that contain text strings that are >1024 chars which get truncated when trying to copy them. The formula bar contains the full text I need however.

The only options pertaining to the Formula bar I can see refer to either switching it on/off (Application.DisplayFormulaBar = {True/False}) but not copying the text from it.

Is there a way to simply copy the contents of the formula bar rather than the cell using VBA?

I thought about perhaps using "Application.Sendkeys" but wondered if there was a more elegant solution available through VBA.

Thanks for any help/advice

-A-

 

[Andrew Poulsom]

In Excel 2000 I can copy a cell containg more than 1024 characters without losing any text. Is that not also the case with Excel 2003?

 

[alucas]

Hi Andrew, thanks for the reply.

I am getting the limits for Excel from this page on Microsofts site:

http://support.microsoft.com/?kbid=264626

Specifically:


Length of cell 32,767 characters. Only 1,024 display in a
contents (text) cell; all 32,767 display in the formula bar.</pre>

 

[Jon von der Heyden]

What is displayed and what it contains are two different things. Although you only see a max of 1024 characters it still contains the full amount. And you can also copy the whole lot to another cell.

Let's test it.

Type in A1: =REPT("a",3000)
In A2: =LEN(A1)
Copy A1 and paste values into A3
In A4: =LEN(A3)

Note that the full 3000 characters are copied across.

 

[alucas]

Hi Jon,

Thanks for the reply.

When trying to copy the contents of the cell into a VBA string, it will only copy the first 1024 characters:

myString = myWkb.Worksheets("Sheet1").range("D" & myRow).Text
Len(myString)  ' Maximum of 1024 chars even if the text in the cell is longer

I just want to dump the entire cell contents into a string for an HTML email I'm building.

 

[Jon von der Heyden]

That is the limitation of the Text property. Use the Value property instead. In A1 enter the following:
=REPT("a",3000)

Test this;

[COLOR=blue]Public[/COLOR] [COLOR=blue]Sub[/COLOR] test1()
    [COLOR=blue]Dim[/COLOR] strText [COLOR=blue]As[/COLOR] [COLOR=blue]String[/COLOR]
    [COLOR=blue]Dim[/COLOR] strValue [COLOR=blue]As[/COLOR] [COLOR=blue]String[/COLOR]
 
    strText = Range("A1").Text
    strValue = Range("A1").Value
 
    [COLOR=blue]Debug.Print[/COLOR] "Text:" & [COLOR=blue]Len[/COLOR](strText), "Value:" & [COLOR=blue]Len[/COLOR](strValue)
[COLOR=blue]End[/COLOR] [COLOR=blue]Sub[/COLOR]

A string is not limited to 1024. Quoting VBA help:

There are two kinds of strings: variable-length and fixed-length strings.

• <LI class=LB1>A variable-length string can contain up to approximately 2 billion (2^31) characters.
  
• A fixed-length string can contain 1 to approximately 64K (2^16) characters.

 

[alucas]

That's where I was getting confused.

Brilliant. Works a treat.

Many thanks for your help!