Hello

This is related to this Thread:
http://www.excelforum.com/excel-prog...de-please.html
and specifically posts around about here:_...
http://www.excelforum.com/excel-prog...-please-3.html
_.. with emphasis and concerning the bit about
“Second attempt at code to get data from Big Closed Workbook”

_.................
So I am using the
ExecuteExcel4Macro Method
https://msdn.microsoft.com/en-us/lib.../ff193589.aspx
http://www.excelforum.com/showthread...t=#post4483230
http://www.excelforum.com/showthread...t=#post4483344

I used this successfully answering threads concerning getting values from a closed Excel Workbook.
But I am having some problems myself now when using it to get at a long column of values from a closed workbook into a one dimensional Array ( Note: I am not too interested in this Thread in alternative ways to get at this info, as I am looking at that in parallel. Specifically I would like to get this way up and running ) . I am wanting to consider here Looping in each row value in the column into a 1 Dimensional Array using specifically the ExecuteExcel4Macro Method or something very similar...

Here just the start of my data, ( in my final file I am looking at 679239 Rows. )

Using Excel 2007 32 bit
Row\Col
I
J
K
1
Food Product is at Row 1
2
Butter, salted is at Row 2
3
Butter, salted is at Row 3
4
Butter, salted is at Row 4
5
Butter, salted is at Row 5
6
Butter, salted is at Row 6
7
Butter, salted is at Row 7
8
Butter, salted is at Row 8
9
Butter, salted is at Row 9
10
Butter, salted is at Row 10
ASheetToIgnoor



The sticking point is that the speed of the code, Per Loop**, appears to be related to the number of rows in the closed Workbook. I do not mean that it takes longer for more rows – that is obvious. What I do not understand is why the speed per Loop is a factor of the Workbook ( row ) Size

I have stripped the main code where I have the problem down to the minimum to both help explain it and make it easier for someone to help.

I get similar results on 3 independent systems
_ 32 Bit Computer with Vista, XL 2007 32 Bit
_ 32 Bit Computer with Vista, XL 2010 32 Bit
_ 64 Bit Computer with WIN 7 XL 2007 32 Bit

So To demo this problem you need a Closed Workbook with a lot of rows in one column., say column J.
I upload 2 example files with 10000 and 679239 filled rows respectively
ClosedWorkbook10000.xlsx
https://app.box.com/s/72u1rsejfqgzkalf612vsdw0cejm98ez
ClosedWorkbook.xlsx
https://app.box.com/s/7l7dv0wnw29pp680am2mn2vdifga0p8v

Here a Demo Code
Sub TestEE14MUSDAToDBArrayThirdBookClsdWorkbook()
It pulls in the rows one by one into a 1 Dimensional Array of String Element Types for
Rem 1 10000 Row Workbook ClosedWorkbook10000.xlsx
And
Rem 2 Deep Column Workbook 679239 Rows ClosedWorkbook.xlsx

Code:
http://www.excelforum.com/showthread...49#post4485749

The code given is also in this file:
MacrosClsdWorkbook.xlsm
https://app.box.com/s/rbxjqhy7lenq4r5wi1jtl56d6v2v534z



The code should be run from a Workbook in the same Folder as the .xlsx data files , which should be closed when running the program.::::..
_::::...I note that the code is much faster if the Workbook is open. My experiments have shown that the ExecuteExcel4Macro retrieves data from the copy “opened” File in such a case and not on the actual saved File. I think this makes sense with some experiments I did in the Links referenced at the start of this Thread. I think the explanation here is that the ExecuteExcel4Macro executes “internal” macros that are done when you Hit ENTER in a Spreadsheet Cell,- In this case when you have a link to another cell in that also goes to either the open or the closed workbook depending on if it is open or not.
What puzzles me a bit is

_1 ) why it is generally slower going to a closed Workbook ?

_2 ) why it should the time per Loop** be a factor of how large** the workbook is ?

It would be helpful to know
_3) if I have hit a strange fundamental limitation here of the ExecuteExcel4Macro making it not much use for deep columns

_4 ) Finally if anyone has the time to run these codes on a “better” system than I have, so as to check if that influences things. In the other ways I have been considering it made , for example , a very large difference running the codes on a system with 64 Bit Excel. However, the issue there appeared to be the Way Excel was handling the memory ( The memory it “sets aside” ..( and seemingly never clears ) ) when Files were opened. I was not expecting similar problems with the closed Workbook case. ( But I have seen so many weird unexplainable issues here that I would believe anything and be keen to experiment on as many things as possible... )

Note: I have hardcoded the stop row to a small number initially. If you run the code you may want to start with the lower row numbers rather than run the full rows initially, so as not to hang up your system should it appear that a full run would take ages ... as it does for me for the larger File,

Any feedback would be welcome. Possibly I am doing something fundamentally wrong.

_5) I thought I am picking data from a “XLM” File somewhere. ?? Maybe there is a more efficient way to do that. (_.......... I am not interested in this Thread in alternatives such as transferring the data somewhere else and retrieving it for there – I am considering that option in parallel as a separate issue
http://www.excelforum.com/excel-prog...al-arrays.html
_......... )

Thanks,
Alan