VB script to Interact with Application Program Interfaces (API's) - CreateObject

The CreateObject function is used to create an object of the type specified in the argument. The Set statement assigns the object reference to a variable or property

Syntax:

CreateObject(ServerName.TypeName, [RemoteServerName])   ‘2nd argument is optional

Here with providing the list of API you can work with

1.  Excel Sheet
2. Word document
1. New Document
2. Existing Document
3. Convert file format from document to pdf or browser or excel
3. Internet Explorer
4. Run Command
1. Open Temp folder
2. Open Remote Desktop Connection
5. Trigger an event on Remote Machines
6. File system Management
1. Windows Explorer
7. Dictionary
8. Regular Expressions
9. SQL Database connection
10. Outlook Mails
11. Network
12. Unified Functional Testing (UFT) / QTP Tool
13. Application LifeCycle Management (ALM)

Examples

1. Working with Excel

Dim tmpExcelApp, tmpWorkbook, tmpSheet

Set tmpExcelApp = CreateObject("Excel.Application")

tmpExcelApp.Visible = true

Set tmpWorkbook = tmpExcelApp.WorkBooks.Add

Set tmpSheet = tmpExcelApp.Sheets(1)

tmpSheet.Cells(1, 1) = tmpWorkbook.Sheets.Count

tmpSheet.Cells(1, 2) = 34

tmpSheet.Cells(2, 1) = Now()

tmpSheet.SaveAs "C:\VasuChiluveru.xls"

tmpExcelApp.Quit

2. Working with Word Document

1. Open any existing document

Set tmpWord = CreateObject("Word.Application")

tmpWord.Visible = True

tmpWord.Documents.Open("D:\VasuChiluveru.doc")

2. Writing some text in and saving the new document

Dim tmpWord

Set tmpWord = CreateObject("Word.Application")

tmpWord.Documents.Add

tmpWord.Selection.TypeText "Hello Friend!!! This document is Audo generated by QTP. "

tmpWord.Selection.TypeText "To know more about Automation view the Blog https://testautomation-by-vasuchiluveru.blogspot.com/"

tmpWord.ActiveDocument.SaveAs "D:\VasuChiluveru.doc"

tmpWord.Quit

Set tmpWord = Nothing

3. Appending a new text in the existing document

Dim tmpWord

Set tmpWord = CreateObject("Word.Application")

tmpWord.Documents.Open "D:\VasuChiluveru.doc"

tmpWord.Selection.TypeText "This new text is append by opening the existing document"

tmpWord.ActiveDocument.Save

tmpWord.Quit

Set tmpWord = Nothing

4. Covert any existing document to PDF or HTML (browser) or Excel. NOTE: change the tmpFormat according to your desire

Dim fso, tmpFile, tmpFilePath, myFile, tmpFormat

Set fso = CreateObject( "Scripting.FileSystemObject")

Set tmpFile = fso.GetFile("D:\VasuChiluveru.doc")

tmpFilePath = tmpFile.Path

tmpFormat = "XLS"

Dim tmpWord

Set tmpWord = CreateObject("Word.Application")

tmpWord.Documents.Open tmpFilePath

If ucase(tmpFormat) = "PDF" Then

myFile = fso.BuildPath (tmpfile.ParentFolder ,fso.GetBaseName(tmpfile) & ".pdf")

tmpWord.Activedocument.Saveas myfile, 17

elseIf ucase(tmpFormat) = "HTML" Then

myFile = fso.BuildPath (tmpfile.ParentFolder ,fso.GetBaseName(tmpfile) & ".html")

tmpWord.Activedocument.Saveas myfile, 8

elseIf ucase(tmpFormat) = "XLS" Then

myFile = fso.BuildPath (tmpfile.ParentFolder ,fso.GetBaseName(tmpfile) & ".xls")

tmpWord.Activedocument.Saveas myfile, 9

End If

tmpWord.Quit

Set tmpWord = Nothing

Set fso = Nothing

Set tmpFile = Nothing

3. Working with Internet Explorer

Dim tmpIE

Set tmpIE = CreateObject("InternetExplorer.Application")

tmpIE.Visible = true

tmpIE.Navigate "https://testautomation-by-vasuchiluveru.blogspot.com/"

4. Working with Run command

1. Open a temporary folder

Set objShell = CreateObject("Wscript.Shell")

objShell.run("%temp%")

2. Open a Remote Desktop Connection

Set objShell = CreateObject("Wscript.Shell")

objShell.run("mstsc")

3. Open a DOS command Prompt/Window

Set objShell = CreateObject("Wscript.Shell")

objShell.run("mstsc")

5. Create a WshController object to run scripts on a remote machine

strServer = "machine_name"

strScript = "script_name.vbs"

Set objWshController =CreateObject("WshController")

Set objRemoteScript = objWshController.CreateScript(strScript, strServer)

objRemoteScript.Execute

6. Working with File system Management

Set fso = CreateObject("Scripting.FileSystemObject")

if fso.FolderExists("D:\Vasu Chiluveru") then

  Msgbox "Found the folder name"

end if

set files = fso.GetFolder("D:\").Files

Msgbox "Total folder names count "&files.count

7. Working on Dictionary

Set d = CreateObject("Scripting.Dictionary")

d.Add "a", "Apple"   

d.Add "b", "BlueTooth"

d.Add "c", "C++"

        

msgbox "Total Count"&d.Count

         

If d.Exists("c") Then

     msgbox  "Specified key exists. The Item Name is = "&d.Item("c")

Else

     msgbox  "Specified key doesn't exist."

End If

8. Working on Regular Expressions

' The character 'i' is replaced for the first found 'i' by '$' 

txt="This is a beautiful blossam day"

Set objReg=CreateObject("vbscript.regexp")

objReg.Pattern="i"

msgbox objReg.Replace(txt,"$")

9. Working on Oracle/SQL database

Set connDB = createobject(“ADODB.Connection”) 

Set recrdSet = createobject(“ADODB.RecordSet”) 

Dim dbquery        

Dbquery=”Select * from TableName”  

connDB.Open“Provider=SQLQLEDB;Server=.\SQLEXPRESS;UserId=userid;Password=password;Database =DatabaseName”        

recrdSet.Open dbquery,obj     

val1 = recrdSet.fields.item(0)

val2 = recrdSet.fields.item(1)

val3 = recrdSet.fields.item(2)

msgbox “Value 1 = “&val1&“Value 2 = “  & val2 & “Value 3 = “& val3

                    

connDB.close                              

recrdSet.close  

Set connDB =Nothing              
Set recrdSet =Nothing

10. Working with Outlook Mails

Set tmpOutlook = CreateObject("Outlook.Application")

Set tmpNameSpace = tmpOutlook.GetNamespace("MAPI")

Set myMail = tmpOutlook.CreateItem(0)

myMail.To = "Mention recipient mail id"

myMail.Subject = "Welcome .... Blog created by Vasu Chiluveru"

myMail.Body= "To know more on Automation Visit the URL https://testautomation-by-vasuchiluveru.blogspot.com/"

myMail.Send

Set myMail = Nothing

Set tmpOutlook = Nothing

Set tmpNameSpace = Nothing 

11. Working with Network Systems

'Retrieve My Computer name or system name

Set tmpNetwork = CreateObject("Wscript.Network")

tmpComputerName = tmpNetwork.ComputerName

msgbox tmpComputerName

12. Unified Functional Testing (UFT) / QTP Tool

'Open QTP and open and Run the Test script & then close the Tool

Dim tmpObj, tmpTest
Set tmpObj=CreateObject("QuickTest.Application")

'Check if the QTP tool is not already Launched
If Not tmpObj.Launched then    tmpObj.Launch
end iftmpObj.Visible=TruetmpObj.Open "Provide the path of the QTP script"
Set tmpTest=tmpObj.Test
'Run the Test
tmpTest.Run 
'Close the Test
tmpTest.Close 
'Quit the QTP Application
tmpObj.Quit 

13. Application LifeCycle Management (ALM)

'Get the folders from the given folder (TEST PLAN)

Set tdc = CreateObject("TDApiOle80.TDConnection")
tdc.InitConnectionEx "https://qcURL:8080/qcbin"
tdc.Login "userID", "PWD"
tdc.Connect "DOMAIN", "ProjectName"

Set sfolder = tdc.TreeManager.TreeRoot("Subject")
msgbox sfolder.Count

For i = 1 To sfolder.Count
   msgbox sfolder.Child(i).name
Next