merge data from multiple excel sheets into a workbook
 

I need to create a utility in VB which picks up all the excel files
from a specified directory. Assume each of these files have 3 sheets
with data (text) in the same format. I need to create a new excel file
with similar format containing the 3 sheets and merge the contents
from all the source excel files into this new excel workbook.

merge data from multiple excel sheets into a workbook
 

.... your request is ambiguous because you haven't specified how you want the
data to be merged together. e.g. if you have three wb's with each one
containing three ws's, how do you want wb(1).Sheet(1) merged with
wb(2).Sheet(1)? Add values? (what if they're text; concatenate?) Place each
of the ws's contents below each other in the target? Something else?

There've been several postings that document how to use the FileSearch
property, which is where you'll need to start to find your xl files. From
there it's just a matter of running a For Each loop to open the wb, deal
with the ws contents, and move on to the next one.

HWH




"Kamlesh" wrote in message
om...
I need to create a utility in VB which picks up all the excel files
from a specified directory. Assume each of these files have 3 sheets
with data (text) in the same format. I need to create a new excel file
with similar format containing the 3 sheets and merge the contents
from all the source excel files into this new excel workbook.

merge data from multiple excel sheets into a workbook
 

Here's an example:

C:\DATA folder is having 3 XL Sheets.
1) Jan03.xls
Sheet1 = 20 Emp Records
Sheet2 = 30 Bank Records

2) Feb03.xls
Sheet1 = 10 Emp Records
Sheet2 = 50 Bank Records

3) Mar03.xls
Sheet1 = 40 Emp Records
Sheet2 = 20 Bank Records


The program should read all these files under C:\DATA folder and
create a new file (example: Consolidate.xls).

Consolidate.xls:
Sheet1 = 70 Emp Records
Sheet2 = 100 Bank Records


The above gives you a very clear picture of my requirement. Please
let me know the fasted method to achieve this in VB.

Thanks in advance,
-K

"Heapy" wrote in message ...
... your request is ambiguous because you haven't specified how you want the
data to be merged together. e.g. if you have three wb's with each one
containing three ws's, how do you want wb(1).Sheet(1) merged with
wb(2).Sheet(1)? Add values? (what if they're text; concatenate?) Place each
of the ws's contents below each other in the target? Something else?

There've been several postings that document how to use the FileSearch
property, which is where you'll need to start to find your xl files. From
there it's just a matter of running a For Each loop to open the wb, deal
with the ws contents, and move on to the next one.

HWH




"Kamlesh" wrote in message
om...
I need to create a utility in VB which picks up all the excel files
from a specified directory. Assume each of these files have 3 sheets
with data (text) in the same format. I need to create a new excel file
with similar format containing the 3 sheets and merge the contents
from all the source excel files into this new excel workbook.