Excel Automation : Open excel file, Sort Excel file, Delete Rows, Delete Columns, Create new excel file,copy data from one file and past in other file

In below code covered Excel Automation with the following actions:-
1) Open existing excel file
2) Sort Excel file
3) Delete Rows
4) Delete Columns
5) Create new excel file
6) copy data from one excel file and past it in another excel file.


‘ Give new excel file path for creation
NewExcelFile="C:\NewExcelFile.xls"
' Give path for Data having excel file
ExistingExcelFile="C:\ExistingExcelFile.xls"
'Represents the sorting type 1 for Ascending 2 for Desc
Const xlAscending = 1
Const xlDescending=2
Const xlYes = 1
Dim objExcel, xlBook, xlSheet, objWorkbook, objWorksheet, objRange, objRange2, oSheet , objWorkbook2
Const xlExcel7 = 39

'Create the excel object, Activate worksheet and disable alerts
Set objExcel = CreateObject("Excel.Application")
Set xlBook=objExcel.workbooks.add
Set xlSheet=xlBook.activesheet
objExcel.DisplayAlerts = False

‘Save new excel file
xlBook.saveas ExistingExcelFile
objExcel.Visible = True

'Open the created excel file
Set objWorkbook = objExcel.Workbooks.Open(NewExcelFile)

'Select the Work sheet based on the index ..1,2 ,3 …
Set objWorksheet = objWorkbook.Worksheets(1)

'Which select the range of the cells has some data other than blank
Set objRange = objWorksheet.UsedRange

‘Select the column to sort
Set objRange2 = objExcel.Range("B1")

‘Sort the Excel file in ascending order
objRange.Sort objRange2, xlAscending, , , , , , xlYes

‘Sort the Excel file in descending order
'objRange.Sort objRange2, xlDescending, , , , , , xlYes
'Set Activesheet
Set oSheet = objExcel.Activesheet
objExcel.DisplayAlerts = False
'‘Delete rows or row in the excel file
oSheet.Rows("10:30").Delete
wait(1)
‘Delete Column or Columns in the excel file
oSheet.Columns("C:P").Delete
wait(1)
‘Save Workbook
objWorkbook.Save
‘
Set objWorkbook2= objExcel.Workbooks.Open(ExistingExcelFile)
‘Copy the data from used range excel file
objWorkbook.Worksheets(1).UsedRange.Copy
‘Past the Copy data
objWorkbook2.Worksheets(1).Range("A1").PasteSpecial Paste =xlValues
‘Save the files and close open files
objWorkbook.save
objWorkbook2.save
objWorkbook.close
objWorkbook2.close
set objExcel=nothing

Transfer data from text file to Excel file

Copy data from text file and past it into the excel file with the following code.This is just like transfer text from Notepad to excel file.


Const ForReading = 1, ForWriting = 2
Dim fso, MyFile,a, intLine, strExcelFilePath
strExcelFilePath="C:\SPQTP.Xls"
Set fso = CreateObject("Scripting.FileSystemObject")
Set xlObj = CreateObject("Excel.Application")
xlObj.DisplayAlerts = False
Set xlBook=xlObj.workbooks.add
Set xlSheet=xlBook.activesheet
xlBook.saveas strExcelFilePath

Set xlWB = xlObj.Workbooks.Open ( strExcelFilePath)
Set NewSheet = xlWB.Sheets("Sheet1")
xlObj.Visible = True
xlObj.DisplayAlerts = False
Set MyFile = fso.OpenTextFile("C:\SPQTP.txt", ForReading, True)

i = 0
Do While not MyFile.AtEndOfStream
' msgbox MyFile.Readline
NewSheet.Cells(i+1,1) = MyFile.Readline
i = i+1
Loop
xlWB.save
xlWB.close
set xlObj= nothing

Get Number of lines in a Text file

In Notepad we can get Count of lines with the following code. But it works fine for less than 100 kb size of Text files.

Const ForReading = 1, ForWriting = 2
Dim fso, MyFile,a, intLine
' Create Object
Set fso = CreateObject("Scripting.FileSystemObject")

' Open the file for reading
Set MyFile = fso.OpenTextFile("C:\SPQTP.txt", ForReading,True)

while(MyFile.atendofline<>true) 'read the file to the end of file
sLine=MyFile.readline()'line by line reading
n = n + 1 'increment counter
msgbox "Text :- " &sLine
Wend
msgbox "Number of lines in the text file:- "& n

' Close the file
MyFile.Close

How to Read data from Text file with QTP?

We can read text line by line from text file with the below code.

Const ForReading = 1, ForWriting = 2
Dim fso, MyFile

