Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing Range in UnOpened Workbook
Hi Vlado,
See Ron de Bruin at: http://www.rondebruin.nl/ado.htm and http://www.rondebruin.nl/copy7.htm --- Regards, Norman "Vlado Sveda" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing Range in UnOpened Workbook
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing Range in UnOpened Workbook
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 Hi Vlado, See Ron de Bruin at: http://www.rondebruin.nl/ado.htm and http://www.rondebruin.nl/copy7.htm --- Regards, Norman "Vlado Sveda" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing Range in UnOpened Workbook
Hi Vlado
See also http://www.rondebruin.nl/copy3.htm -- Regards Ron de Bruin http://www.rondebruin.nl "Vlado Sveda" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing Range in UnOpened Workbook
Hi keepITcool
I see that your site is working now ? Problems with Chello ? -- Regards Ron de Bruin http://www.rondebruin.nl "keepITcool" wrote in message .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 Hi Vlado, See Ron de Bruin at: http://www.rondebruin.nl/ado.htm and http://www.rondebruin.nl/copy7.htm --- Regards, Norman "Vlado Sveda" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing Range in UnOpened Workbook
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 Hi keepITcool I see that your site is working now ? Problems with Chello ? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Referencing Range in UnOpened Workbook
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 Hi keepITcool I see that your site is working now ? Problems with Chello ? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Referencing a range within a workbook | Excel Programming | |||
Importing Data from unopened Workbook into an open Workbook | Excel Discussion (Misc queries) | |||
Functions referencing unopened workbooks | Excel Worksheet Functions | |||
Copy a range of cells in an unopened workbook and paste it to the current workbook | Excel Programming | |||
Referencing a range from another workbook | Excel Programming |