Excel and QTP

Microsoft Excel Scripts

Add Data to a Spreadsheet Cell Demonstration script that adds the words "Test Value" to cell 1,1 in a new spreadsheet.

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.Workbooks.Add
objExcel.Cells(1, 1).Value = "Test value"


Add Formatted Data to a Spreadsheet Demonstration script that adds the words "test value" to a new spreadsheet, then formats the cell containing the value.

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.Workbooks.Add
objExcel.Cells(1, 1).Value = "Test value"
objExcel.Cells(1, 1).Font.Bold = TRUE
objExcel.Cells(1, 1).Font.Size = 24
objExcel.Cells(1, 1).Font.ColorIndex = 3


Create User Accounts Based on Information in a Spreadsheet Demonstration script that creates new Active Directory user accounts based on information stored in an Excel spreadsheet.

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open _
("C:\Scripts\New_users.xls")
intRow = 2
Do Until objExcel.Cells(intRow,1).Value = ""
Set objOU = GetObject("ou=Finance, dc=fabrikam, dc=com")
Set objUser = objOU.Create _
("User", "cn=" & objExcel.Cells(intRow, 1).Value)
objUser.sAMAccountName = objExcel.Cells(intRow, 2).Value
objUser.GivenName = objExcel.Cells(intRow, 3).Value
objUser.SN = objExcel.Cells(intRow, 4).Value
objUser.AccountDisabled = FALSE
objUser.SetInfo
intRow = intRow + 1
Loop
objExcel.Quit


Format a Range of Cells Demonstration script that adds data to four different cells in a spreadsheet, then uses the Range object to format multiple cells at the same time.

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.Workbooks.Add
objExcel.Cells(1, 1).Value = "Name"
objExcel.Cells(1, 1).Font.Bold = TRUE
objExcel.Cells(1, 1).Interior.ColorIndex = 30
objExcel.Cells(1, 1).Font.ColorIndex = 2
objExcel.Cells(2, 1).Value = "Test value 1"
objExcel.Cells(3, 1).Value = "Test value 2"
objExcel.Cells(4, 1).Value = "Tets value 3"
objExcel.Cells(5, 1).Value = "Test value 4"
Set objRange = objExcel.Range("A1","A5")
objRange.Font.Size = 14
Set objRange = objExcel.Range("A2","A5")
objRange.Interior.ColorIndex = 36
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.AutoFit()


List Active Directory Data in a Spreadsheet Demonstration script that retrieves data from Active Directory and then displays that data in an Excel spreadsheet.

