Hello,
This Thread is strongly related to this one_....
http://www.excelforum.com/excel-prog...de-please.html
http://www.excelforum.com/excel-prog...-please-3.html
_...I am looking for support here to help me solve that one...

_Very briefly what I am trying to do there is:
_ take data from 3 very deep columns in a large database File, “NutritionalValues.___”, and sort the data into very long rows in another file. ( “BDSept2016.xls__” )

_ We are coming to the conclusion in That that Thread, that Excel has a “ “Memory” of the Memory “ it uses, which cannot be cleared without closing and restarting Excel. Closing the big data file after extracting the data does not help. In effect once I have opened up the data File, then I have hardly any memory left ( in Excel 32Bit at any rate ) . So I do not get far in producing my sorted rows in File “DBSept2016.xls__” before it bombs out.
_ I do have a fairly efficient VBA code based on Arrays that sorts and makes the rows in the File “DBSept2016.xlsx” ( I cannot get far with it on my 32 Bit Excel, but one Member did get it to work up to the full 8790 rows with his 64 Bit Excel )

_ My latest thought to help me get that Thread Solved is the following:

_ Open just the big data file and write ( Print ) out the columns to a simple text File. I should usually have enough Memory left to do that !!

_ Close and restart Excel , ( But don’t open the data File !!! ). Open just the File for sorted data, “DBSept2016”, ( make it a macro “DBSept2016.xlsm – no big memory difference there I think. Or maybe it is *** ) –

_ Use a VBA code to get the columns of data into the three 1 Dimensional Arrays ( which can then be used in a version of my original fairly efficient VBA code based on Arrays that sorts and makes the rows in the File “DBSept2016.”

_ So over in the Test Area Sub Forum
_ This Post shows again what I want to do, ( That is to say make a .txt File from the three deep Columns of data in the big excel data File, “NutritionalValues.---”, and then put those three columns in three VBA Arrays):
http://www.excelforum.com/showthread...t=#post4484311


_ This Post gives a code that does exactly what I want in terms of making the Text File:
http://www.excelforum.com/showthread...t=#post4484331


_ This Post gives a code that does uses the text File to make the Three Arrays exactly as I want. ( BTW. they have the headers in, but I do not necessarily need those – I am principally interested in the data in ).
http://www.excelforum.com/showthread...t=#post4484332

( All the relevant and complete Files are also uploaded in those posts )

_.....
_ For the codes I wrote, I am using the basic VBA type stuff of the form like_...
Open FullTextFilePathAndName For Input As___
Open strFullPathAndFileNameForPrint For Output As ___
_.......and looping to read in or print out a line at a time_...

_ What I am asking here is for better ways to do this. ( preferably still using code ). I expect Columns can be “chucked out” in some very efficient ways to a Text File. And in particular I expect putting a column from a Text File efficiently into an Array is possible. ( I did have the idea of putting and getting the data with a Microsoft Scripting Runtime Dictionary, or a VBA Collection – but I believe you only have the possibility of looping stuff in and out of those...
_ There are a lot of “Queer” ry Query things and “ADODBy” things out there I think, but I have little idea about them_....

_No rush, - I am getting clued up now on the whole ADODB und similar type scene now, but I am posting the question here as someone may see immediately a way to do this. If anyone can help it would also be great if they can explain how / what is going on in any code suggestion. – I can handle standard VBA quite well, but using some of these external libraries, “Queer” ry Query things and the such are a bit of a mystery to me. ..
( _ By the way I realise I could tidy , simplify and speed up my codes a little bit, Mostly here I am asking for fundamentally different ( and most likely much better ) was of doing it )

Thanks
Alan

***P.s. A small related question.. If I have learnt one thing from that other thread it is that how “Big” a File is and how a File effects VBA Memory limits are two different things!! – Now I have noticed that the size difference between a moderately big .xlsx File and the same File after putting a few c codes in it and saving as .xlsm is quite small. I am being fooled again by Excel here ? Has Excel “put aside” ( for as long as Excel is open ) an extra bit of memory once I save a File as .xlsm, which does not necessarily show up in the File size ?