Indirect.ext pulls only first 255 characters of closed file

H

hond70

Board Regular
Joined
Apr 13, 2004
Messages
72
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 pulls out the 1024 characters when both files are open.
This is not where indirect.ext was designed for.

Anybody has a solution?

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

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Sort by date Sort by votes
Re: Indirect.ext pulls only first 255 characters of closed f

Thanks, but I don't want to use another function. With my formulas I bypass #REF! errors and zeros when cells are empty.
The pull function can't do that.

Anybody has another suggestion?
 
Upvote 0
Re: Indirect.ext pulls only first 255 characters of closed f

hond70 said:
Thanks, but I don't want to use another function. With my formulas I bypass #REF! errors and zeros when cells are empty.
The pull function can't do that.

Anybody has another suggestion?
Click to expand...

You can combine PULL() with V()...

Public Function V(Optional vrnt As Variant) As Variant
'
' Stephen Dunn
' 2002-09-12
'
Static vrntV As Variant
If Not IsMissing(vrnt) Then vrntV = vrnt
V = vrntV
End Function

To add it to your workbook:

Activate Tools|Macro|Visual Basic Editor;
Activate Insert|Module;
Copy the UDF above and paste it in the pane entitled "...(code)".
Activate File|Close and Return to Microsoft Excel.

Addendum: V() works like SETV()/GETV(). It's a single function for both.
 
Upvote 0
Re: Indirect.ext pulls only first 255 characters of closed f

:cry:
Alas! the pull-function doesn't work either. They all have the 255 character limitation when the file where is pulled from is closed.

I can't open 172 (!) files together to pull data into my file... you would need 2 Gigs of RAM.....

only when using the direct
=c:\test.xls[sheet1]!A1
results in a full loaded cell of the max. visible 1024 characters.

There must be a solution for both indirect.ext and Grove's pull-function.

Please.....?

It's 1.20 am, so I'll try to get some sleep... They'll fire me if this formula doesn't work! Oh dear...
 
Upvote 0
Re: Indirect.ext pulls only first 255 characters of closed f

hond70 said:
:cry:
Alas! the pull-function doesn't work either. They all have the 255 character limitation when the file where is pulled from is closed.

I can't open 172 (!) files together to pull data into my file... you would need 2 Gigs of RAM.....

only when using the direct
=c:\test.xls[sheet1]!A1
results in a full loaded cell of the max. visible 1024 characters.

There must be a solution for both indirect.ext and Grove's pull-function.

Please.....?
Click to expand...

Post a request/inquiry reply to Harlan at the worksheet.functions.
 
Upvote 0
Re: Indirect.ext pulls only first 255 characters of closed f

I can't open 172 (!) files together to pull data into my file... you would need 2 Gigs of RAM.....
Click to expand...
Why exactly are you using Indirect.ext or Harlan Grove’s Pull function? Is there some reason that you cannot open the files?

There are several macros that will copy data from various workbooks to a master workbook. These macros work on the basis of opening the first file, extracting data, closing the file, then opening the next file etc. This process is fully automated. At no stage are more than two workbooks open – the Master file and the file currently open.

Here is a typical example of this type of macro:
http://www.mrexcel.com/board2/viewtopic.php?t=80352

Also:
http://www.rondebruin.nl/ado.htm - Copy a range from closed workbooks (ADO)

In particular, see Mr. de Bruin’s macro “Copy a range from all files that you have selected with GetOpenFilename”

Perhaps these types of macros may interest you.

HTH

Mike
 
Upvote 0
Re: Indirect.ext pulls only first 255 characters of closed f

Thanks for the comments, but I'm not looking for a simple "copy contents of file a, b and c to file z".
My problem is that I want to pull out data with a file that is totally differently organized than the files containing the data.
If I open say file a and the "pull file" and transfer the data, close file a, open file b and transfer data, all the data from file a will be deleted. That's my prblem.
 
Upvote 0
Re: Indirect.ext pulls only first 255 characters of closed f

I would write an Excel VBA program that reads the appropriate cells of each of the files (presumably they are mostly in the same sort of format) and puts or adds the data to the destination workbook.

There is no need for this program to delete data unless you want it to.

If neccessary you can have a spec sheet that tells the program where to read stuff from (Book, Sheet, Range), and where to write it to (book, sheet, range) anf whether to add to or overwrite the destination data.
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Solved
MATCH function
2
Replies
14
Views
252
Fluff
Fluff
E
  • Question
Repeating Unicode Character Issue (Publication calendar)
Replies
0
Views
167
EDV
E
V
  • Solved
Extract first 3 characters & first 4 characters from a alphanumeric value
Replies
7
Views
664
vikrampnz
V
A
  • Question
Need to repeat the first row in every csv file created
Replies
1
Views
199
rpaulson
rpaulson
MrDB4Excel
  • Solved
DuckDuckGo Conditional Format
2
Replies
12
Views
398
MrDB4Excel
MrDB4Excel

Forum statistics

Threads
1,216,084
Messages
6,128,722
Members
449,465
Latest member
TAKLAM

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top