' Create Object
Set fso = CreateObject("Scripting.FileSystemObject")
' Create file and enter data into the created file
Set MyFile = fso.OpenTextFile("C:\SPQTP.txt", ForWriting, True)
MyFile.WriteLine "First line - - Hello world!"
MyFile.WriteLine "Second Line :-- "
MyFile.WriteLine "Third Line :-- "
MyFile.WriteLine "4th Line :-- "
MyFile.WriteLine "5th Line :-- "
MyFile.WriteLine "6th Line :-- "
MyFile.WriteLine "7th Line :-- "
MyFile.WriteLine "8th Line :-- "
MyFile.WriteLine "9th Line :-- "
MyFile.WriteLine "10th Line :-- "

' Close the file
MyFile.Close
' Open the file for reading
Set MyFile = fso.OpenTextFile("C:\SPQTP.txt", ForReading,True)

Do While not MyFile.AtEndOfStream
msgbox "Text :" & MyFile.Readline ' get data line by line
' msgbox "Text :" & MyFile.Read(15) ' get no of charecters from a line
' msgbox "Text :" & MyFile.ReadAll ' Get complete file text
Loop

Create a text file and enter data into the text file

In the below snippet code covered following actions.
1. Create a text file with “FilesytemObject”(Note pad) object.
2. Enter text in to the text file and close it.
3. Open the same and read the data .

Const ForReading = 1, ForWriting = 2
Dim fso, MyFile,a, intLine
' Create Object
Set fso = CreateObject("Scripting.FileSystemObject")
' Create file and enter data into the created file
Set MyFile = fso.OpenTextFile("c:\SuryaRead1.txt", ForWriting, True)
MyFile.WriteLine "Hello world!"
MyFile.WriteLine "Quick Test Pro "
' Close the file
MyFile.Close

' Open the file for reading
Set MyFile = fso.OpenTextFile("c:\SuryaRead.txt", ForReading,True)

For i = 1 to 2
a = MyFile.ReadLine ' Returns text line by line
MsgBox a
Next

Send Email from Outlook with QTP code

We can send E-Mail with creating Object for Outlook. In below example code we can send mail with CC, BCC recipients and Attachment.

Below is a list of all default folder constants in MS Outlook.


'Folder Constants
olFolder=3 'Deleted items
olFolder=4 'Outbox
olFolder=5 'Sent Items
olFolder=6 'Inbox
olFolder=9 'Calendar
olFolder=10 'Contacts
olFolder=11 'Journal
olFolder=12 'Notes
olFolder=13 'Tasks



'Mail sending
Dim objOutlook
Set objOutlook = CreateObject("Outlook.Application")
Set EMail = objOutlook.CreateItem(0)

SendTO="……. @......com"
SendCC="……. @......com"
sendBCC="……. @......co.in"
MailSubject="Outlook automation"
EMailBodyStr = "Out Look automation body with QTP"
EMail.to=SendTO
EMail.CC=SendCC
EMail.BCC=sendBCC
EMail.Subject=MailSubject
EMail.body=emailBodyStr
EMail.Attachments.Add("C:\SPQTP.txt")
email.Send
objOutlook.quit
Set EMail=Nothing
Set objOutlook=Nothing

In some systems we will get following error
“A program is trying to access e-mail addresses you have stored in Outlook. Do you want to allow this?”


We can handle this error with installing one third party tool in our machine. Please see the details in the below URL.

http://www.contextmagic.com/express-clickyes

If you want more details for above error refer the following sites.

http://www.add-in-express.com/docs/outlook-security-manager-addins.php

http://pubs.logicalexpressions.com/pub0009/LPMArticle.asp?ID=604

How to handle run time errors with Recovery scenario?

First create below function in function library. In this function, if we want to add additional features like Report, capture bitmap facilities etc.

Function RuntimeErrorHandle (Object, Method, Arguments, retVal)
Dim objReference, strClassName
Set objReference = Object
strClassName = objReference.GetTOProperty("Class Name")
Reporter.ReportEvent micFail,"Runtime Error"," Runtime Error Displayed and
stoped execution -- "&" Object Name:-- " & strClassName &" - Method - "&
Method& " -- ErrorNo: -- "&retVal
Desktop.CaptureBitmap "C:\ScreenShot.png"
ExitAction "Fail"
End Function

While creating Recovery scenario select
1. “Test Run Error” as Trigger Event
2. “Any error” from Error combo box
3. “Function Call” as Recovery Operation
4. Select Function Library and then select created function and finish with further steps.

How to configure Mouse Operations at runtime?

Configure the mouse operations
1. Using browser events
or
2. Using the mouse at run time with "ReplayType" Property.

Example:-
1. Runs mouse operations using browser events.
Setting.WebPackage(“ReplayType”)=1
2. Runs mouse operations using the mouse.
Setting.WebPackage(“ReplayType”)=2

In some applications While entering data in WebEdit object with Set method, we will get Problem,there this is useful.