How to Read Multiple excel files and save data in single excel sheet

[venkatbatchu]

Hi Mahesh,
Please find the below code,
Here i wrote the code for appending two excel sheets data in to third excel sheets.

Code:

msgbox "Going to Start"
Set objExcel = CreateObject("Excel.Application")
'objExcel.Visible = True
Set objWorkbook1= objExcel.Workbooks.Open("C:\Documents and Settings\Venkat.Batchu\Desktop\d1.xls")
Set objWorkbook2= objExcel.Workbooks.Open("C:\Documents and Settings\Venkat.Batchu\Desktop\d2.xls")
Set objWorkbook3= objExcel.Workbooks.Open("C:\Documents and Settings\Venkat.Batchu\Desktop\d12.xls")
st="A1"
objWorkbook1.Worksheets("Sheet1").UsedRange.Copy
objWorkbook3.Worksheets("Sheet1").Range(st).PasteSpecial Paste =xlValues
objWorkbook1.save
objWorkbook3.save
objWorkbook1.close
'objWorkbook3.close
rcount=objWorkbook3.Worksheets("Sheet1").UsedRange.rows.count
rcount=rcount+1
st="A"&rcount
objWorkbook2.Worksheets("Sheet1").UsedRange.Copy
objWorkbook3.Worksheets("Sheet1").Range(st).PasteSpecial Paste =xlValues
objWorkbook2.save
objWorkbook3.save
objWorkbook2.close
objWorkbook3.close
set objExcel=nothing
msgbox "Done"

Steps perfomed in the above code:
1.First i have copied the file d1 and paste in to d12
2.Find the number of rows in d12 (Ex;10 rows)
3.Copied the file d2 and appended to d12 (appended to previous data i.e d1 here data is appended from 11th row)
4.Finally we have d12 file with d1 and d2

If you want to perform with number of sheets then use for loop or use function using above code

Regards,
Venkat.Batchu