Referencing Range in UnOpened Workbook

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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!
 
yep.

chello revoked the webspace for my keepITcool account.

xlSupport now redirects to:
members.chello.nl\jvolk\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.
 
Back
Top