VBScript for Automation (QTP/UFT) Testing

In VBScript – Part 5, let’s see the following topics:

How to handle Excel files
How to handle Files & Folders (File System Object)

HOW TO HANDLE EXCEL FILES:

Let’s see the syntax and example of some of the important Excel Handling Functions:

TO CREATE EXCEL OBJECT:

Syntax:

set excel = createobject("excel.application")

TO MAKE EXCEL VISIBLE:

Syntax:

excel.visible = true

TO ADD AN EXCEL:

Syntax:

excel.workbooks.add()

TO SAVE AN OPENED EXCEL:

Syntax:

excel.Activeworkbook.saveas "D:\TEST\excel.xls"

TO OPEN EXISTING EXCEL:

Syntax:

set workbook1 = excel.workbooks.open("D:\TEST\excel.xls")
set workbook2 = excel.workbooks.open("D:\TEST\excel1.xls")

TO OPEN A PARTICULAR SHEET IN AN EXCEL:

Syntax:

set sheet1 = workbook1.worksheets("sheet1")
set sheet2 = workbook2.worksheets("sheet1")

TO WRITE DATA IN EXCEL:

Syntax:

sheet.cells(1,1).value = "Name"

TO READ DATA FROM EXCEL:

Syntax:

msgbox sheet.cells(1,1).value

TO COMPARE THE DATA IN TWO EXCELS:

Syntax:

FOR EACH cell IN sheet2.usedrange
IF cell.value <> sheet1.range(cell.address).value THEN
cell.interior.colorindex = 3
END IF
NEXT

TO COPY DATA FROM ONE EXCEL TO ANOTHER EXCEL:

Syntax:

workbook1.worksheets("sheet1").usedrange.copy
workbook2.worksheets("sheet2").Range("A1").PasteSpecial

REMOVE DATA FROM EXCEL:

Syntax:

excel.workbooks.open "D:\TEST\excel.xls"
set worksheet = excel.worksheets("sheet1")
worksheet.Range("A1:A3").clearcontents

TO SAVE WORKBOOK:

Syntax:

workbook1.save
workbook2.save

TO CLOSE WORKBOOK:

Syntax:

workbook1.close
workbook2.close

TO QUIT EXCEL:

Syntax:

excel.quit

TO RELEASE SYSTEM RESOURCES:

Syntax:

set sheet1 = nothing
set sheet2 = nothing
set workbook1 = nothing
set workbook2 = nothing
set excel = nothing

FILE SYSTEM OBJECT:

Let’s see the syntax and example of some of the important FSO Functions:

TO CREATE FILE SYSTEM OBJECT:

Syntax:

Set fso = CreateObject("Scripting.FileSystemObject")

TO CREATE NEW FILE:

Syntax:

Set objfile = fso.CreateTextFile(filename[, overwrite[, Unicode]])

Note: default true – overwrite

Example:

Set FSO = CreateObject("Scripting.FileSystemObject")
Set NewFile = FSO.CreateTextFile("D:\TEST\newfile.txt", true)

TO OPEN A TEXT FILE:

Syntax:

Set objfile = fso.OpenTextFile(filename)

Example:

Set FSO = CreateObject("Scripting.FileSystemObject")
Set NewFile = FSO.OpenTextFile("D:\TEST\newfile.txt")

TO OPEN FILE WITH WRITE PERMISSION:

Set OpObj = FSO.OpenTextFile("D:\TEST\newfile.txt",2,True)

TO OPEN FILE WITH READ PERMISSION:

Set OpObj = FSo.OpenTextFile("D:\TEST\newfile.txt",1,True)

TO COPY A TEXT FILE:

Syntax:

fso.CopyFile (source, destination[, overwrite])

Example:

Set FSO = CreateObject("Scripting.FileSystemObject")
FSO.CopyFile "D:\TEST\newfile.txt","D:\TEST\copyfile.txt"

TO MOVE A FILE:

Syntax:

fso.MoveFile (sourceFilePath, destinationFolder)

Example:

Set FSO = CreateObject("Scripting.FileSystemObject")
FSO.MoveFile "D:\TEST\copyfile.txt", "D:\TEST\NEWFOLDER\"

TO DELETE TEXT FILE:

Syntax:

fso.DeleteFile (filename[, force])

Example:

Set FSO = CreateObject("Scripting.FileSystemObject")
FSO.DeleteFile "D:\TEST\newfile.txt"

TO CREATE A NEW FOLDER:

Syntax:

fso.CreateFolder(foldername)

Example:

Set FSO = CreateObject("Scripting.FileSystemObject")
Set NewFolder = FSO.CreateFolder("D:\TEST\NEWFOLDER")

TO COPY A FOLDER:

Syntax:

fso.CopyFolder (source, destination[, overwrite])

Example:

Set FSO = CreateObject("Scripting.FileSystemObject")
FSO.CopyFolder "D:\TEST\NEWFOLDER", "D:\TEST\NEWFOLDERCOPY"

