Example to explain working with excel application in QTP using VBScript

 In this post, we will know how to work with excel application with an example . We will explain how to copy a source file and create an destination file with same name or a dynamic name generated. We will also explain how to copy data from an excel worksheet to another worksheet, delete an excel sheet, and rename an excel worksheet using excel application object.

 The problem we will resolve using this code is as follows. 

In the below code, we will explain how to implement the above using vb script.

destfile = CopyFile("D:\testt\TestSupport_1.xls", "D:\testt\", 1)

copydata "D:\testt\generatedRpt.xls", destfile, "general_report", "temp"

masterfile = Copyfile(destfile, "D:\testt\TestingSupport_Daily_Data.xls",0)

'' Description - This function will copy the file from source location to destination folder. In this example we have used below three parameter as per our need.

''Source file - Exact name and location of the source file. This is an external file generated at a fixed location which was first part of problem and will be handled using QTP

''DestinationFolder - this is either the folder location with or without file name. 

''filename - this shows filename is included in the the destinationfolder. In case of dynamic naming of the file, provide value as 1 else provide value as 0.

function CopyFile(SourceFile, DestinationFolder, filename)

if (filename =1) then

'' generate a dynamic name for the file in case filename argument is provided as 1

    DestinationFile = DestinationFolder + "TestSupport" + cstr(DatePart("m",Now()))+ cstr(DatePart("d",Now()))+ cstr(DatePart("yyyy",Now())) + ".xls"

else

   DestinationFile = Destinationfolder

end If

    Set fso = CreateObject("Scripting.FileSystemObject")

    'Check to see if the file already exists in the destination folder

    If fso.FileExists(DestinationFile) Then

    ''In case of destinationfile already exists, delete the existing file

fso.DeleteFile DestinationFile, True

    End If

'' Copy the file from source location to destination location

    fso.CopyFile SourceFile, DestinationFile, True

''close the instance of filesystem object

    Set fso = Nothing

''return the value of file name as the rsturn value of the function.

copyfile = Destinationfile

End function

'

'' Description - this function will copy data from sheet1 of workbook1 to sheet2 of workbook2.

sub copydata(workbook1, workbook2, sheet1,sheet2)

'' Create an instance of excel object

Set objExcel = CreateObject("Excel.Application") 

'' define if the excel needs to be displayed and alerts to be displayed. here the excel will be visible but no alerts will be displayed in case of alert prompt in the excel application.

objExcel.Visible = True

objExcel.DisplayAlerts = False

'' create an instance of excel workbooks for source as well as destination workbook

Set objsrcFile= objExcel.Workbooks.Open(workbook1)   

Set objdestfile = objExcel.Workbooks.Open(workbook2) 

objsrcfile.Activate

'' create instance of worksheet in the workbook

Set objsrcSheet = objsrcfile.Worksheets(sheet1)

Set objdestSheet = objdestfile.Worksheets(sheet2)

'' gather the data from the source file to destination file

'' In the below example , we are copying the data from 4th row in source file to destination file. we can start from first row also if required.

icolcount = objsrcSheet.usedrange.columns.count

irowcount = objsrcSheet.usedrange.rows.count

for i = 4 to irowcount-1

for j = 1 to icolcount-1

''copies data from the destination file to the source file

objdestSheet.cells(i-3,j)= objsrcsheet.cells(i,j)

next

next

objsrcfile.save

objdestfile.save

''This is again a customised code to delete a sheet from excel file. 

objdestfile.sheets("Previous Day").Delete

'' This step renames the sheet in the destinstion file.

Set objWrksheet = objdestfile.Worksheets("Today")

objWrksheet.Name = "Previous Day"

set objWrkSheet = nothing

''Rename another sheet in the workbook

Set objWrksheet = objdestfile.Worksheets("temp")

objWrkSheet.Name = "Today"

set objWrkSheet = nothing

''Add a temp table at the end of sheets and name it as temp

Set objWrkSheet = objdestfile.Sheets.Add(, objdestfile.Sheets(objdestfile.Sheets.Count))

objWrkSheet.Name = "temp"

''Save the excel file and close the instance of excel aplication

objdestfile.save

objsrcfile.close

objdestfile.close

objexcel.quit

set objexcel = nothing

msgbox "done"

end sub