Wednesday, July 15, 2009

VB Script Objects

As we know, we frequently create the objects to perform some specific functions using defining the objects in our programming script. Specially when we use qtp, to write a descriptive programming, we need to create this objects.

Here are some lists of objects that are frequently used in VB Script:

Set objEmail = CreateObject( "CDO.Message" )
Set objIE = CreateObject( "InternetExplorer.Application" )
Set objInet = CreateObject( "InetCtls.Inet.1" )
Set objHTTP = CreateObject( "WinHttp.WinHttpRequest.5.1" )
Set objExcel = CreateObject( "Excel.Application" )
Set objExcelSheet = CreateObject( "Excel.Sheet" )
Set objOutlook = CreateObject( "Outlook.Application" )
Set objPpt = CreateObject( "PowerPoint.Application" )
Set objWord = CreateObject( "Word.Application" )
Set objCal = CreateObject( "MSCAL.Calendar" )
Set objQPro = CreateObject( "QuattroPro.PerfectScript" )
Set objWP = CreateObject( "WordPerfect.PerfectScript" )
Set objConn = CreateObject( "ADODB.Connection" )
Set objRecSet = CreateObject( "ADODB.Recordset" )
Set objDic = CreateObject( "Scripting.Dictionary" )
Set objFSO = CreateObject( "Scripting.FileSystemObject" )
Set wshNetwork = CreateObject( "WScript.Network" )
Set wshShell = CreateObject( "WScript.Shell" )
Set objRandom = CreateObject( "System.Random" )
Set objArrList = CreateObject( "System.Collections.ArrayList" )
Set objSortList = CreateObject( "System.Collections.SortedList" )
Set xmlDoc = CreateObject( "Microsoft.XmlDom" )
Set xml2Doc = CreateObject( "Msxml2.DOMDocument.5.0" )
Set objiTunes = CreateObject( "iTunes.Application" )
Set objPlayer = CreateObject( "WMPlayer.OCX" )
Set objWMPlayer = CreateObject( "WMPlayer.OCX.7" )
Set objReal = CreateObject( "rmocx.RealPlayer G2 Control.1" )
Set objFSDialog = CreateObject( "SAFRCFileDlg.FileSave" )
Set objFODialog = CreateObject( "SAFRCFileDlg.FileOpen" )
Set objDialog = CreateObject( "UserAccounts.CommonDialog" )
Set SOAPClient = CreateObject( "MSSOAP.SOAPClient" )
Set objWOL = CreateObject( "UltraWOL.ctlUltraWOL" )
Set objSearcher = CreateObject( "Microsoft.Update.Searcher" )
Set objShell = CreateObject( "Shell.Application" )
Set objDeviceReplay=CreateObject("Mercury.DeviceReplay")

Here are some examples how to use this objects:

Description: Creates and returns a reference to an Automation object.

Syntax: CreateObject(class)

The class argument uses the syntax servername.typename and has these parts:
servername: The name of the application providing the object.
typename: The type or class of the object to create.

Remarks: Automation servers provide at least one type of object. For example, a word-processing application may provide an application object, a document object, and a toolbar object. To create an Automation object, assign the object returned by CreateObject to an object variable:

Dim ExcelSheet
Set ExcelSheet = CreateObject("Excel.Sheet")

This code starts the application creating the object (in this case, a Microsoft Excel spreadsheet). Once an object is created, you refer to it in code using the object variable you defined. In the following example, you access properties and methods of the new object using the object variable, ExcelSheet, and other Excel objects, including the Application object and the Cells collection. For example:

' Make Excel visible through the Application object.
ExcelSheet.Application.Visible = True

' Place some text in the first cell of the sheet.
ExcelSheet.Cells(1,1).Value = "This is column A, row 1"

' Save the sheet.
ExcelSheet.SaveAs "C:\DOCS\TEST.XLS"

' Close Excel with the Quit method on the Application object.

' Release the object variable.
Set ExcelSheet = Nothing

Example 2: ("Excel.Application" )

For example to create an excel application object:

‘Close all the open excel sheet open on your desktop
Systemutil.CloseProcessByName "excel.exe"

'Create a new excel file
Set Excel = createObject("Excel.Application")

‘Open the excel sheet
Set SExcelSheet = Excel.Workbooks.Open("D:\Data\Compa.xls")

‘Show the excel sheet in your desk to

‘Write the value (text) in the excel sheet(in 1st row, 2nd column)

'Close any pop up alart message box due to excel error
Excel.DisplayAlerts = False
'To run a macro in excel
Excel.Run "Compa"
‘Save the same updated file in different location with different name
SExcelSheet.SaveAs "D:\Elements\BaseLine.xls"

‘Close the excel sheet

‘quit the excel application from system

Example 3A: ("Scripting.FileSystemObject")

'Drive path where you want to create the folder
strDrive = “D:/Data”

'Name of the folder to be created
strfoldername= "New Folder"'

‘Combined the path with folder name
strPath= strDrive&strfoldername

' Create FileSystemObject.
Set objFSO = CreateObject("Scripting.FileSystemObject")

