Excel handling using QTP is one of the most required utility that any QTP programmer need to be expert at . In the next few sections we shall be handling several excel handling codes that one can re-utilize as per his/her needs:
How to Create a new Excel Object with a default new Workbook ?
How to Close a open Excel Object ?
How to save a workbook according to the workbookIdentifier?
How to set the given 'value' in the cell ?
How to returns the cell's value according to its row column and sheet ?
How to get returned an Excel Sheet according to the sheetIdentifier ?
How to insert a new worksheet into the active workbook ?
How to renames a worksheet's name ?
How to remove a worksheet from a workbook ?
How to create a new workbook in the excel application ?
How to open a previously saved Excel workbook and add it to the Application ?
How to set one of the workbooks in the application as Active workbook ?
How to close an open workbook ?
How to compare between two sheets ?
Dim ObjExcelApp 'As Excel.Application
Dim objExcelSheet 'As Excel.worksheet
Dim objExcelBook 'As Excel.workbook
Dim objFso 'As Scripting.FileSystemObject
' This function will return a new Excel Object with a default new Workbook
Function CreateExcel() 'As Excel.Application
Dim objExcelSheet 'As Excel.worksheet
Set ObjExcelApp = CreateObject("Excel.Application") 'Create a new excel Object
ObjExcelApp.Workbooks.Add
ObjExcelApp.Visible = True
Set CreateExcel = ObjExcelApp
End Function
'This function will close the given Excel Object
'objExcelApp - an Excel application object to be closed
Sub CloseExcel(ObjExcelApp)
Set objExcelSheet = ObjExcelApp.ActiveSheet
Set objExcelBook = ObjExcelApp.ActiveWorkbook
Set objFso = CreateObject("Scripting.FileSystemObject")
On Error Resume Next
objFso.CreateFolder "C:\Viplav"
objFso.DeleteFile "C:\Viplav\ExcelExamples.xls"
objExcelBook.SaveAs "C:\Viplav\ExcelExamples.xls"
ObjExcelApp.Quit
Set ObjExcelApp = Nothing
Set objFso = Nothing
Err = 0
On Error GoTo 0
End Sub
'The SaveWorkbook method will save a workbook according to the workbookIdentifier
'The method will overwrite the previously saved file under the given path
'objExcelApp - a reference to the Excel Application
'workbookIdentifier - The name or number of the requested workbook
'path - the location to which the workbook should be saved
'Return "OK" on success and "Bad Workbook Identifier" on failure
Function SaveWorkbook(ObjExcelApp, workbookIdentifier, path) 'As String
Dim workbook 'As Excel.workbook
On Error Resume Next
Set workbook = ObjExcelApp.Workbooks(workbookIdentifier)
On Error GoTo 0
If Not workbook Is Nothing Then
If path = "" Or path = workbook.FullName Or path = workbook.Name Then
workbook.Save
Else
Set objFso = CreateObject("Scripting.FileSystemObject")
'if the path has no file extension then add the 'xls' extension
If InStr(path, ".") = 0 Then
path = path & ".xls"
End If
On Error Resume Next
objFso.DeleteFile path
Set objFso = Nothing
Err = 0
On Error GoTo 0
workbook.SaveAs path
End If
SaveWorkbook = "OK"
Else
SaveWorkbook = "Bad Workbook Identifier"
End If
End Function
'The SetCellValue method sets the given 'value' in the cell which is identified by
'its row column and parent Excel sheet
'objExcelSheet - the excel sheet that is the parent of the requested cell
'row - the cell's row in the objExcelSheet
'column - the cell's column in the objExcelSheet
'value - the value to be set in the cell
Sub SetCellValue(objExcelSheet, row, column, value)
On Error Resume Next
objExcelSheet.Cells(row, column) = value
On Error GoTo 0
End Sub
'The GetCellValue returns the cell's value according to its row column and sheet
'objExcelSheet - the Excel Sheet in which the cell exists
'row - the cell's row
'column - the cell's column
'return 0 if the cell could not be found
Function GetCellValue(objExcelSheet, row, column)
value = 0
Err = 0
On Error Resume Next
tempValue = objExcelSheet.Cells(row, column)
If Err = 0 Then
value = tempValue
Err = 0
End If
On Error GoTo 0
GetCellValue = value
End Function
'The GetSheet method returns an Excel Sheet according to the sheetIdentifier
'ObjExcelApp - the Excel application which is the parent of the requested sheet
'sheetIdentifier - the name or the number of the requested Excel sheet
'return Nothing on failure
Function GetSheet(ObjExcelApp, sheetIdentifier) 'As Excel.worksheet
On Error Resume Next
Set GetSheet = ObjExcelApp.Worksheets.Item(sheetIdentifier)
On Error GoTo 0
End Function
'The InsertNewWorksheet method inserts an new worksheet into the active workbook or
'the workbook identified by the workbookIdentifier, the new worksheet will get a default
'name if the sheetName parameter is empty, otherwise the sheet will have the sheetName
'as a name.
'Return - the new sheet as an Object
'ObjExcelApp - the excel application object into which the new worksheet should be added
'workbookIdentifier - an optional identifier of the worksheet into which the new worksheet should be added
'sheetName - the optional name of the new worksheet.
Function InsertNewWorksheet(ObjExcelApp, workbookIdentifier, sheetName) 'As Excel.worksheet
Dim workbook 'As Excel.workbook
Dim worksheet 'As Excel.worksheet
'In case that the workbookIdentifier is empty we will work on the active workbook
If workbookIdentifier = "" Then
Set workbook = ObjExcelApp.ActiveWorkbook
Else
On Error Resume Next
Err = 0
Set workbook = ObjExcelApp.Workbooks(workbookIdentifier)
If Err <> 0 Then
Set InsertNewWorksheet = Nothing
Err = 0
Exit Function
End If
On Error GoTo 0
End If
sheetCount = workbook.Sheets.Count
workbook.Sheets.Add , sheetCount
Set worksheet = workbook.Sheets(sheetCount + 1)
'In case that the sheetName is not empty set the new sheet's name to sheetName
If sheetName <> "" Then
worksheet.Name = sheetName
End If
Set InsertNewWorksheet = worksheet
End Function
'The RenameWorksheet method renames a worksheet's name
'ObjExcelApp - the excel application which is the worksheet's parent
'workbookIdentifier - the worksheet's parent workbook identifier
'worksheetIdentifier - the worksheet's identifier
'sheetName - the new name for the worksheet
Function RenameWorksheet(ObjExcelApp, workbookIdentifier, worksheetIdentifier, sheetName) 'As String
Dim workbook 'As Excel.workbook
Dim worksheet 'As Excel.worksheet
On Error Resume Next
Err = 0
Set workbook = ObjExcelApp.Workbooks(workbookIdentifier)
If Err <> 0 Then
RenameWorksheet = "Bad Workbook Identifier"
Err = 0
Exit Function
End If
Set worksheet = workbook.Sheets(worksheetIdentifier)
If Err <> 0 Then
RenameWorksheet = "Bad Worksheet Identifier"
Err = 0
Exit Function
End If
worksheet.Name = sheetName
RenameWorksheet = "OK"
End Function
'The RemoveWorksheet method removes a worksheet from a workbook
'ObjExcelApp - the excel application which is the worksheet's parent
'workbookIdentifier - the worksheet's parent workbook identifier
'worksheetIdentifier - the worksheet's identifier
Function RemoveWorksheet(ObjExcelApp, workbookIdentifier, worksheetIdentifier) 'As String
Dim workbook 'As Excel.workbook
Dim worksheet 'As Excel.worksheet
On Error Resume Next
Err = 0
Set workbook = ObjExcelApp.Workbooks(workbookIdentifier)
If Err <> 0 Then
RemoveWorksheet = "Bad Workbook Identifier"
Exit Function
End If
Set worksheet = workbook.Sheets(worksheetIdentifier)
If Err <> 0 Then
RemoveWorksheet = "Bad Worksheet Identifier"
Exit Function
End If
worksheet.Delete
RemoveWorksheet = "OK"
End Function
'The CreateNewWorkbook method creates a new workbook in the excel application
'ObjExcelApp - the Excel application to which an new Excel workbook will be added
Function CreateNewWorkbook(ObjExcelApp)
Set NewWorkbook = ObjExcelApp.Workbooks.Add()
Set CreateNewWorkbook = NewWorkbook
End Function
'The OpenWorkbook method opens a previously saved Excel workbook and adds it to the Application
'objExcelApp - the Excel Application the workbook will be added to
'path - the path of the workbook that will be opened
'return Nothing on failure
Function OpenWorkbook(ObjExcelApp, path)
On Error Resume Next
Set NewWorkbook = ObjExcelApp.Workbooks.Open(path)
Set OpenWorkbook = NewWorkbook
On Error GoTo 0
End Function
'The ActivateWorkbook method sets one of the workbooks in the application as Active workbook
'ObjExcelApp - the workbook's parent excel Application
'workbookIdentifier - the name or the number of the workbook
Sub ActivateWorkbook(ObjExcelApp, workbookIdentifier)
On Error Resume Next
ObjExcelApp.Workbooks(workbookIdentifier).Activate
On Error GoTo 0
End Sub
'The CloseWorkbook method closes an open workbook
'ObjExcelApp - the parent Excel application of the workbook
'workbookIdentifier - the name or the number of the workbook
Sub CloseWorkbook(ObjExcelApp, workbookIdentifier)
On Error Resume Next
ObjExcelApp.Workbooks(workbookIdentifier).Close
On Error GoTo 0
End Sub
'The CompareSheets method compares between two sheets.
'if there is a difference between the two sheets then the value in the second sheet
'will be changed to red and contain the string:
'"Compare conflict - Value was 'Value2', Expected value is 'value2'"
'sheet1, sheet2 - the excel sheets to be compared
'startColumn - the column to start comparing in the two sheets
'numberOfColumns - the number of columns to be compared
'startRow - the row to start comparing in the two sheets
'numberOfRows - the number of rows to be compared
Function CompareSheets(sheet1, sheet2, startColumn, numberOfColumns, startRow, numberOfRows, trimed) 'As Boolean
Dim returnVal 'As Boolean
returnVal = True
'In case that one of the sheets doesn't exists, don't continue the process
If sheet1 Is Nothing Or sheet2 Is Nothing Then
CompareSheets = False
Exit Function
End If
'loop through the table and fill values into the two worksheets
For r = startRow to (startRow + (numberOfRows - 1))
For c = startColumn to (startColumn + (numberOfColumns - 1))
Value1 = sheet1.Cells(r, c)
Value2 = sheet2.Cells(r, c)
'if 'trimed' equels True then used would like to ignore blank spaces
If trimed Then
Value1 = Trim(Value1)
Value2 = Trim(Value2)
End If
'in case that the values of a cell are not equel in the two worksheets
'create an indicator that the values are not equel and set return value
'to False
If Value1 <> Value2 Then
Dim cell 'As Excel.Range
sheet2.Cells(r, c) = "Compare conflict - Value was '" & Value2 & "', Expected value is '" & Value1 & "'."
Set cell = sheet2.Cells(r, c)
cell.Font.Color = vbRed
returnVal = False
End If
Next
Next
CompareSheets = returnVal
End Function
For gaining more insights in the automation using QTP log on to below url :
Automation Testing Using QTP
Dim objExcelSheet 'As Excel.worksheet
Dim objExcelBook 'As Excel.workbook
Dim objFso 'As Scripting.FileSystemObject
' This function will return a new Excel Object with a default new Workbook
Function CreateExcel() 'As Excel.Application
Dim objExcelSheet 'As Excel.worksheet
Set ObjExcelApp = CreateObject("Excel.Application") 'Create a new excel Object
ObjExcelApp.Workbooks.Add
ObjExcelApp.Visible = True
Set CreateExcel = ObjExcelApp
End Function
'This function will close the given Excel Object
'objExcelApp - an Excel application object to be closed
Sub CloseExcel(ObjExcelApp)
Set objExcelSheet = ObjExcelApp.ActiveSheet
Set objExcelBook = ObjExcelApp.ActiveWorkbook
Set objFso = CreateObject("Scripting.FileSystemObject")
On Error Resume Next
objFso.CreateFolder "C:\Viplav"
objFso.DeleteFile "C:\Viplav\ExcelExamples.xls"
objExcelBook.SaveAs "C:\Viplav\ExcelExamples.xls"
ObjExcelApp.Quit
Set ObjExcelApp = Nothing
Set objFso = Nothing
Err = 0
On Error GoTo 0
End Sub
'The SaveWorkbook method will save a workbook according to the workbookIdentifier
'The method will overwrite the previously saved file under the given path
'objExcelApp - a reference to the Excel Application
'workbookIdentifier - The name or number of the requested workbook
'path - the location to which the workbook should be saved
'Return "OK" on success and "Bad Workbook Identifier" on failure
Function SaveWorkbook(ObjExcelApp, workbookIdentifier, path) 'As String
Dim workbook 'As Excel.workbook
On Error Resume Next
Set workbook = ObjExcelApp.Workbooks(workbookIdentifier)
On Error GoTo 0
If Not workbook Is Nothing Then
If path = "" Or path = workbook.FullName Or path = workbook.Name Then
workbook.Save
Else
Set objFso = CreateObject("Scripting.FileSystemObject")
'if the path has no file extension then add the 'xls' extension
If InStr(path, ".") = 0 Then
path = path & ".xls"
End If
On Error Resume Next
objFso.DeleteFile path
Set objFso = Nothing
Err = 0
On Error GoTo 0
workbook.SaveAs path
End If
SaveWorkbook = "OK"
Else
SaveWorkbook = "Bad Workbook Identifier"
End If
End Function
'The SetCellValue method sets the given 'value' in the cell which is identified by
'its row column and parent Excel sheet
'objExcelSheet - the excel sheet that is the parent of the requested cell
'row - the cell's row in the objExcelSheet
'column - the cell's column in the objExcelSheet
'value - the value to be set in the cell
Sub SetCellValue(objExcelSheet, row, column, value)
On Error Resume Next
objExcelSheet.Cells(row, column) = value
On Error GoTo 0
End Sub
'The GetCellValue returns the cell's value according to its row column and sheet
'objExcelSheet - the Excel Sheet in which the cell exists
'row - the cell's row
'column - the cell's column
'return 0 if the cell could not be found
Function GetCellValue(objExcelSheet, row, column)
value = 0
Err = 0
On Error Resume Next
tempValue = objExcelSheet.Cells(row, column)
If Err = 0 Then
value = tempValue
Err = 0
End If
On Error GoTo 0
GetCellValue = value
End Function
'The GetSheet method returns an Excel Sheet according to the sheetIdentifier
'ObjExcelApp - the Excel application which is the parent of the requested sheet
'sheetIdentifier - the name or the number of the requested Excel sheet
'return Nothing on failure
Function GetSheet(ObjExcelApp, sheetIdentifier) 'As Excel.worksheet
On Error Resume Next
Set GetSheet = ObjExcelApp.Worksheets.Item(sheetIdentifier)
On Error GoTo 0
End Function
'The InsertNewWorksheet method inserts an new worksheet into the active workbook or
'the workbook identified by the workbookIdentifier, the new worksheet will get a default
'name if the sheetName parameter is empty, otherwise the sheet will have the sheetName
'as a name.
'Return - the new sheet as an Object
'ObjExcelApp - the excel application object into which the new worksheet should be added
'workbookIdentifier - an optional identifier of the worksheet into which the new worksheet should be added
'sheetName - the optional name of the new worksheet.
Function InsertNewWorksheet(ObjExcelApp, workbookIdentifier, sheetName) 'As Excel.worksheet
Dim workbook 'As Excel.workbook
Dim worksheet 'As Excel.worksheet
'In case that the workbookIdentifier is empty we will work on the active workbook
If workbookIdentifier = "" Then
Set workbook = ObjExcelApp.ActiveWorkbook
Else
On Error Resume Next
Err = 0
Set workbook = ObjExcelApp.Workbooks(workbookIdentifier)
If Err <> 0 Then
Set InsertNewWorksheet = Nothing
Err = 0
Exit Function
End If
On Error GoTo 0
End If
sheetCount = workbook.Sheets.Count
workbook.Sheets.Add , sheetCount
Set worksheet = workbook.Sheets(sheetCount + 1)
'In case that the sheetName is not empty set the new sheet's name to sheetName
If sheetName <> "" Then
worksheet.Name = sheetName
End If
Set InsertNewWorksheet = worksheet
End Function
'The RenameWorksheet method renames a worksheet's name
'ObjExcelApp - the excel application which is the worksheet's parent
'workbookIdentifier - the worksheet's parent workbook identifier
'worksheetIdentifier - the worksheet's identifier
'sheetName - the new name for the worksheet
Function RenameWorksheet(ObjExcelApp, workbookIdentifier, worksheetIdentifier, sheetName) 'As String
Dim workbook 'As Excel.workbook
Dim worksheet 'As Excel.worksheet
On Error Resume Next
Err = 0
Set workbook = ObjExcelApp.Workbooks(workbookIdentifier)
If Err <> 0 Then
RenameWorksheet = "Bad Workbook Identifier"
Err = 0
Exit Function
End If
Set worksheet = workbook.Sheets(worksheetIdentifier)
If Err <> 0 Then
RenameWorksheet = "Bad Worksheet Identifier"
Err = 0
Exit Function
End If
worksheet.Name = sheetName
RenameWorksheet = "OK"
End Function
'The RemoveWorksheet method removes a worksheet from a workbook
'ObjExcelApp - the excel application which is the worksheet's parent
'workbookIdentifier - the worksheet's parent workbook identifier
'worksheetIdentifier - the worksheet's identifier
Function RemoveWorksheet(ObjExcelApp, workbookIdentifier, worksheetIdentifier) 'As String
Dim workbook 'As Excel.workbook
Dim worksheet 'As Excel.worksheet
On Error Resume Next
Err = 0
Set workbook = ObjExcelApp.Workbooks(workbookIdentifier)
If Err <> 0 Then
RemoveWorksheet = "Bad Workbook Identifier"
Exit Function
End If
Set worksheet = workbook.Sheets(worksheetIdentifier)
If Err <> 0 Then
RemoveWorksheet = "Bad Worksheet Identifier"
Exit Function
End If
worksheet.Delete
RemoveWorksheet = "OK"
End Function
'The CreateNewWorkbook method creates a new workbook in the excel application
'ObjExcelApp - the Excel application to which an new Excel workbook will be added
Function CreateNewWorkbook(ObjExcelApp)
Set NewWorkbook = ObjExcelApp.Workbooks.Add()
Set CreateNewWorkbook = NewWorkbook
End Function
'The OpenWorkbook method opens a previously saved Excel workbook and adds it to the Application
'objExcelApp - the Excel Application the workbook will be added to
'path - the path of the workbook that will be opened
'return Nothing on failure
Function OpenWorkbook(ObjExcelApp, path)
On Error Resume Next
Set NewWorkbook = ObjExcelApp.Workbooks.Open(path)
Set OpenWorkbook = NewWorkbook
On Error GoTo 0
End Function
'The ActivateWorkbook method sets one of the workbooks in the application as Active workbook
'ObjExcelApp - the workbook's parent excel Application
'workbookIdentifier - the name or the number of the workbook
Sub ActivateWorkbook(ObjExcelApp, workbookIdentifier)
On Error Resume Next
ObjExcelApp.Workbooks(workbookIdentifier).Activate
On Error GoTo 0
End Sub
'The CloseWorkbook method closes an open workbook
'ObjExcelApp - the parent Excel application of the workbook
'workbookIdentifier - the name or the number of the workbook
Sub CloseWorkbook(ObjExcelApp, workbookIdentifier)
On Error Resume Next
ObjExcelApp.Workbooks(workbookIdentifier).Close
On Error GoTo 0
End Sub
'The CompareSheets method compares between two sheets.
'if there is a difference between the two sheets then the value in the second sheet
'will be changed to red and contain the string:
'"Compare conflict - Value was 'Value2', Expected value is 'value2'"
'sheet1, sheet2 - the excel sheets to be compared
'startColumn - the column to start comparing in the two sheets
'numberOfColumns - the number of columns to be compared
'startRow - the row to start comparing in the two sheets
'numberOfRows - the number of rows to be compared
Function CompareSheets(sheet1, sheet2, startColumn, numberOfColumns, startRow, numberOfRows, trimed) 'As Boolean
Dim returnVal 'As Boolean
returnVal = True
'In case that one of the sheets doesn't exists, don't continue the process
If sheet1 Is Nothing Or sheet2 Is Nothing Then
CompareSheets = False
Exit Function
End If
'loop through the table and fill values into the two worksheets
For r = startRow to (startRow + (numberOfRows - 1))
For c = startColumn to (startColumn + (numberOfColumns - 1))
Value1 = sheet1.Cells(r, c)
Value2 = sheet2.Cells(r, c)
'if 'trimed' equels True then used would like to ignore blank spaces
If trimed Then
Value1 = Trim(Value1)
Value2 = Trim(Value2)
End If
'in case that the values of a cell are not equel in the two worksheets
'create an indicator that the values are not equel and set return value
'to False
If Value1 <> Value2 Then
Dim cell 'As Excel.Range
sheet2.Cells(r, c) = "Compare conflict - Value was '" & Value2 & "', Expected value is '" & Value1 & "'."
Set cell = sheet2.Cells(r, c)
cell.Font.Color = vbRed
returnVal = False
End If
Next
Next
CompareSheets = returnVal
End Function
For gaining more insights in the automation using QTP log on to below url :
Automation Testing Using QTP
Post a Comment