VBScript for Automation (QTP/UFT) Testing – Part 5

VBScript for Automation (QTP/UFT) Testing – Part 5

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:
1
set excel = createobject(“excel.application”)

TO MAKE EXCEL VISIBLE:

Syntax:
1
excel.visible = true

TO ADD AN EXCEL:

Syntax:
1
excel.workbooks.add()

TO SAVE AN OPENED EXCEL:

Syntax:
1
excel.Activeworkbook.saveas “D:\TEST\excel.xls”

TO OPEN EXISTING EXCEL:

Syntax:
1
2
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:
1
2
set sheet1 = workbook1.worksheets(“sheet1”)
set sheet2 = workbook2.worksheets(“sheet1”)

TO WRITE DATA IN EXCEL:

Syntax:
1
sheet.cells(1,1).value = “Name”

TO READ DATA FROM EXCEL:

Syntax:
1
msgbox sheet.cells(1,1).value

TO COMPARE THE DATA IN TWO EXCELS:

Syntax:
1
2
3
4
5
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:
1
2
workbook1.worksheets(“sheet1”).usedrange.copy
workbook2.worksheets(“sheet2”).Range(“A1”).PasteSpecial

REMOVE DATA FROM EXCEL:

Syntax:
1
2
3
excel.workbooks.open “D:\TEST\excel.xls”
set worksheet = excel.worksheets(“sheet1”)
worksheet.Range(“A1:A3”).clearcontents

TO SAVE WORKBOOK:

Syntax:
1
2
workbook1.save
workbook2.save

TO CLOSE WORKBOOK:

Syntax:
1
2
workbook1.close
workbook2.close

TO QUIT EXCEL:

Syntax:
1
excel.quit

TO RELEASE SYSTEM RESOURCES:

Syntax:
1
2
3
4
5
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:
1
Set fso = CreateObject(“Scripting.FileSystemObject”)

TO CREATE NEW FILE:

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

Note: default true – overwrite

Example:
1
2
Set FSO = CreateObject(“Scripting.FileSystemObject”)
Set NewFile = FSO.CreateTextFile(“D:\TEST\newfile.txt”, true)

TO OPEN A TEXT FILE:

Syntax:
1
Set objfile = fso.OpenTextFile(filename)

Example:

1
2
Set FSO = CreateObject(“Scripting.FileSystemObject”)
Set NewFile = FSO.OpenTextFile(“D:\TEST\newfile.txt”)

TO OPEN FILE WITH WRITE PERMISSION:

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

TO OPEN FILE WITH READ PERMISSION:

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

TO COPY A TEXT FILE:

Syntax:
1
fso.CopyFile (source, destination[, overwrite])
Example:
1
2
Set FSO = CreateObject(“Scripting.FileSystemObject”)
FSO.CopyFile “D:\TEST\newfile.txt”,“D:\TEST\copyfile.txt”

TO MOVE A FILE:

Syntax:
1
fso.MoveFile (sourceFilePath, destinationFolder)
Example:
1
2
Set FSO = CreateObject(“Scripting.FileSystemObject”)
FSO.MoveFile “D:\TEST\copyfile.txt”, D:\TEST\NEWFOLDER\

TO DELETE TEXT FILE:

Syntax:
1
fso.DeleteFile (filename[, force])

Example:

1
2
Set FSO = CreateObject(“Scripting.FileSystemObject”)
FSO.DeleteFile “D:\TEST\newfile.txt” 

TO CREATE A NEW FOLDER:

Syntax:
1
fso.CreateFolder(foldername)
Example:
1
2
Set FSO = CreateObject(“Scripting.FileSystemObject”)
Set NewFolder = FSO.CreateFolder(“D:\TEST\NEWFOLDER”)

TO COPY A FOLDER:

Syntax:
1
fso.CopyFolder (source, destination[, overwrite])
Example:
1
2
Set FSO = CreateObject(“Scripting.FileSystemObject”)
FSO.CopyFolder “D:\TEST\NEWFOLDER”, “D:\TEST\NEWFOLDERCOPY”

TO MOVE A FOLDER:

Syntax:
1
fso.MoveFolder (source, destination)
Example:
1
2
Set FSO = CreateObject(“Scripting.FileSystemObject”)
FSO.MoveFolder “D:\TEST\NEWFOLDERCOPY”, D:\TEST\NEWFOLDER\

TO DELETE A FOLDER:

Syntax:
1
fso.DeleteFolder (folderspec[, force])
Example:
1
2
Set FSO = CreateObject(“Scripting.FileSystemObject”)
FSO.DeleteFolder “D:\TEST\NEWFOLDER”

TO CHECK DRIVE EXISTANCE:

Syntax:
1
fso.DriveExists (drivespec)
Example:
1
2
3
4
5
6
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:
1
fso.FileExists (filespec)
Example:
1
2
3
4
5
6
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:
1
fso.FolderExists (folderspec)
Example:
1
2
3
4
5
6
7
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:
1
objDrv = fso.GetDrive(drivespec)
Example:
1
2
3
4
5
6
7
8
9
10
11
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:
1
objFolder = fso.GetFolder(folderSpec)
Example:
1
2
3
4
5
6
7
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:
1
objFile = fso.GetFile(fileSpec)
Example:
1
2
3
4
5
6
7
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:
1
objTso.Close

READ:

Syntax:
1
strChars = objTso.Read(numCharacters)
Example:
1
2
3
Set FSO = CreateObject(“Scripting.FileSystemObject”)
Set OpObj = FSo.OpenTextFile(“D:\TEST\newfile.txt”,1,True)
msgbox OpObj.Read(5)

TO READ COMPLETELY:

Syntax:
1
strChars = objTso.ReadAll
Example:
1
2
3
Set FSO = CreateObject(“Scripting.FileSystemObject”)
Set OpObj = FSo.OpenTextFile(“D:\TEST\newfile.txt”,1,True)
msgbox OpObj.ReadAll

TO READ LINE BY LINE:

Syntax:
1
strChars = objTso.ReadLine
Example:
1
2
3
Set FSO = CreateObject(“Scripting.FileSystemObject”)
Set OpObj = FSo.OpenTextFile(“D:\TEST\newfile.txt”,1,True)
msgbox OpObj.ReadLine

TO WRITE:

Syntax:
1
objTso.Write(string)
Example:
1
2
3
4
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:
1
objTso.WriteLine([string])
Example:
1
2
3
4
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”

WRITE BLANK LINES:

Syntax:
1
objTso.WriteBlankLines(numLines)
Example:
1
OpObj.WriteBlankLines(3)

At End Of Line:

Syntax:
1
objTso.AtEndOfLine

At End Of Stream:

Syntax:
1
objTso.AtEndOfStream

TO DEALLOCATE FILE SYSTEM OBJECT:

Syntax:
1
Set FSO = nothing

I would like to conclude VBScript – Part 5 here.

VBScript Series:

VBScript for Automation (QTP/UFT) Testing – Part 1

VBScript for Automation (QTP/UFT) Testing – Part 2

VBScript for Automation (QTP/UFT) Testing – Part 3

VBScript for Automation (QTP/UFT) Testing – Part 4

VBScript for Automation (QTP/UFT) Testing – Part 5

Back To Top