How do I get around the 255 char limit?

Dave H · External Since: Jun 09, 2006 Posts: 1

I am linking a master spreadsheet to numerous spreadsheets. I am
pulling in text back to the master spreadsheet. I know that excel has
a 255 character limit for linked cells. I noticed however that when I
initially establish the link I can pull in 700+ characters. I have
realized that you get all the characters when the source spreadsheet is
open. In my case opening that many large spreadsheets would probably
overload my computer. Is there a way to trick excel into getting all
this without opening all these files?

--
Dave H

macropod · External Since: Sep 27, 2005 Posts: 994

Hi Dave,

If you open the workbook without allowing it to update the links, they'll
retain the same amount of displayed data as when they were last saved. This
may allow you to open just those source workbooks that have been updated
since the target workbook was last saved. If you then open and close the
updated source workbooks sequentially, that should keep the performance hit
to a minimum. To make life easier, all this is possible through code. For
example, MS provides sample code for opening linked workbooks at:
http://support.microsoft.com/kb/213647/en-us
For your purposes, you'd probably want to modify the MS code to test the
source workbook's date/time stamp before opening it and to it again before
moving on to the next one.

Cheers

--
macropod
[MVP - Microsoft Word]

"Dave H" wrote in message

>
> I am linking a master spreadsheet to numerous spreadsheets. I am
> pulling in text back to the master spreadsheet. I know that excel has
> a 255 character limit for linked cells. I noticed however that when I
> initially establish the link I can pull in 700+ characters. I have
> realized that you get all the characters when the source spreadsheet is
> open. In my case opening that many large spreadsheets would probably
> overload my computer. Is there a way to trick excel into getting all
> this without opening all these files?
>
>
> --
> Dave H

Dave H · External Since: Jun 12, 2006 Posts: 1

I am using excel 2003 when I choose not to update excel drops the
characters over 255. Opening each spreadsheet and then closing them is
a great idea however in this case I have about 80 to 90 linked
spreadsheets and each one is suffiecently large that it takes 1 to 2
minutes to open each one. So to run a macro could easily take upto 2
to 3 hrs to run and I don't think my users would wait that long.
macropod Wrote:
> Hi Dave,
>
> If you open the workbook without allowing it to update the links,
> they'll
> retain the same amount of displayed data as when they were last saved.
> This
> may allow you to open just those source workbooks that have been
> updated
> since the target workbook was last saved. If you then open and close
> the
> updated source workbooks sequentially, that should keep the performance
> hit
> to a minimum. To make life easier, all this is possible through code.
> For
> example, MS provides sample code for opening linked workbooks at:
> http://support.microsoft.com/kb/213647/en-us
> For your purposes, you'd probably want to modify the MS code to test
> the
> source workbook's date/time stamp before opening it and to it again
> before
> moving on to the next one.
>
> Cheers
>
> --
> macropod
> [MVP - Microsoft Word]
>
>
> "Dave H" Dave.H.295e65.RemoveThis@news.excelbanter.com wrote in message
>
>
> I am linking a master spreadsheet to numerous spreadsheets. I am
> pulling in text back to the master spreadsheet. I know that excel
> has
> a 255 character limit for linked cells. I noticed however that when
> I
> initially establish the link I can pull in 700+ characters. I have
> realized that you get all the characters when the source spreadsheet
> is
> open. In my case opening that many large spreadsheets would probably
> overload my computer. Is there a way to trick excel into getting all
> this without opening all these files?
>
>
> --
> Dave H

--
Dave H

macropod · External Since: Sep 27, 2005 Posts: 994

Hi Dave,

I use Office 2000, which retains the full string unless the links are
updated. Shame it appears not to Work with O2003.

If you adopt the alternative approach I suggested, hopefully only a few
workbooks would need opening each time.

Yet another option would be to use a macro to retrieve the values from the
source workbooks without opening them. For information on how to go about
this, see: http://www.j-walk.com/ss/excel/tips/tip82.htm.

Cheers

--
macropod
[MVP - Microsoft Word]

"Dave H" wrote in message

>
> I am using excel 2003 when I choose not to update excel drops the
> characters over 255. Opening each spreadsheet and then closing them is
> a great idea however in this case I have about 80 to 90 linked
> spreadsheets and each one is suffiecently large that it takes 1 to 2
> minutes to open each one. So to run a macro could easily take upto 2
> to 3 hrs to run and I don't think my users would wait that long.
> macropod Wrote:
> > Hi Dave,
> >
> > If you open the workbook without allowing it to update the links,
> > they'll
> > retain the same amount of displayed data as when they were last saved.
> > This
> > may allow you to open just those source workbooks that have been
> > updated
> > since the target workbook was last saved. If you then open and close
> > the
> > updated source workbooks sequentially, that should keep the performance
> > hit
> > to a minimum. To make life easier, all this is possible through code.
> > For
> > example, MS provides sample code for opening linked workbooks at:
> > http://support.microsoft.com/kb/213647/en-us
> > For your purposes, you'd probably want to modify the MS code to test
> > the
> > source workbook's date/time stamp before opening it and to it again
> > before
> > moving on to the next one.
> >
> > Cheers
> >
> > --
> > macropod
> > [MVP - Microsoft Word]
> >
> >
> > "Dave H" Dave.H.295e65 RemoveThis @news.excelbanter.com wrote in message
> >
> >
> > I am linking a master spreadsheet to numerous spreadsheets. I am
> > pulling in text back to the master spreadsheet. I know that excel
> > has
> > a 255 character limit for linked cells. I noticed however that when
> > I
> > initially establish the link I can pull in 700+ characters. I have
> > realized that you get all the characters when the source spreadsheet
> > is
> > open. In my case opening that many large spreadsheets would probably
> > overload my computer. Is there a way to trick excel into getting all
> > this without opening all these files?
> >
> >
> > --
> > Dave H
>
>
> --
> Dave H