Referencing Range in UnOpened Workbook

[Vlado Sveda]

Hello Gurus !

I need your help to solve my performance problem: I'm processing ~600
workbooks in a loop (punctually saying in 10 loops).
So I use ~6000 times construction like this:

....
Workbooks.Open MyCostsPath & Budget(i)
Set MyRange = Range("MyRangeName")

For Each MyCell In MyRange.Cells
....
....
Next MyCell
.....
ActiveWorkbook.Close

Is it possible to do it without opening workbooks ?
Thanks in advance !

Vlado Sveda

 

[Norman Jones]

Hi Vlado,

See Ron de Bruin at:

http://www.rondebruin.nl/ado.htm

and

http://www.rondebruin.nl/copy7.htm

 

[Vlado Sveda]

Thanks Norman,
but this is not the exact want i need to solve (sorry for unclear initial
question).
Ranges in my source workbooks are uncontinuos and my target range is
continuos (and transposed too), that's why I process it by:

For Each MyCell In MyRange.Cells
.....

So I need to reference Uncontinuos Range in Closed workbook and step
throught its cells.

Once more thanks !
Vlado

 

[keepITcool]

Vlado,

An other approach could be to use Indirect.Ext
worksheet function from MoreFunc.Xll addin.
(download the addin from Laurent Longre's website)
http://xcell05.free.fr/english/


But reconsider. Ado may be the way to go:

Use ADO to create 1 consolidated workbook with 60 sheets.
each containing the original values from MyRange
(ADO will do this VERY fast)

Then use VBA to open the temporary cons workbook
and iterate thru cells on the sheets.

Note that ADO will truncate any long text to 255 chars.
(and the workbooks must be saved calculated)

If you still want to open the 60 workbooks
with your own code:

Be sure to disable:
ScreenUpdating/Events/Calculation and PageBreaks.
Be sure to avoid:
Select/Activate.


Have fun!

 

[Ron de Bruin]

Hi Vlado

See also
http://www.rondebruin.nl/copy3.htm

 

[Ron de Bruin]

Hi keepITcool

I see that your site is working now ?
Problems with Chello ?

 

[keepITcool]

yep.

chello revoked the webspace for my keepITcool account.

xlSupport now redirects to:
members.chello.nl\jvolk\keepITcool

 

[keepITcool]

BTW:

Office12 includes a new OLEDB provider called

Provider=Microsoft.ACE.OLEDB.12.0;
Data Source=d:\my documents\mytable.xls;
Extended Properties=Excel 12.0;

it appears an interesting update of Jet4:

it reads both Excel 8 and Excel 12 files.
(no difference in settings, just use Extended properties Excel 12.0)

BUT it can read long strings. NO 255 char limit.