Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update Excel file
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update Excel file
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update Excel file
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update Excel file
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update Excel file
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update Excel file
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update Excel file
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to update Excel file using MFC? | Excel Programming | |||
No... while trying to update to Excel file | Excel Programming | |||
i want to update one excel file the other one update automaticaly | New Users to Excel | |||
Update should be done in different excel file | Excel Worksheet Functions |