255 character limitation of indirect.ext and pull-function

[Hond70]

Help, this is a disaster... I discovered that indirect.ext (add in of
morefunc) only pulls the first 255 characters of the contents of a
cell, when the target file is closed. It does pull out the full 1024
characters when both files are open.
This is not what indirect.ext was designed for.

I also tried Grove's pull-function, but unfortunately this also has
the 255 characters limitation.
Only when using the direct
=c:\test.xls[sheet1]!A1
results in a full loaded cell of the max. visible 1024 characters.

I can't open 172 (!) files together to pull data into my file... you
would need 2 Gigs of RAM.....
Anybody has a solution? I discovered this after 2 months of work on
hundreds of sheets that will pull data out of another hundreds of
files (questionnaires). Now I really freaking out!!!!!!!

I use formulas like
=IF(ISERROR(SETV(INDIRECT.EXT("'"&Locaties!$A$1&"["&A11&".xls]"&$C$3&"'!$B$"&$D$3)));"";IF(GETV()
<> 0;GETV();""))
and
=IF(ISERROR(INDIRECT.EXT("'"&Locaties!$A$1&"["&A10&".xls]"&$C$3&"'!$B$"&$D$3));"";INDIRECT.EXT("'"&Locaties!$A$1&"["&A10&".xls]"&$C$3&"'!$B$"&$D$3))

 

[Charles Williams]

You should really be using a proper database for this rather than excel, but
if you have to continue with Excel I would suggest you write some VBA to
open the files one at a time in manual mode and extract the data you want,
either using INDIRECT if thats easiest or any other method that works for
you.

Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm

Help, this is a disaster... I discovered that indirect.ext (add in of
morefunc) only pulls the first 255 characters of the contents of a
cell, when the target file is closed. It does pull out the full 1024
characters when both files are open.
This is not what indirect.ext was designed for.

I also tried Grove's pull-function, but unfortunately this also has
the 255 characters limitation.
Only when using the direct
=c:\test.xls[sheet1]!A1
results in a full loaded cell of the max. visible 1024 characters.

I can't open 172 (!) files together to pull data into my file... you
would need 2 Gigs of RAM.....
Anybody has a solution? I discovered this after 2 months of work on
hundreds of sheets that will pull data out of another hundreds of
files (questionnaires). Now I really freaking out!!!!!!!

I use formulas like
=IF(ISERROR(SETV(INDIRECT.EXT("'"&Locaties!$A$1&"["&A11&".xls]"&$C$3&"'!$B$"
&$D$3)));"";IF(GETV()
<> 0;GETV();""))
and

=IF(ISERROR(INDIRECT.EXT("'"&Locaties!$A$1&"["&A10&".xls]"&$C$3&"'!$B$"&$D$3
));"";INDIRECT.EXT("'"&Locaties!$A$1&"["&A10&".xls]"&$C$3&"'!$B$"&$D$3))

 

[Frank Kabel]

Hi
I totally agree with Charles. Excel is the wrong application for this.
This sounds like a database application (esp. as your data seems to be
scattered among 172 files). If you may explain what your business logic
behind this is we could give you some ideas how to proceede with a
migration to a more suited environment

--
Regards
Frank Kabel
Frankfurt, Germany

Help, this is a disaster... I discovered that indirect.ext (add in of
morefunc) only pulls the first 255 characters of the contents of a
cell, when the target file is closed. It does pull out the full 1024
characters when both files are open.
This is not what indirect.ext was designed for.

I also tried Grove's pull-function, but unfortunately this also has
the 255 characters limitation.
Only when using the direct
=c:\test.xls[sheet1]!A1
results in a full loaded cell of the max. visible 1024 characters.

I can't open 172 (!) files together to pull data into my file... you
would need 2 Gigs of RAM.....
Anybody has a solution? I discovered this after 2 months of work on
hundreds of sheets that will pull data out of another hundreds of
files (questionnaires). Now I really freaking out!!!!!!!

I use formulas like
=IF(ISERROR(SETV(INDIRECT.EXT("'"&Locaties!$A$1&"["&A11&".xls]"&$C$3&"'
!$B$"&$D$3)));"";IF(GETV()
<> 0;GETV();""))
and

=IF(ISERROR(INDIRECT.EXT("'"&Locaties!$A$1&"["&A10&".xls]"&$C$3&"'!$B$"
&$D$3));"";INDIRECT.EXT("'"&Locaties!$A$1&"["&A10&".xls]"&$C$3&"'!$B$"&
$D$3))

 

[Hond70]

Thanks for the comments, but I really need excel, because most of th
data are quantitative and only a part is qualitative.
It is impossible to change application at this stage.

Any suggestions?

Thank

 

[Charles Williams]

Use Excel VBA

regards
Charles

 

[Hond70]

See perfect answer here:

http://www.experts-exchange.com/Applications/MS_Office/Excel/Q_21008857.html

Thanks for the help

 

[Charles Williams]

The question is very different and it doesnt show the presumably Excel VBA
answer.

regards
Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm

 

[Michelle]

Do you know how to get the footer to exceed 255 character
limitation?