ExcelBanter - View Single Post

Erik

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

"Patrick Molloy" wrote:

you'll need to open the other workbooks to extract the data...either that or
use JET to read the data as if the workbooks were set up with proper range
named tables -unlikely

so are all the workbooks by themselves in a folder or all over the place?

I'll assume the latter, but that you have a table on sheet2 which lists the
full path and name of the 10 workbooks

to open and close :

SUB Tester()
DIM wb as Workbook
DIM rWBname as Range
for each rWBName in worksheets("Sheet2").Range("A1:A10") ' table of
workbook full names
'open each workbook using a loop
SET wb = Workbooks.Open(rWBname.Value)

' {do something}

'close before opening the next workbook
wb.Close False ' closes without saving
SET wb = nothing
next ' to process workbook in table

END SUB

"vmadan16" wrote in message
...
Hi Otto,

Thanks for replying, I dont want to open jus want to update the data from
the other workbooks to the Main workbook thats all...

Please help me on this.. do you have any code for doing this.. I f so can
you please share.

Thanks in Advance

Madhan V

"Otto Moehrbach" wrote:

Madhan
Yes, VBA can do this, but why do you not want the other workbooks
opened? Do you mean YOU don't want to open the other workbooks manually
or
do you mean that you don't want VBA to open them either. VBA can open
each
book, get the data, close it, open another book, etc. and you would not
see
any of it happening on the screen. HTH Otto
"vmadan16" wrote in message
...
Hi All,

I need help.. I have an excel file called as Main Det. I need to update
this
file with Datas from more than 10 other excel Workbooks. The
complication
in
this is the Main Workbook has 5 sheets and all other workbook also has
the
same number of sheets and each sheets as 20 rows of data. and what I
want
is
Subworkbook sheet1 data to be updated in Manin Det sheet1 (for example:
data
from workbook1 sheet1 and the the range of the other workbooks changes
(1
column increases every week). Is there any way of doing this using a
VBA
and
without opening the other 10 workbooks.

Please let me know if I am not clear or confusing.

Thanks in Advance

Madhan V