Const ADS_SCOPE_SUBTREE = 2
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.Workbooks.Add
objExcel.Cells(1, 1).Value = "Last name"
objExcel.Cells(1, 2).Value = "First name"
objExcel.Cells(1, 3).Value = "Department"
objExcel.Cells(1, 4).Value = "Phone number"
Set objConnection = CreateObject("ADODB.Connection")
Set objCommand = CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
Set objCommand.ActiveConnection = objConnection
objCommand.Properties("Page Size") = 100
objCommand.Properties("Searchscope") = ADS_SCOPE_SUBTREE
objCommand.CommandText = _
"SELECT givenName, SN, department, telephoneNumber FROM " _
& "'LDAP://dc=fabrikam,dc=microsoft,dc=com' WHERE " _
& "objectCategory='user'"
Set objRecordSet = objCommand.Execute
objRecordSet.MoveFirst
x = 2
Do Until objRecordSet.EOF
objExcel.Cells(x, 1).Value = _
objRecordSet.Fields("SN").Value
objExcel.Cells(x, 2).Value = _
objRecordSet.Fields("givenName").Value
objExcel.Cells(x, 3).Value = _
objRecordSet.Fields("department").Value
objExcel.Cells(x, 4).Value = _
objRecordSet.Fields("telephoneNumber").Value
x = x + 1
objRecordSet.MoveNext
Loop
Set objRange = objExcel.Range("A1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()
Set objRange = objExcel.Range("B1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()
Set objRange = objExcel.Range("C1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()
Set objRange = objExcel.Range("D1")
objRange.Activate
Set objRange = objExcel.ActiveCell.EntireColumn
objRange.Autofit()
Set objRange = objExcel.Range("A1").SpecialCells(11)
Set objRange2 = objExcel.Range("C1")
Set objRange3 = objExcel.Range("A1")


List Excel Color Values Demonstration script that displays the various colors -- and their related color index -- available when programmatically controlling Microsoft Excel.

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.Workbooks.Add
For i = 1 to 56
objExcel.Cells(i, 1).Value = i
objExcel.Cells(i, 1).Interior.ColorIndex = i
Next


List Service Data in a Spreadsheet Demonstration script that retrieves information about each service running on a computer, and then displays that data in an Excel spreadsheet.

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
objExcel.Workbooks.Add
x = 1
strComputer = "."
Set objWMIService = GetObject _
("winmgmts:\\" & strComputer & "\root\cimv2")
Set colServices = objWMIService.ExecQuery _
("Select * From Win32_Service")
For Each objService in colServices
objExcel.Cells(x, 1) = objService.Name
objExcel.Cells(x, 2) = objService.State
x = x + 1
Next


Open an Excel Spreadsheet Demonstration script that opens an existing Excel spreadsheet named C:\Scripts\New_users.xls.


Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open("C:\Scripts\New_users.xls")


Read an Excel Spreadsheet Demonstration script that reads the values stored in a spreadsheet named C:\Scripts\New_users.xls.

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open _
("C:\Scripts\New_users.xls")
intRow = 2
Do Until objExcel.Cells(intRow,1).Value = ""
Wscript.Echo "CN: " & objExcel.Cells(intRow, 1).Value
Wscript.Echo "sAMAccountName: " & objExcel.Cells(intRow, 2).Value
Wscript.Echo "GivenName: " & objExcel.Cells(intRow, 3).Value
Wscript.Echo "LastName: " & objExcel.Cells(intRow, 4).Value
intRow = intRow + 1
Loop
objExcel.Quit

Source:- http://www.activexperts.com/activmonitor/windowsmanagement/scripts/msoffice/excel/




QTP Code to read an excel file

Dim arr() ' Declares the array arr
zRead_frm_Excel "c:\data.xls","Sheet1",1
For i=0 to ubound(arr)-1
msgbox arr(i)
next

' This function is to read and close the xl arg1=path of xl, arg2=sheet name, arg3=startn row

Function zRead_frm_Excel(xl,sheet,srt_row)
Set xlApp = GetObject("","Excel.Application")
xlApp.visible = true
Set xlWrkbk = xlApp.Workbooks.Open(xl)
Set xlWrksht = xlWrkbk.Worksheets(sheet) ' Data is the name of the sheetint
StartRow = srt_row ' Row from whcih you need to
startrow_cnt= xlWrksht.UsedRange.Rows.Count
ReDim arr(row_cnt)
ub= ubound(arr)-1
i=0
For intRow = intStartRow to xlWrksht.UsedRange.Rows.Count
strAccountNumber = Trim(xlWrksht.Range("A" & intRow)) ' Column A
strAccuntName = Trim(xlWrksht.Range("B" & intRow)) ' Column B
dtDate = Trim(xlWrksht.Range("C" & intRow)) ' Column C
arr_value=strAccountNumber&strAccuntName&dtDate
arr(i)=arr_value
i=i+1
Next
xlApp.Quit
Set xlApp = Nothing
zRead_frm_Excel=arr
End Function





How To open Password Protected Excel sheets

Function UnprotectXL(filePath,fileName,pwd,writeresPwd)
Set objExcel=CreateObject(“Excel.Application”)
objExcel.Visible=false
testData=filePath&”\”&fileName

Set oWorkbook=objExcel.Workbooks

Set myWkbook=objExcel.Workbooks.open (testData,0,False,5,pwd,writeresPwd)
objExcel.DisplayAlerts=False
oWorkbook(fileName).Activate
For Each w in objExcel.Workbooks
w.SaveAs testData,,”",”"

Next

objExcel.Workbooks.Close
objExcel.Quit
Set oWorkbook=Nothing
Set objExcel=Nothing

End Function

Function ProtectXL(filePath,fileName,pwd,writeresPwd)
On Error Resume Next
Set objExcel=CreateObject(“Excel.Application”)
objExcel.Visible=False
testData=filePath&”\”&fileName
Set oWorkbook=objExcel.Workbooks
Set outputWkbook=objExcel.Workbooks.open (testData,0,False)
oWorkbook(testData).Activate
objExcel.DisplayAlerts=False
outputWkbook.SaveAs testData,,pwd,writeresPwd

outputWkbook.Close
objExcel.Workbooks.Close
objExcel.Quit
Set outputWkbook=Nothing
Set objExcel=Nothing

End Function

‘Call ProtectXL(“C:\Documents and Settings\kmohankumar\Desktop”,”4.xls”,”test123″,”test123″)
‘Call UnprotectXL(“C:\Documents and Settings\kmohankumar\Desktop”,”4.xls”,”test123″,”test123″)


November 27, 2008 Posted by QTP Excel Automation 10 Comments

Search for a particular value in Excel

Set appExcel = CreateObject(“Excel.Application”)
appExcel.visible=true
Set objWorkBook = appExcel.Workbooks.Open (filepath)’opens the sheet

Set objSheet = appExcel.Sheets(“Sheet1″)’ To select particular sheet
With objSheet.UsedRange ‘ select the used range in particular sheet
Set c = .Find (“nn”)’ data to find
For each c in objSheet.UsedRange’ Loop through the used range
If c=”nn” then’ compare with the expected data
c.Interior.ColorIndex = 40′ make the gary color if it finds the data
End If
Set c = .FindNext(c)’ next search

next
End With
objWorkBook.save
objWorkBook.close
set appExcel=nothing


October 21, 2008 Posted by QTP Excel Automation, Uncategorized excel and qtp, Excel Automation, searc for a string in excel 15 Comments

Copy an excel sheet to another excel
Following is the code to copy the conntents of a sheet in one excel to another excel sheet

Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True
Set objWorkbook1= objExcel.Workbooks.Open(“C:\Documents and Settings\mohan.kakarla\Desktop\1.xls”)
Set objWorkbook2= objExcel.Workbooks.Open(“C:\Documents and Settings\mohan.kakarla\Desktop\2.xls”)
objWorkbook1.Worksheets(“Sheet1″).UsedRange.Copy
objWorkbook2.Worksheets(“Sheet1″).Range(“A1″).PasteSpecial Paste =xlValues
objWorkbook1.save
objWorkbook2.save
objWorkbook1.close
objWorkbook2.close
set objExcel=nothing

June 9, 2008 Posted by QTP Excel Automation, Uncategorized excel copy, excel copy and paste, execl automation, qtp excel copy and paste 13 Comments

Compare 2 Excel sheets cell by cell
This code will open two excel sheet and compare each sheet cell by cell, if any changes there in cells , it will highlight the cells in red color in the first sheet.
Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True
Set objWorkbook1= objExcel.Workbooks.Open(“C:Documents andSettingsmohan.kakarlaDesktopDocs1.xls”)
Set objWorkbook2= objExcel.Workbooks.Open(“C:Documents and

Settingsmohan.kakarlaDesktopDocs2.xls”)

Set objWorksheet1= objWorkbook1.Worksheets(1)

Set objWorksheet2= objWorkbook2.Worksheets(1)

For Each cell In objWorksheet1.UsedRange
If cell.Value <> objWorksheet2.Range(cell.Address).Value Then
cell.Interior.ColorIndex = 3′Highlights in red color if any changes in cells
Else
cell.Interior.ColorIndex = 0
End If
Next

set objExcel=nothing

February 27, 2008 Posted by QTP Excel Automation AOM, Automation Testing, Compare 2 Excel sheets, Compare 2 Excel sheets cell by cell, Excel Automation, QTP, QTP Additional Faqs, QTP AOM, VBS, VBScripting 44 Comments

Excel Sorting(Ascending , Descending) By Rows and Columns
Excel Sorting By Row:
Const xlAscending = 1
Const xlNo = 2
Const xlSortRows = 2

Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True

Set objWorkbook = objExcel.Workbooks.Open(“C:Documents and Settingsmohan.kakarlaDesktopDocs1.xls”)
Set objWorksheet = objWorkbook.Worksheets(1)
objWorksheet.Cells(1,1).activate

Set objRange = objExcel.ActiveCell.EntireRow
objRange.Sort objRange, xlAscending, , , , , , xlNo, , , xlSortRows
set objExcel=nothing

Excel Sorting By Colum :
Const xlAscending = 1′represents the sorting type 1 for Ascending 2 for Desc
Const xlYes = 1

Set objExcel = CreateObject(“Excel.Application”)’Create the excel object
objExcel.Visible = True’Make excel visible
Set objWorkbook = _
objExcel.Workbooks.Open(“C:\Documents and Settings\mohan.kakarla\Desktop\Docs1.xls”)’Open the

document

Set objWorksheet = objWorkbook.Worksheets(1)’select the sheet based on the index .. 1,2 ,3 …
Set objRange = objWorksheet.UsedRange’which select the range of the cells has some data other than blank
Set objRange2 = objExcel.Range(“A1″)’ select the column to sort

objRange.Sort objRange2, xlAscending, , , , , , xlYes
set objExcel=nothing

Reference: MSDN

February 26, 2008 Posted by QTP Excel Automation AOM, Automation Testing, Excel Automation, Excel Sorting, Excel Sorting Ascending, Excel Sorting By Colum, Excel Sorting By Row, Excel Sorting Descending, QTP, QTP Additional Faqs, QTP AOM, VBS, VBScripting 7 Comments

DELETE ROWS FROM XL SHEET
DELETE ROWS FROM



XL SHEET

Public Function BIP_xlsDeleteRowRange (sSrcPath, sDestPath, sStartRow, sEndRow) ‘Create Excel object
Set oExcel = CreateObject(“Excel.Application”)
‘Sets the application to raise no app alerts
‘In this case it will allow a file overwrite w/o raising a ‘yes/no’ dialog
oExcel.DisplayAlerts = False

‘Open Book in Excel
Set oBook = oExcel.Workbooks.Open(sSrcPath)
‘Set Activesheet
Set oSheet = oExcel.Activesheet

‘Delete row range
oSheet.Rows(sStartRow +”:”+ sEndRow).Delete

‘Save new book to Excel file
oBook.SaveAs (sDestPath)

‘Close the xls file
oExcel.Workbooks.Close()

End Function


February 14, 2008 Posted by QTP Excel Automation AOM, Automation Testing, Excel Automation, QTP, QTP AOM, VBS, VBScripting, word automation 10 Comments

DELETE COLUMNS FROM XL SHEET
DELETE COLUMNS FROM

XL SHEET

Public Function BIP_xlsDeleteColumnRange (sSrcPath, sDestPath, sStartCol, sEndCol) ‘Create Excel object
Set oExcel = CreateObject(“Excel.Application”)
‘Sets the application to raise no app alerts
‘In this case it will allow a file overwrite w/o raising a ‘yes/no’ dialog
oExcel.DisplayAlerts = False‘Open Book in Excel
Set oBook = oExcel.Workbooks.Open(sSrcPath)
‘Set Activesheet
Set oSheet = oExcel.Activesheet

‘Delete row range
oSheet.Columns(sStartCol + “:” + sEndCol).Delete

‘Save new book to Excel file
oBook.SaveAs (sDestPath)

‘Close the xls file
oExcel.Workbooks.Close()

End Function


February 14, 2008 Posted by QTP Excel Automation AOM, Automation Testing, DELETE COLUMNS FROM XL SHEET, Excel Automation, QTP, QTP AOM, VBS, VBScripting, word automation Leave a Comment

ADODB CONNECTION TO READ DATA FROM EXCEL SHEET
Function GetContentFromDB (strFileName, strSQLStatement)



Dim objAdCon, objAdRs



Set objAdCon = CreateObject(“ADODB.Connection”)



objAdCon.Open “DRIVER={Microsoft Excel Driver (*.xls)};DBQ=”&strFileName & “;Readonly=True”

If Err <> 0 Then

Reporter.ReportEvent micFail,”Create Connection”, “[Connection] Error has occured. Error : ” & Err

Set obj_UDF_getRecordset = Nothing

Exit Function

End If

Set objAdRs = CreateObject(“ADODB.Recordset”)

objAdRs.CursorLocation=3 ‘ set the cursor to use adUseClient – disconnected recordset

objAdRs.Open strSQLStatement, objAdCon, 1, 3



MsgBox objAdRs.fields(4).name

While objAdRs.EOF=false

For i=0 to objAdRs.Fields.count

Msgbox objAdRs.fields(i)

Next

objAdRs.moveNext

Wend



If Err<>0 Then

Reporter.ReportEvent micFail,”Open Recordset”, “Error has occured.Error Code : ” & Err

Set obj_UDF_getRecordset = Nothing

Exit Function

End If



Set objAdRs.ActiveConnection = Nothing



objAdCon.Close

Set objAdCon = Nothing







End Function



Set rsAddin = GetContentsFromDB(“C:\Documents and Settings\mohank\Desktop\Login.xls”, “Select * from [Login$]“)