Load array from closed workbook range

[Journeyman3000]

Hi all,

I am looking to load an array from a range in a closed workbook - that is, a workbook that must remain closed, nor can it be manipulated in anyway.

I can do this using the following code:

Please Login or Register  to view this content.

However, this simply inserts the data into a worksheet. I need it to go into an array.

How would this be done?

[Norie]

Can't you replace this,

Please Login or Register  to view this content.

with this, where arr is the array, declared as Variant but with no dimensions.

Please Login or Register  to view this content.

[Journeyman3000]

Thanks for the resonse.

You can only use the .value if you've already inserted the data into the sheet using .formula.

I need to populate an array only without pulling the data onto a sheet first.

I'm certain this must be possible but I don't know how this is done.

[Norie]

The only other way I can think would be to use ADO but that might not be appropriate.

For example the data might not be formatted/structured properly for ADO or the file might get locked when you connect to it.

Is putting the values on a sheet a definite no go?

Actually, just thought of another possibility - Evaluate.

[Journeyman3000]

Thanks for the ADO suggestion but I would like to avoid that. I can open a file, get the range and close the file, but this causes saving requirements from the opened sheet when switching between sheets to get data.

I can create a sheet, populate the sheet with the data, create the array from the sheet, close the sheet. But that slows everything down and isn't really an elegant solution. Unless there is a way to create the sheet as a worksheet in code without actually making a new sheet in the book?

ADO is a no go and seems to me that it's more trouble than its worth.

And I've never heard of the evaluate used in this way. Can you provide an example?

[apo]

What about accessing the Workbook using "GetObject" and loading the values into an ArrayList.. ?

Please Login or Register  to view this content.

[Norie]

Why do you want to avoid ADO?

Also, what 'saving requirements' do you refer to and what switching between sheets would there be?

This code would open the workbook, grab the data and then close the workbook.

Please Login or Register  to view this content.

apo

I don't think the workbook is open.

[apo]

Hi Norrie..

I initially didn't include the line..

Please Login or Register  to view this content.

but without it.. I get the "File in Use" dialogue when i try to later open the file.

[Norie]

apo

What I mean is the workbook is closed and the OP doesn't want to openness it.

As far as I can see your code would only work if the workbook was open.

[apo]

yeah.. i think you're right.. the Workbook is open.. just not visible..

[Journeyman3000]

Thanks all who contributed to my question.

The code line below worked exactly to resolve the issue.

Please Login or Register  to view this content.

Cheers all and take a rep for the help.