255 character limit

N

Noah

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
 
T

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.
 
P

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

Noah said:
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
 
T

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


Peter T said:
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 said:
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
 
P

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

Tom Ogilvy said:
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 said:
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

Noah said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top