Thread: Referencing Range in UnOpened Workbook

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

Hi Vlado,

See Ron de Bruin at:

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

and

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



---
Regards,
Norman



"Vlado Sveda" <VladoSveda@discussions.microsoft.com> wrote in message
news:7C27E618-9252-4F05-B66E-5652CC5C76EC@microsoft.com...
> 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



"Norman Jones" wrote:

> Hi Vlado,
>
> See Ron de Bruin at:
>
> http://www.rondebruin.nl/ado.htm
>
> and
>
> http://www.rondebruin.nl/copy7.htm
>
>
>
> ---
> Regards,
> Norman
>
>
>
> "Vlado Sveda" <VladoSveda@discussions.microsoft.com> wrote in message
> news:7C27E618-9252-4F05-B66E-5652CC5C76EC@microsoft.com...
> > 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
> >
>
>
>

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!

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Norman Jones wrote in <news:<#DRZ8h7gGHA.2456@TK2MSFTNGP04.phx.gbl>

> Hi Vlado,
>
> See Ron de Bruin at:
>
> http://www.rondebruin.nl/ado.htm
>
> and
>
> http://www.rondebruin.nl/copy7.htm
>
>
>
> ---
> Regards,
> Norman
>
>
>
> "Vlado Sveda" <VladoSveda@discussions.microsoft.com> wrote in message
> news:7C27E618-9252-4F05-B66E-5652CC5C76EC@microsoft.com...
> > 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
> >

Hi Vlado

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

--
Regards Ron de Bruin
http://www.rondebruin.nl


"Vlado Sveda" <VladoSveda@discussions.microsoft.com> wrote in message news:7C27E618-9252-4F05-B66E-5652CC5C76EC@microsoft.com...
> 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
>

Hi keepITcool

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

--
Regards Ron de Bruin
http://www.rondebruin.nl


"keepITcool" <xrrcvgpbby@puryyb.ay> wrote in message news:xn0emu86k8kzcaw001keepitcoolnl@news.microsoft.com...
> 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!
>
> --
> keepITcool
> | www.XLsupport.com | keepITcool chello nl | amsterdam
>
>
> Norman Jones wrote in <news:<#DRZ8h7gGHA.2456@TK2MSFTNGP04.phx.gbl>
>
>> Hi Vlado,
>>
>> See Ron de Bruin at:
>>
>> http://www.rondebruin.nl/ado.htm
>>
>> and
>>
>> http://www.rondebruin.nl/copy7.htm
>>
>>
>>
>> ---
>> Regards,
>> Norman
>>
>>
>>
>> "Vlado Sveda" <VladoSveda@discussions.microsoft.com> wrote in message
>> news:7C27E618-9252-4F05-B66E-5652CC5C76EC@microsoft.com...
>> > 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
>> >

yep.

chello revoked the webspace for my keepITcool account.

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


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Ron de Bruin wrote in <news:<OPZNh3$gGHA.4388@TK2MSFTNGP05.phx.gbl>

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

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.



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Ron de Bruin wrote in <news:<OPZNh3$gGHA.4388@TK2MSFTNGP05.phx.gbl>

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