255 character limit

[Guest]

I am using the macro1 to grab values from a closed workbook. Column Z
contains text cells, many of which have text strings of approximately 1000
characters...so I am running into problems with the 255 character limit. Is
there a way to modify the “Looper†macro at
http://support.microsoft.com/kb/213841/ or some different macro in order to
pull in the full text length of those cells in column z? Thanks! -Noah

Sub Macro1()
With Range("A1:Z1000")
.FormulaR1C1 = "='[Book1.xls]Sheet1!RC"
.Formula = Range("A1:Z1000").Value
End With

 

[Tom Ogilvy]

Dim sh as Sheet, bk as Workbook
Application.ScreenUpdating = False
set sh = Activesheet
set bk = workbooks.Open("C:\MyFolder\Book1.xls")
sh.Range("A1:Z1000").Value = _
bk.Worksheets("Sheet1").Range("A1:Z1000").Value
bk.Close SaveChanges:=False
Application.ScreenUpdating = True

Untested, but see if this is acceptable/works.

 

[Peter T]

Cell formula length limit is 1024 though can be a bit less. But the .Value
limit is 32K for text strings (though not displayed).

Try changing

.Formula = Range("A1:Z1000").Value
to

.Value = Range("A1:Z1000").Value

In the sample you posted I think you are missing an apostrophe before the !
in the formula. string

Regards,
Peter T

I am using the macro1 to grab values from a closed workbook. Column Z
contains text cells, many of which have text strings of approximately 1000
characters...so I am running into problems with the 255 character limit. Is
there a way to modify the "Looper" macro at
http://support.microsoft.com/kb/213841/ or some different macro in order to
pull in the full text length of those cells in column z? Thanks! -Noah

Sub Macro1()
With Range("A1:Z1000")
.FormulaR1C1 = "='[Book1.xls]Sheet1!RC"
.Formula = Range("A1:Z1000").Value
End With

 

[Tom Ogilvy]

Possibly the problem has already manifested by the time that code is
executed:

http://support.microsoft.com/kb/211878/en-us
XL2000: Linked Formula May Return a Maximum of 255 Characters

--
Regards,
Tom Ogilvy

Cell formula length limit is 1024 though can be a bit less. But the .Value
limit is 32K for text strings (though not displayed).

Try changing

.Formula = Range("A1:Z1000").Value
to

.Value = Range("A1:Z1000").Value

In the sample you posted I think you are missing an apostrophe before the !
in the formula. string

Regards,
Peter T

I am using the macro1 to grab values from a closed workbook. Column Z
contains text cells, many of which have text strings of approximately 1000
characters...so I am running into problems with the 255 character limit. Is
there a way to modify the "Looper" macro at
http://support.microsoft.com/kb/213841/ or some different macro in order to
pull in the full text length of those cells in column z? Thanks! -Noah

Sub Macro1()
With Range("A1:Z1000")
.FormulaR1C1 = "='[Book1.xls]Sheet1!RC"
.Formula = Range("A1:Z1000").Value
End With

 

[Peter T]

Hi Tom,

Yes, you and the article are right. What I posted works if the source file
is open but if closed strings are truncated to 255.

Also, I can't get the RC method to work with a closed file, maybe I'm not
getting the formula string right. I can though do this -

Sub Test()
Dim sFla As String

sFla = "='C:\My Documents\Excel\[Tmp.xls]Sheet1'!A1"
Range("A1").Formula = sFla
Range("A1").AutoFill Range("A1:A1000")
Range("A1:A1000").AutoFill Range("A1:Z1000")
Range("A1:Z1000").Value = Range("A1:Z1000").Value

End Sub

But if the source is closed strings are max 255

The OP should use the method you posted, perhaps first try and reference the
file to check if it is already open

Regards,
Peter T

Possibly the problem has already manifested by the time that code is
executed:

http://support.microsoft.com/kb/211878/en-us
XL2000: Linked Formula May Return a Maximum of 255 Characters

--
Regards,
Tom Ogilvy

Cell formula length limit is 1024 though can be a bit less. But the ..Value
limit is 32K for text strings (though not displayed).

Try changing


In the sample you posted I think you are missing an apostrophe before

the

!
in the formula. string

Regards,
Peter T

I am using the macro1 to grab values from a closed workbook. Column Z
contains text cells, many of which have text strings of approximately 1000
characters...so I am running into problems with the 255 character

limit.
Is

there a way to modify the "Looper" macro at
http://support.microsoft.com/kb/213841/ or some different macro in

order
to

pull in the full text length of those cells in column z? Thanks! -Noah

Sub Macro1()
With Range("A1:Z1000")
.FormulaR1C1 = "='[Book1.xls]Sheet1!RC"
.Formula = Range("A1:Z1000").Value
End With