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

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
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

G
  • Question Question
remove columns from multiple Excel-files in a folder
Replies
5
Views
213
gjvv
G
T
  • Solved
MATCH function
2
Replies
14
Views
776
Fluff
Fluff
E
  • Question Question
Repeating Unicode Character Issue (Publication calendar)
Replies
0
Views
325
EDV
E
T
  • Question Question
VBA - Remove only the first occurence of a duplicate based on a dropdown.
Replies
1
Views
262
mumps
mumps
Y
  • Question Question
Pivot Table deletes on opening of workbook
Replies
3
Views
231
cooleomter
C

Forum statistics

Threads
1,223,912
Messages
6,175,340
Members
452,637
Latest member
Ezio2866

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