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

[upadhyay40]

Hello All,

I want to read 10 excel sheet, and each sheet has some data. And i want to collect all data from all excel sheet and prepared a single excel sheet. Actually i need to append data at last when 1st excel sheet complete its data .
Can anybody help me out, please hoping for cooperation.

Thanks

Mahesh

[upadhyay40]

Hello All,

I want to read 10 excel sheet, and each sheet has some data. And i want to collect all data from all excel sheet and prepared a single excel sheet. Actually i need to append data at last when 1st excel sheet complete its data .
Can anybody help me out, please hoping for cooperation.

Thanks

Mahesh

[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

[upadhyay40]

Hi venkat,

Thanks for your help,

i written a code to read multiple excel sheet and save it properlly, function is

Code:

Function ExcelUtilities( ) Dim oFolder Dim aFile Dim FSO Set oXL = CreateObject("Excel.Application") Set objExcel = CreateObject("Excel.Application") Set FSO = CreateObject("Scripting.FileSystemObject") oXL.DefaultFilePath = "C:\Results" oXL.DisplayAlerts = False If FSO.FolderExists(oXL.DefaultFilePath) then Set oFolder = FSO.GetFolder(oXL.DefaultFilePath) For each aFile in oFolder.Files If Right(LCase(aFile.Name), 4) = ".xls" Then Set objWorkbook1= objExcel.Workbooks.Open(aFile.Name) Set objWorkbook3= objExcel.Workbooks.Open("C:\results.xls") rcount=objWorkbook3.Worksheets("Sheet1").UsedRange.rows.count rcount=rcount+1 st="A"&rcount objWorkbook1.Worksheets("Results").UsedRange.Copy objWorkbook3.Worksheets("Sheet1").Range(st).PasteSpecial Paste =xlValues objWorkbook1.save objWorkbook3.save objWorkbook1.close objWorkbook3.close End If Next Set oFolder = Nothing end if oXL.DisplayAlerts = True oXL.Quit Set oXL = Nothing End Function

it works fine,
but when i run next time is there any process to delete all previous data from results.xls and save it once again from row 1. please take in consider

Thanks
Mahesh

[venkatbatchu]

Hi Mahesh,
You have a solution to delete all the previous records
1.First find out the how many records available in results.xls (Ex: 30)
Note: Here u can find the number of previous records from
rcount=objWorkbook3.Worksheets("Sheet1").UsedRange.rows.count (Use the results object to get the count)
2.Now u want to delete these rows from the excel
Note: Use the below code to delete the previous records

Code:

set aa=Createobject("Excel.Application") set bb=aa.Workbooks.open("D:\venkatbatchu.xls") set cc=aa.worksheets("sheet1")

--------

Code:

rcount= here this value u might be getting from this (rcount=objWorkbook3.Worksheets("Sheet1").UsedRange.rows.count )

--------

Code:

for i=1 to rcount step 1 cc.Rows(1).Delete Next bb.save aa.workbooks.close

with the above code will delete the (Ex:30) records

[upadhyay40]

Hi venkat,

Thanks for the immediate response, looking forward for same cooperation. 1 more request,

I am new to QTP, i my self implement QTP in my organization, i did a lot, but i dint aware about the concept of datadriven testing (I have theoretical knowledge), can you please give me or send me the whole framework demo so i will look at into it and implement on my end, please take in consider this, hoping for your kind cooperation. My mail id upadhyay40@gmail.com
waiting for your response.

Thanks
mahesh

[Saket]

Hi Mahesh,

always maintain one query per thread. as the original query has been answered, please create a new thread for your new query.
Thread is closed now.