255 character limit

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
news:[email protected]...
> 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
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> > >
> >
> >
>
>