On Error Resume Next ' pass this error if folder already exist

' Create a Folder, using strPath
Set objFolder = objFSO.CreateFolder(strPath)

Example 3B:

' Get the name of file extentionmsgbox
GetAnExtension("D:\Documents and Settings\Execution Summary.htm
Function GetAnExtension(DriveSpec)
Dim fso Set fso = CreateObject("Scripting.FileSystemObject")
GetAnExtension = fso.GetExtensionName(Drivespec) 'msgbox GetAnExtension
End Function

Example 4: ("CDO.Message")

Dim objMessage
‘create the message object to send an email
Set objMessage = CreateObject("CDO.Message")

‘Add subject on your message
objMessage.Subject = "QTP Results - Automated Testing"

objMessage.From = "" ' Change this for your own from address

objMessage.To = 'Send to email id
objMessage.CC = 'CC to email id
‘Body text message
objMessage.TextBody ="N.B. - Please Do Not Reply This Message Directly.”

'Include File attachments here
objMessage.AddAttachment “D:\Data\file.text”

'This section provides the configuration information for the remote SMTP server.
objMessage.Configuration.Fields.Item _
("") = 2

'Name or IP of Remote SMTP Server
objMessage.Configuration.Fields.Item _
("") = ""

'Server port (typically 25)
objMessage.Configuration.Fields.Item _
("") = 25

'End remote SMTP server configuration section==
‘Send the email

Example 5: ("Wscript.Shell")

Set WshShell = CreateObject("Wscript.Shell")
Dim Response
' Displays a message box with the yes and no options.
Response = MsgBox("Please Select your choice as 'Yes' or 'No'." & vbcrlf & vbcrlf & "Do you want to Select “Yes” or “No” ?", vbYesNo)
' If statement to check if the yes button was selected.
If Response = vbYes Then
‘message box will appear for 3 second
WshShell.Popup "You Have Been Selected “Yes”. Please wait.", 3, "Your Selection" ‘-WshSheel.Popup “message”, “time to wait”, “message box title”

' The no button was selected.
‘message box will appear for 5 second
WshShell.Popup "You Have Been Selected “No”", 5, "Your Selection"

Example 6A: ("Mercury.DeviceReplay")

Here is the example of 'Mercury.DeviceReplay' Object used in QTP:

abs_x = objWebList.GetROProperty("abs_x")
abs_y = objWebList.GetROProperty("abs_y")
Set objMercuryMouse = CreateObject ("Mercury.DeviceReplay")
mercuryMouse.MouseMove abs_x,abs_y

Example 6B:("Mercury.DeviceReplay")
We can use 'Mercury.DeviceReplay' simply to enter data in the fields. Here is a simple example of this. But before using this object, you need to select the object where the data needs to enter
For i = 1 to 10
Set dr=CreateObject("Mercury.DeviceReplay")
Set dr=Nothing

Example 7("Scripting.Dictionary")

This function will generate user specified random numbers


Function RanNumber(val)
Dim d Set d=nothing
Set d = createobject("Scripting.Dictionary")
For i =1 to val
r=RandomNumber (0,9)
d.add i, r
a = d.items 'Get the items.
For i = 0 To d.Count -1 ' Iterate the array.
s = s&a(i)'Create return string.
End Function

Example 8A: ("ADODB.Connection") /("ADODB.Recordset")

This function will execute an specific query from database using a dedicated database connection string

Function database()

'DATABASE connection
Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adUseClient = 3
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordset = CreateObject("ADODB.Recordset")
objConnection.Open "DRIVER={Microsoft ODBC for Oracle};UID="& v_MHXMLDBSchema &" ;PWD=" & v_MHXMLDBPwd & ";SERVER=" & v_DBInstance &";"
objRecordset.CursorLocation = adUseClient
objRecordset.CursorType = adopenstatic
objRecordset.LockType = adlockoptimistic
objRecordset.Source="select SOP from MHXML.FIRMS_STG where org_id in 681915"
ObjRecordset.Open 'This will execute query
If ObjRecordset.recordcount>0 then
Field1 = ObjRecordset("SOP").Value
'Field2 = ObjRecordset("LAST_NAME").Value
msgbox Field1
'msgbox Field2
End if
End Function

Example 8B: ("Database connection without ADODB.Connection Object")

This function will retrive the database value even if the value is null or empty.

Function GetAttorneyInfo(InField,ALid)

' Creating the database connection
MHXMLconnection_string="DRIVER={Microsoft ODBC for Oracle};UID="& v_MHXMLDBSchema &" ;PWD=" & v_MHXMLDBPwd & ";SERVER=" & v_DBInstance &";"
isMHXMLConnected = db_connect ( MHXMLConnection ,MHXMLconnection_string )
If isMHXMLConnected=0Then ' get the data from the table
v_Exe_SQL2="Select length(NVL(" & InField & ",'Data Not Found')) from lbmgradmin.ilv_vw where ilisting_id = " & ALid
set RecSet_SOPInfo_LEN=db_execute_query( MHXMLConnection , v_Exe_SQL2 )
d_SOPInfo_Length=db_get_field_value( RecSet_SOPInfo_LEN , 0 , 0 )
'msgbox d_SOPInfo_Length
v_Exe_SQL2="select substr(to_char(NVL(" & InField & ",'Data Not Found')),1," & d_SOPInfo_Length & ") from lbmgradmin.ilv_vw where ilisting_id = " & ALid
set RecSet_SOPInfo=db_execute_query( MHXMLConnection , v_Exe_SQL2 )
RowCnt=db_get_rows_count( RecSet_SOPInfo )
If RowCnt=1Then
d_SOPInfo=db_get_field_value( RecSet_SOPInfo , 0 , 0 )
d_SOPInfo=db_get_field_value( RecSet_SOPInfo , 0 , 0 )
End If
End If
'If isMHXMLConnected=0 Then db_disconnect MHXMLConnection

End Function

' Database functions

Function db_connect( byRef curSession ,connection_string)
dim connection
on error Resume next
' Opening connection
set connection = CreateObject("ADODB.Connection")
If Err.Number <> 0 then
db_connect= "Error # " & CStr(Err.Number) & " " & Err.Description
Exit Function
End If
connection.Open connection_string
If Err.Number <> 0 then
db_connect= "Error # " & CStr(Err.Number) & " " & Err.Description
Exit Function
End If
set curSession=connection
End Function

' Db Disconnect - Function to disconnect the database connection

Function db_disconnect( byRef curSession )
set curSession = Nothing
End Function

' DB Execute Query - Function to execute the query

Function db_execute_query ( byRef curSession , SQL)
set rs = curSession.Execute( SQL )
set db_execute_query = rs
End Function

' DB Function to get the number of rows in the record set

Function db_get_rows_count( byRef curRS )
dim rows
rows = 0
Do Until curRS.EOF
rows = rows+1
db_get_rows_count = rows
End Function

' Function to fetch the records from the record set

Function db_get_field_value( curRecordSet , rowIndex , colIndex )
dim curRow
count_fields = curRecordSet.fields.count-1
If ( TypeName(colIndex)<> "String" ) and ( count_fields < db_get_field_value =" -1" db_get_field_value =" curRecordSet.fields(colIndex).Value">


Example 8C: ("ADODB.Connection") /("ADODB.Recordset")
This following code will get the data from excel sheet located on the following path:
Dim Get_Field
set connectToDB = CreateObject("ADODB.Connection")
connectToDB.Provider = "Microsoft.Jet.OLEDB.4.0"
connectToDB.Properties("Extended Properties").Value = "Excel 8.0"
connectToDB.Open "D:\Documents and Settings\pauldx\Desktop\Data.xls"
strQuery="Select Age from [Data$] WHERE Name ='Joli'"
Set rsRecord = CreateObject("ADODB.Recordset")
rsRecord.Open strQuery,connectToDB,1,1
' msgbox rsRecord.RecordCount
If rsRecord.RecordCount>0 Then
for i= 1 to rsRecord.RecordCount
print Get_Field
Get_Field="Field Not Present"
End If

Example 9:("AcroExch.App" / "AcroExch.AVDoc")

‘Below code search for word ‘Software’ from the pdf file

Option Explicit
Dim accapp, acavdocu
Dim pdf_path, bReset, Wrd_count
pdf_path="C:\Program Files\Om\Om 1.1 User Manual.pdf"
‘AcroExch is acrobat application object
Set accapp=CreateObject("AcroExch.App")

‘Need to create one AVDoc object par displayed document
Set acavdocu=CreateObject("AcroExch.AVDoc")

'Opening the PDF
If acavdocu.Open(pdf_path,"") Then
bReset=1 : Wrd_count = 0
'Find Text Finds the specified text, scrolls so that it is visible, and highlights it
Do While acavdocu.FindText("software", 1, 1, bReset)
bReset=0 : Wrd_count=Wrd_count+1
Wait 0, 200
End If

msgbox "The word 'software' was found " & Wrd_count & "times"
Set accap=nothing : Set accapp=nothing

(Note: you can only use the following code if you have acrobat professional installed. If you just have adobe reader standard version installed you will get this error message - "ActiveX component can't create object: 'AcroExch.PDDoc")

Example 10:("DotNetFactory")
These Functions will conversion of Binary to Hexadecimal/ Decimal or vice versa.
Here we can see the use of DotNetFactory utility with create an instance of “System.Convert”

‘Binary to Hexadecimal conversion
Print "&H: " & BinToHex("00001110100111011111101000111011")

Function BinToHex( bits )
If( bits <> "" ) Then
BinToHex = 2 * BinToHex( Left( bits, Len( bits ) - 1 ) ) + CLng( Right( bits, 1 ) )
End If
End Function

‘Decimal to Binary conversion
Print DecToBin(245234235,32)

Public Function DecToBin( decNum, bitsCount )
Dim str
str = DotNetFactory.CreateInstance( "System.Convert" ).ToString( Clng( decNum ) , 2 )
DecToBin = String( bitsCount - Len( str ), "0" ) & str
End Function