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
kotesh says
February 8, 2018 at 1:06 pmexcellent its spoon feeding … great