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

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.

--
Regards,
Tom Ogilvy



"Noah" wrote in message
...
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

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

"Noah" wrote in message
...
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

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


"Peter T" <peter_t@discussions wrote in message
...
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

"Noah" wrote in message
...
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

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

"Tom Ogilvy" wrote in message
...
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


"Peter T" <peter_t@discussions wrote in message
...
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

"Noah" wrote in message
...
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