Creating, Renaming and Data Entry in Excel

The below code is for creating your own excel application, custom naming the sheets and entering data in an excel. This is very useful if you have to get some data from the application and your datatable already has some other application input data. Basically a case where you need to have more than one excel sheets.

Set Excel = CreateObject("Excel.Application")
Set Book = Excel.Workbooks.Add
Set Sheet = Book.Worksheets(1)
Sheet.Name = "Results"
Sheet.Rows(1).Columns(1).value = "This is the first Sheet"

Book.SaveAs "D:\Results.xls"
Book.Close
Excel.Quit

To Rename the second sheet you just need to modify the Worksheet id in the above code. It would look like

Set Excel = CreateObject("Excel.Application")
Set Book = Excel.Workbooks.Open ("D:\Results.xls")
Set Sheet1 = Book.Worksheets(2)
Sheet1.Name = "Output_Data"

You need not close the excel to rename the second sheet. This code can be combined with the above code also. As per your needs. But you should make sure that the name does not exceed the default length in excel

Sending mails using Outlook and SMTP

Here we will see how we can send the mail after the test run using outlook and also using SMTP. First let us see how to send the mail using Outlook. This method though useful has the following drawbacks:
1. The system or machine where you are running the scripts should have outlook configured in it
2. The email will be sent in the name of the person whose email has been configured in the outlook.
3. Finally, outlook throws up a security message when you try to send an automated mail and to take care of this you will have to download a third party software called expressclick.

Well, if you still prefer to go by this method, then here is the code

systemutil.Run "C:\Program Files\Express ClickYes\ClickYes.exe"
wait 5
set oMailobj=CreateObject("Outlook.Application")

set oSendmail=oMailobj.CreateItem(0)
oSendmail.To=""
'oSendmail.Cc=""
oSendmail.Subject="Test"
oSendmail.Body="Hi" & vbcrLf & vbCrlf & "PFA the Smoke Test Automation Results" & vbCrlf & vbCrlf & "Thanks" & vbcrlf & "Automation Team"
sAttachment = "D:\Results.zip"
If (sAttachment <> "") Then
oSendmail.Attachments.Add(sAttachment)

Else
Reporter.ReportEvent micInfo,"Sending mail:","Unable to send attachment,please verify your attachment file"
End If
oSendmail.Send
wait 3
set oSendmail=Nothing
set oMailobj=Nothing
SystemUtil.CloseProcessByName "ClickYes.exe"

All these problems can be overcome by using SMTP mail. The only thing that you need to know is the SMTP server name and the machine should be in the network. Once you use this code you will never go back to using Outlook.

Set oMessage = CreateObject("CDO.Message")

oMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2

'SMTP Server
oMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserver") ="servername or ip"

oMessage.Configuration.Fields.Item _
("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25

oMessage.Configuration.Fields.Update
oMessage.Subject = "Test"
oMessage.Sender = "" ' any non-existing mail id will also work.
oMessage.To =""
'oMessage.CC = ""
'oMessage.BCC = ""

oMessage.AddAttachment "D:\Results.zip"
oMessage.TextBody = "Hi" & vbcrLf & vbCrlf & "PFA the Smoke Test Automation Results & vbCrlf & vbCrlf & "Thanks" & vbcrlf & "Automation Team" & vbCrlf & vbCrlf & vbCrlf &"*********************************************************************************************************" & vbcrlf & "This is an auto-generated email. Please do not reply to this email." & vbcrlf &"*********************************************************************************************************"
oMessage.Send

Set oMessage = Nothing

How to close an application opened through QTP

For opening an application through QTP we use the command SystemUtil.Run. We have a similar command for closing the opened function we as well. Both opening and closing an application called "application" is illustrated below:

Opening an application through QTP:
SystemUtil.Run "C:\Program Files\Application.exe", " ", "C:\Program Files", " "

Closing the same applcation through QTP:
SystemUtil.CloseProcessByName "Application.exe"

Other similar commands available are mentioned below and their names are self explainatory.
SystemUtil.CloseDescendentProcesses
SystemUtil.CloseProcessByHwnd
SystemUtil.CloseProcessById
SystemUtil.CloseProcessByWndTitle

How to search for data in a tab separated log file

Set Fso = CreateObject("Scripting.FileSystemObject")

Const ForReading = 1, ForWriting = 2
Set File1 = Fso.OpenTextFile ("abc.txt", ForReading, True)


Do While File1.AtEndOfStream <> True

text = File1.ReadLine()
newtext = replace (text, " ", " ")' Copy the tab from the Notepad and paste here.For details check the syntax for Replace function in QTP help

data = split (newtext, " ")
For i = Lbound(data) to UBound(data)
If data(i) = "TimeStamp" Then
msgbox "true"
End If
Next
Loop

Getting the data from a WinList View Object

The below code explains how we can extract the data from a WinListView object and store that data in the run time DataTable and also export it to the required location in the end.

Col_count = Window(" ").WinlistView(" ").ColumnCount
Row_count = Window(" ").WinlistView(" ").GetROProperty("items count")

For i = 0 to Col_count
Col_Header = Window(" ").WinlistView(" ").GetColumnHeader(i)
DataTable.GetSheet(1).AddParameter(Col_Header, "") ' This is to add the column header to the datatable
Next


For i = 0 to Row_count
DataTable.SetCurrentRow(i+1)

For j = 0 to Col_count
Header = Window(" ").WinlistView(" ").GetColumnHeader(j)
Row_Item = Window(" ").WinlistView(" ").GetItem(i)
Var = Window(" ").WinlistView(" ").GetSubItem (Row_Item, Header)
DataTable(Header, GlobalDt)= Var
Next
Next

DataTable.Export "C:\QTP Automation\WinlistView_Data.xls"