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

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

+- Micro Focus QTP (UFT) Forums (https://www.learnqtp.com/forums)
+-- Forum: Micro Focus UFT (earlier known as QTP) (https://www.learnqtp.com/forums/Forum-Micro-Focus-UFT-earlier-known-as-QTP)
+--- Forum: VB Scripting/Descriptive Programming (https://www.learnqtp.com/forums/Forum-VB-Scripting-Descriptive-Programming)
+--- Thread: How to Read Multiple excel files and save data in single excel sheet (/Thread-How-to-Read-Multiple-excel-files-and-save-data-in-single-excel-sheet)

How to Read Multiple excel files and save data in single excel sheet - upadhyay40 - 12-01-2009

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

RE: How to Read Multiple excel files and save data in single excel sheet - upadhyay40 - 12-01-2009

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

RE: How to Read Multiple excel files and save data in single excel sheet - venkatbatchu - 12-02-2009

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

RE: How to Read Multiple excel files and save data in single excel sheet - upadhyay40 - 12-02-2009

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

RE: How to Read Multiple excel files and save data in single excel sheet - venkatbatchu - 12-02-2009

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

RE: How to Read Multiple excel files and save data in single excel sheet - upadhyay40 - 12-03-2009

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

RE: How to Read Multiple excel files and save data in single excel sheet - Saket - 12-03-2009

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.