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?

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

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.

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" 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