Update Excel file

[vmadan16]

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

 

[Otto Moehrbach]

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]

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

 

[Patrick Molloy]

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

 

[Erik]

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

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