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.