Usage of Excel COM

Integration b/w QTP and Excel is more important in todays world of Data driven testing and other advanced frameworks being in use.
Here are some basic examples of the usage of Excel COM model.

These can be used within QTP or also in a independent manner by saving them as vbs and calling functions.
More functions will be added on a regular basis.


Function BIP_xlsCreateExcelObject() 'As Excel.Application
Set oExcel = CreateObject("Excel.Application") 'Create a new excel Object
oExcel.Workbooks.Add
oExcel.Visible = True
Set BIP_xlsCreateExcelObject = oExcel
End Function

' ___________________________________________________________________' ___________________________________________________________________
'DESC: 'The OpenWorkbook method opens a previously saved Excel oWorkBook and adds it to the Application
'ARG(s):
'sPath - the path of the oWorkBook that will be opened 'Note - returns Nothing on failure
' ___________________________________________________________________
Function BIP_xlsOpenExcel(oExcel, sPath)
Set oBook = oExcel.Workbooks.Open(sPath)
Set BIP_xlsOpenBook = oBook
End Function

' ___________________________________________________________________' ___________________________________________________________________
'DESC: 'This function deletes a specified range of rows
'ARG(s):
'oExcel - Reference to the Excel object 'sStartRow - Starting row to start deleting from 'sEndRow - last row to delete.
'Note - Deletes everything inbetween.
' ___________________________________________________________________
Public Function BIP_xlsDeleteRowRng(oExcel, sStartRow, sEndRow)
Set oSheet = oExcel.Activesheet 'Delete rows above headers '
oSheet.Rows("1:4").Delete
oSheet.Rows(sStartRow +":"+ sEndRow).Delete
End Function

' ___________________________________________________________________' ___________________________________________________________________
'DESC: 'This function saves Excel with Save As
'ARG(s):
'oExcel - Reference to the Excel object 'sPath - Full path, including file name to save to
'Note - Automatically overwrites files with matching names
' ___________________________________________________________________

Public Function BIP_xlsSaveAs(oExcel, sPath) 'Save excel
oExcel.DisplayAlerts = False
oExcel.ActiveWorkbook.SaveAs (sPath)
oExcel.DisplayAlerts = True
End Function

' ___________________________________________________________________' ___________________________________________________________________
'DESC: 'This function closes Excel and ignofes any messages - does not save
'ARG(s):
'oExcel - Reference to the Excel object
'Note - closes app without saving
' ___________________________________________________________________
Public Function BIP_xlsCloseExcel(oExcel) 'Close Excel
oExcel.DisplayAlerts = False
oExcel.Quit
End Function

' ___________________________________________________________________ 'Retrieve the names of all the worksheets in an excel file

Public Function retrievSheetName(strPath)
Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Open strPath
NoSheets= objExcel.ActiveWorkbook.Worksheets.count
msgbox NoSheets
For iSheet = 1 To NoSheets
objExcel.ActiveWorkbook.Worksheets(iSheet).Activate
SheetName =objExcel.ActiveWorkbook.Worksheets(iSheet).name
msgbox SheetName
Next
End Function


Disclaimer: Some function are mostly written as it is and are obtained while browsing through various sites for information.
Any discrepancy noticed can be suggested and will updated on a regular basis.