How do I get around the 255 char limit?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dave H - 09 Jun 2006 14:05 GMT

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

--
Dave H

Reply to this Message

macropod - 11 Jun 2006 09:19 GMT

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

Signature

macropod
[MVP - Microsoft Word]

> I am linking a master spreadsheet to numerous spreadsheets. I am
> pulling in text back to the master spreadsheet. I know that excel has
[quoted text clipped - 4 lines]
> overload my computer. Is there a way to trick excel into getting all
> this without opening all these files?

Reply to this Message

Dave H - 12 Jun 2006 21:35 GMT

I am using excel 2003 when I choose not to update excel drops th
characters over 255. Opening each spreadsheet and then closing them i
a great idea however in this case I have about 80 to 90 linke
spreadsheets and each one is suffiecently large that it takes 1 to
minutes to open each one. So to run a macro could easily take upto
to 3 hrs to run and I don't think my users would wait that long.
macropod Wrote:

> Hi Dave,
>
[quoted text clipped - 38 lines]
> --
> Dave

--
Dave H

Reply to this Message

macropod - 13 Jun 2006 13:25 GMT

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

Signature

macropod
[MVP - Microsoft Word]

> 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
[quoted text clipped - 45 lines]
> > --
> > Dave H

Reply to this Message