TO MOVE A FOLDER:

Syntax:

fso.MoveFolder (source, destination)

Example:

Set FSO = CreateObject("Scripting.FileSystemObject")
FSO.MoveFolder "D:\TEST\NEWFOLDERCOPY", "D:\TEST\NEWFOLDER\"

TO DELETE A FOLDER:

Syntax:

fso.DeleteFolder (folderspec[, force])

Example:

Set FSO = CreateObject("Scripting.FileSystemObject")
FSO.DeleteFolder "D:\TEST\NEWFOLDER"

TO CHECK DRIVE EXISTANCE:

Syntax:

fso.DriveExists (drivespec)

Example:

Set FSO = CreateObject("Scripting.FileSystemObject")
If fso.DriveExists ("G:\") = True then
msgbox "G Drive Exists"
Else
msgbox "G Drive Not Exists"
End If

TO CHECK FILE EXISTANCE:

Syntax:

fso.FileExists (filespec)

Example:

Set FSO = CreateObject("Scripting.FileSystemObject")
If fso.FileExists ("D:\TEST\newfile.txt") = False then
fso.CreateTextFile("D:\TEST\newfile1.txt")
Else
fso.CreateTextFile("D:\TEST\newfile.txt")
End If

TO CHECK FOLDER EXISTANCE:

Syntax:

fso.FolderExists (folderspec)

Example:

Set FSO = CreateObject("Scripting.FileSystemObject")
If fso.FolderExists ("D:\TEST1\") = True then
fso.CreateFolder ("D:\TEST\Happy")
Else
fso.CreateFolder ("D:\TEST1")
fso.CreateFolder ("D:\TEST1\Happy")
End If

TO GET DRIVE DETAILS:

Syntax:

objDrv = fso.GetDrive(drivespec)

Example:

Set FSO = CreateObject("Scripting.FileSystemObject")
Set GetDrv = FSO.GetDrive("D:\")
msgbox GetDrv.AvailableSpace
msgbox GetDrv.DriveLetter
msgbox GetDrv.DriveType
msgbox GetDrv.FileSystem
msgbox GetDrv.FreeSpace
msgbox GetDrv.Path
msgbox GetDrv.RootFolder
msgbox GetDrv.SerialNumber
msgbox GetDrv.TotalSize

TO GET FOLDER DETAILS:

Syntax:

objFolder = fso.GetFolder(folderSpec)

Example:

Set FSO = CreateObject("Scripting.FileSystemObject")
Set GetFld = FSO.GetFolder("D:\TEST")
msgbox GetFld.name
Set GetSubFld = GetFld.SubFolders
For each FILE in GetSubFld
msgbox FILE.name
Next

TO GET FILE DETAILS:

Syntax:

objFile = fso.GetFile(fileSpec)

Example:

Set FSO = CreateObject("Scripting.FileSystemObject")
Set GetFile = FSO.GetFile("D:\TEST\newfile.txt")
msgbox GetFile.DateCreated
msgbox GetFile.DateLastAccessed
msgbox GetFile.DateLastModified
msgbox GetFile.ParentFolder
msgbox GetFile.Path

TEXT STREAM OBJECT METHODS:

TO CLOSE OBJECT:

Syntax:

objTso.Close

READ:

Syntax:

strChars = objTso.Read(numCharacters)

Example:

Set FSO = CreateObject("Scripting.FileSystemObject")
Set OpObj = FSo.OpenTextFile("D:\TEST\newfile.txt",1,True)
msgbox OpObj.Read(5)

TO READ COMPLETELY:

Syntax:

strChars = objTso.ReadAll

Example:

Set FSO = CreateObject("Scripting.FileSystemObject")
Set OpObj = FSo.OpenTextFile("D:\TEST\newfile.txt",1,True)
msgbox OpObj.ReadAll

TO READ LINE BY LINE:

Syntax:

strChars = objTso.ReadLine

Example:

Set FSO = CreateObject("Scripting.FileSystemObject")
Set OpObj = FSo.OpenTextFile("D:\TEST\newfile.txt",1,True)
msgbox OpObj.ReadLine

TO WRITE:

Syntax:

objTso.Write(string)

Example:

Set FSO = CreateObject("Scripting.FileSystemObject")
Set OpObj = FSO.OpenTextFile("D:\TEST\newfile.txt",2,True)
OpObj.Write "Software Testing Material"
OpObj.Write "www.softwaretestingmaterial.com"

TO KEEP CURSOR IN A NEW LINE TO WRITE:

Syntax:

objTso.WriteLine([string])

Example:

Set FSO = CreateObject("Scripting.FileSystemObject")
Set OpObj = FSO.OpenTextFile("D:\TEST\newfile.txt",2,True)
OpObj.WriteLine "Software Testing Material"
OpObj.WriteLine "www.softwaretestingmaterial.com"