First Steps with QTP part 2

Ok, in place of the planned article on QTP automation which I promise I will get to eventually, this post will be about interacting with files through QTP.

Typically we’ll want to handle text files (for error logging and general message transmission) or Excel files. To begin with though, there are some standard declarations required.

Const ForReading = 1, ForWriting = 2, ForAppending = 8
Dim fso, f


1. Text Files

Set fso = CreateObject("Scripting.FileSystemObject")
Set file= fso.OpenTextFile(strFilename, ForWriting, True)
file.Write timestamp & vbcrlf
file.Write strQuery & vbcrlf
file.Write "strDSN is not specified" & vbcrlf
file.close

This code has come from a function library I’ve used at a number of clients. strFilename is a string with the full path to a destination file. This is overwritten if it exists or created if not. (Use forAppending in the event you want to append, or change True to False if you don’t want to replace the file.)

File Exists

I like to use an if File Exists, to check for the presence of a file before writing to it or overwriting it. Given below is an excel-based example.


Set fso = CreateObject("Scripting.FileSystemObject")
rc = fso.FileExists(strFilename)
Set ExcelObj = CreateObject("Excel.Application")
ExcelObj.Visible = False

If rc = true Then
Set objWorkbook = ExcelObj.Workbooks.Open(strFilename)
Set NewSheet = objWorkbook.Worksheets(1)
Else
Set objWorkbook = ExcelObj.Workbooks.Add
Set NewSheet = ExcelObj.Sheets.Item(1)
NewSheet.Name = "Output_Data"
End If

2. Excel Files

With Excel files, typically we’ll want to output values from an Array into the spreadsheet. The array used in this case, Objrec, comes as the result of a database query.


'OUTPUT HEADERS
For I = 0 To objRec.Fields.Count - 1
NewSheet.Cells(1, I + 1).Value = objRec.Fields(I).Name
Next

' Pull all row data into array
arrData = objRec.GetRows
cnt = UBound(arrData, 2)
cnt1 = UBound(arrData, 1)
objRec.MoveFirst

For I = 0 to cnt
For J = 0 to cnt1
NewSheet.Cells(I + 2, J + 1).Value = objRec(J).Value
Next
objRec.MoveNext
Next
'-------------------------------------------------------------------------------------------------------
'ALTERNATIVE OUTPUT TO FILE
'-------------------------------------------------------------------------------------------------------
'Do Until objRec.EOF
' For Each x In objRec.Fields
' Msgbox x.value
' Next
' objRec.MoveNext
'Loop

Interrogating a data-base with QTP

Set objDB = CreateObject("ADODB.Connection")

If strDSN <> "" Then
objDB.Open(strDSN)
Else
strFilename = strFilename & ".txt"
Set fso = CreateObject("Scripting.FileSystemObject")
Set file= fso.OpenTextFile(strFilename, ForWriting, True)
file.Write timestamp & vbcrlf
file.Write strQuery & vbcrlf
file.Write "strDSN is not specified" & vbcrlf
file.close
Exit function
End If

Set objRec = objDB.Execute(strQuery)
objRec.MoveFirst
z = 1 'Z is the active row in Excel.

The entire function library will be uploaded later.

You can leave a response, or trackback from your own site.

Leave a Reply

Powered by WordPress and ThemeMag