Scripting Techniques

There are 5 types of scripting techniques.
1. Linear
2. Structured
3. Shared
4. Data Driven
5. Keyword Driven

Build in Functions

IsNumeric Function
IsDate Function
Year Function
Date Function
Time Function

IsNumeric Function: Evaluates whether an expression can be converted to Date or Not.
Example:
Dim MyVar, MyCheck
MyVar = 53 ' Assigns a Numeric value.
MyCheck = IsNumeric(MyVar) ' Returns True.
MsgBox MyCheck
MyVar = "459.95" ' Assigns a String which consists of Number
MyCheck = IsNumeric(MyVar) ' Returns True.
MsgBox MyCheck
MyVar = "45 Help" ' Assigns a String.
MyCheck = IsNumeric(MyVar) ' Returns False.
MsgBox MyCheck

IsDate Function: Evaluates whether an expression is a number or not.
Example:
Dim MyDate, YourDate, NoDate
MyDate = "October 19, 1962": YourDate = #10/19/62#: NoDate = "Hello"
Msgbox "Value of MyDate is "&IsDate(MyDate)
Msgbox "Value of Your Date is "&IsDate(YourDate)
Msgbox "Value of NoDate variable is "&IsDate(NoDate)

Year Function: Returns the whole number representing an Year
Dim MyDate, MyYear
MyDate = #October 19, 1962# ' Assign a date.
MyYear = Year(MyDate) ' MyYear contains 1962.

Date Function: Returns the current system date.
MyDate = Date
Msgbox "Today's Date is "&MyDate

Time Function: Returns the current system time.
MyTime = Time
Msgbox "Current Time is "&MyTime

Input Box

Displays a prompt in a dialog box, waits for the user to input text or click a button, and returns the contents of the text box.

Example:
Input = InputBox("Input Something","Title of the Input Box","Default Text in the Input Box")
Msgbox ("You Entered " & Input)

VBScript - XML Object

Create an XML file
Inner text and Inner xml of XML file
Details of a single node

'Create an XML file
Set oXMLDoc = CreateObject("Microsoft.XMLDOM")
Set oRoot = oXMLDoc.createElement("Employees")
oXMLDoc.appendChild oRoot

Set oRecord = oXMLDoc.createElement("Employee")
oRoot.appendChild oRecord

Set oSSN = oXMLDoc.createElement("SSN")
oSSN.Text = "123456789"
oRecord.appendChild oSSN

Set oName = oXMLDoc.createElement("Name")
oName.Text = "Thanvi"
oRecord.appendChild oName

Set oDOB = oXMLDoc.createElement("DOB")
oDOB.Text = "2009-19-06"
oRecord.appendChild oDOB

Set oSalary = oXMLDoc.createElement("Salary")
oSalary.Text = 1000000
oRecord.appendChild oSalary

Set oIntro = oXMLDoc.createProcessingInstruction("xml","version='1.0'")
oXMLDoc.insertBefore oIntro,oXMLDoc.childNodes(0)
oXMLDoc.Save "C:\Employees.xml"


'Get the Inner text and Inner xml of the XML file
Set oXML = CreateObject("Microsoft.XMLDOM")
oXML.async = "false"
oXML.load("C:\Employees.xml")
msgbox "XML of the file "&oXML.xml
msgbox "Inner text of the xml file "&oXML.text

'Get the details of a single node
Set oXML = CreateObject("Microsoft.XMLDOM")
oXML.async = "false"
oXML.load("C:\Employees.xml")
Set MyNode = oXML.SelectSingleNode("/Employees/Employee/Name")
Msgbox "XML of the single Node "&MyNode.xml
Msgbox "Inner Text of the single Node "&MyNode.text
Msgbox "Type of the Node "&MyNode.nodeType

'Set the Attribute for the XML
Set oXMLDoc = CreateObject("Microsoft.XMLDOM")
Set oRoot = oXMLDoc.createElement("suite")
oRoot.setAttribute "name","suite"
oRoot.setAttribute "parallel","classes"
oRoot.setAttribute "thread-count","3"
oXMLDoc.appendChild oRoot
Set oRecord = oXMLDoc.createElement("test")
oRecord.setAttribute "name", "test"
oRoot.appendChild oRecord
Set oClasses = oXMLDoc.createElement("classes")
oRecord.appendChild oClasses
Set oName = oXMLDoc.createElement("class")
oName.Text = "Authorization"
oClasses.appendChild oName
Set oIntro = oXMLDoc.createProcessingInstruction("xml","version='1.0'")
oXMLDoc.insertBefore oIntro,oXMLDoc.childNodes(0)
oXMLDoc.Save "G:\TestNG.xml"

Sample Scripts

'Using Integer Array
A(0) = 10
A(1) = 20
A(2) = 30
For i = 0 to 2
msgbox A(i)
Next

'Using String Array
StringArray(0) = "ArrayVariable1"
StringArray(1) = "ArrayVariable2"
StringArray(2) = "ArrayVariable3"
For i = 0 to 2
msgbox StringArray(i)
Next

'Split a string
Dim StringName, SplitString
StringName = "My name is xxx. I studied 10th class. I completed intermediate"
SplitString = split(StringName, " ")
For i = 0 to ubound(SplitString(i))
msgbox SplitString(i)
Next
'or
For i = LBound(SplitString) to UBound(SplitString)
msgbox SplitString(i)
Next

' Gets the Current Date, Current Time
Dim TodaysDate, CurTime, CurDateTime
TodaysDate = Date
CurTime = Time
CurDateTime = Now
msgbox "Today's Date "&TodaysDate
msgbox "Current Time "&CurTime
msgbox "Current Date and Time "&Now

File System Object Vs Files

Type of the File
File Information
Copy a file
Move a file
Delete a file
Create a Text File
Appending Text to File
Reads Specified Number of characters from a Text File
Reads all the characters from a Text File
Reads the Text File Line by Line
Writes Text to a File
Writes Blank Line to a File
Column Number of current character position
Line Number of current position
'Syntax: 'OpenTextFile
'OpenTextFile(filename[, iomode[, create[, format]]])
'iomode:
'ForReading - 1 - Opens a file for Read Only
'ForWriting - 2 - Opens a file for Reading and Writing
'ForAppending - 8 - Opens a file for Appending

Delete FileObject.Delete(force)
'force is boolean value
True - Files with Read-Only attribute is deleted
Move FileObject.Move(Destination)

File Type '0 - Normal File '1 - Read Only File
'2 - Hidden File
'4 - System File
'8 - Volume File - Disk Drive Volume Label
'16 - Folder or Directory
'32 - Archieve - File has changed
'1024 - Alias
'2048 - Compressed File

Dim fso, f, s
Dim PathOfTheFile PathOfTheFile = "C:\NewDebug.xml"
Set fso = CreateObject("Scripting.FileSystemObject")
Set FileInfo = fso.GetFile(PathOfTheFile)
AttrVal = FileInfo.Attributes
If AttrVal = 1 then
Msgbox "Normal File"
Else
Msgbox "Not a Normal File"
End If

File Information
Dim fso, f, s
Dim PathOfTheFile
PathOfTheFile = "C:\Documents and Settings\...\Desktop\NewDebug.txt"
Set fso = CreateObject("Scripting.FileSystemObject")
Set FileInfo = fso.GetFile(PathOfTheFile)
s = "Date on which the file is Created: "&FileInfo.DateCreated &vbcrlf
s = s & "Date on which the file is Last Accessed: "&FileInfo.DateLastAccessed&vbcrlf
s = s & "Date on which the file is Last Modified: "&FileInfo.DateLastModified & vbcrlf
s = s & "Drive on which the file is Saved: "&FileInfo.Drive & vbcrlf
s = s & "Name of the file: "&FileInfo.Name & vbcrlf
s = s & "Name of the folder on which the file is saved: "&FileInfo.ParentFolder&vbcrlf
s = s & "Path of the file: "&FileInfo.Path&vbCrlf
s = s & "Short Name of the file: "&FileInfo.ShortName&vbcrlf
s = s & "Short Path of the file: "&FileInfo.ShortPath & vbcrlf
s = s & "Total Size of the file: "&FileInfo.Size & vbcrlf
s = s & "Type of the file: "&FileInfo.Type
msgbox s

'Copy a file from One location to Another location
Set objFSO = CreateObject("Scripting.FileSystemObject")
SourceFilePath = "D:\debugnew.txt"
DestinationFilePath = "D:\debugnew1.txt"
Set objFileCopy = objFSO.GetFile(SourceFilePath)
If objFSO.FileExists(DestinationFilePath)Then
objFileCopy.Copy(DestinationFilePath)
End If

'Move a file from One location to Another location
Set objFSO = CreateObject("Scripting.FileSystemObject")
SourceFilePath = "d:\debugnew.txt"
DestinationFolder = "C:\"
Set SourceFile = objFSO.GetFile(SourceFilePath)
SourceFile.Move DestinationFolder

'Delete a file
Set objFSO = CreateObject("Scripting.FileSystemObject")
FilePath = "D:\debugnew.txt"
Set objFile = objFSO.GetFile(FilePath)
If objFSO.FileExists(FilePath)Then
objFile.Delete
End If

'Create a text file
Dim fso, tf
Set fso = CreateObject("Scripting.FileSystemObject")
Set Textfile = fso.CreateTextFile("C:\FileName.txt",True) Textfile.WriteLine("First Sentence")
Textfile.Close
Set Textfile = Nothing
Set fso = Nothing

'Appending Text to File
Dim ForAppending, fso, TextFile
ForAppending = 8
Set fso = CreateObject("Scripting.FileSystemObject")
Set TextFile = fso.OpenTextFile("C:\FileName.txt",ForAppending)
TextFile.WriteLine("Fail")
TextFile.Close
Set TextFile = Nothing
Set fso = Nothing

'Reading Text from File
Const ForReading =1, ForWriting = 2, ForAppending = 8
Set fso = CreateObject("Scripting.FileSystemObject")
Set TextFile = fso.OpenTextFile("C:\FileName.txt",ForReading)
Text=TextFile.Read(10) 'Reads first 10 characters
Msgbox "First 10 characters of file is "&Text
TextFile.Close
Set TextFile = Nothing
Set fso = Nothing

'Reading All the Text from File
Const ForReading =1, ForWriting = 2, ForAppending = 8
Set fso = CreateObject("Scripting.FileSystemObject")
Set TextFile = fso.OpenTextFile("C:\FileName.txt",ForReading)
Text=TextFile.ReadAll 'Reads entire file
Msgbox "File Contents "&Text
TextFile.Close
Set TextFile = Nothing
Set fso = Nothing

'Reading Text File Line by Line
Const ForReading =1, ForWriting = 2, ForAppending = 8
Set fso = CreateObject("Scripting.FileSystemObject")
Set TextFile = fso.OpenTextFile("C:\FileName.txt",ForReading)
linecount = 0
Text = ""
Do While TextFile.AtEndOfStream <> True
linecount = linecount + 1
Text=Text&"line "&linecount&" "&TextFile.ReadLine&vbCrLf
Loop
Msgbox "File Contents "&Text
TextFile.Close
Set TextFile = Nothing
Set fso = Nothing

'Writes Text to a File
Const ForReading =1, ForWriting = 2, ForAppending = 8
Set fso = CreateObject("Scripting.FileSystemObject")
Set TextFile = fso.OpenTextFile("C:\FileName.txt",ForWriting, True)
TextFile.Write "This is a new String of Data"
TextFile.Close
Set TextFile = Nothing
Set fso = Nothing

'Writes Blank Line to a File
Const ForReading =1, ForWriting = 2, ForAppending = 8
Set fso = CreateObject("Scripting.FileSystemObject")
Set TextFile = fso.OpenTextFile("C:\FileName.txt",ForWriting, True)
TextFile.WriteBlankLines(3) 'Writes 3 Blank Lines to the Text File
TextFile.Write "This is a new String of Data"
TextFile.Close
Set TextFile = Nothing
Set fso = Nothing

Column Number of current character position
Const ForReading =1, ForWriting = 2, ForAppending = 8
Set fso = CreateObject("Scripting.FileSystemObject")
Set TextFile = fso.OpenTextFile("C:\FileName.txt",ForWriting, True)
Text = TextFile.ReadLine
ColNum = Text.Column
TextFile.Close
Set TextFile = Nothing
Set fso = Nothing

'Line Number of current position
Const ForReading =1, ForWriting = 2, ForAppending = 8
Set fso = CreateObject("Scripting.FileSystemObject")
Set TextFile = fso.OpenTextFile("C:\FileName.txt",ForReading, True)
Text = TextFile.ReadAll
LineNum = Text.Line 'Gets the Last Line Number
msgbox "Number of Lines in the Text File "&LineNum TextFile.Close
Set TextFile = Nothing
Set fso = Nothing

'Gets the current folder path
Set fso = CreateObject("Scripting.FileSystemObject")
GetCurrentFolder= fso.GetAbsolutePathName(".")

Working with Excel Object

'Create an Microsoft Excel Object using VBScript
Set ExcelObject = CreateObject("Excel.Application")
ExcelObject.visible = True
ExcelObject.WorkBooks.Add 'Adds a workbook to an excel object
ExcelObject.Sheets(1).Cells(1,1).value = "Text in the Cell" 'Writes a text to a particular cell in the excel object

'Open Microsoft file using VBScript
Set ExcelObject = CreateObject("Excel.Application")
ExcelObject.visible = True
ExcelObject.Workbooks.Open("c:\excelsheet.xls",Default, False)
ExcelObject.Sheets(1).Cells(1,1).value = "Text to be entered"
ExcelObject.Activeworkbook.SaveAs("d:\excelsheet.xls") 'Using Save As

'Save the Excel Workbook
ExcelObject.Activeworkbook.save

'Get the Number of Rows used in the Excel sheet
RowCount = ExcelObject.ActiveWorkbook.Sheets(1).UsedRange.Rows.count
msgbox "Number of rows used in the excel sheet "& RowCount

'Get the Number of Columns used in the Excel sheet
ColumnCount = ExcelObject.ActiveWorkbook.Sheets(1).UsedRange.Columns.count
msgbox "Number of columns used in the excel sheet "&ColumnCount

'Change the sheet name of the Excel workbook
ExcelObject.Activeworkbook.Sheets(1).Name = "NameOftheFirstSheet"
ExcelObject.Activeworkbook.Sheets(2).Name = "NameoftheSecondSheet"

'Get the Sheet Name of the Excel workbook
FirstSheetName = ExcelObject.Activeworkbook.Sheets(1).Name
msgbox "Name of the first sheet"&FirstSheetName
SecondSheetName = ExcelObject.Activeworkbook.Sheets(2).Name
msgbox "Name of the second sheet"&SecondSheetName

'Get the value of the Particular Cell 
ValueOfTheCell = ExcelObject.Activeworkbook.Sheets(1).Range("A1").Value
msgbox "Value Of the Cell " &  ValueOfTheCell

'Storing and Using the data in Excel First Row as a collection in Dictionary Object
lNoofColumns = ExcelObject.Activeworkbook.Sheets(1).UsedRange.Columns.Count
Set oDic = New Collection
For lColNumber = 1 To lNoofColumns
        lColValue = ExcelObject.Activeworkbook.Sheets(1).Range(GetCol(lColNumber) & "1").Value
        If (Len(lColValue) > 0) Then
            oDic.Add Item:=lColNumber, Key:=CStr(LCase(lColValue))
        End If
Next

Function GetCol(ColumnNumber)
    FuncRange = ExcelObject.Activeworkbook.Sheets(1).Cells(1, ColumnNumber).AddressLocal(False, False)
    'Creates Range (defaults Row to 1) and returns Range in xlA1 format

    FuncColLength = Len(FuncRange)
    'finds length of range reference

    GetCol = Left(FuncRange, FuncColLength - 1)
    'row always "1" therefore take 1 away from string length and you are left with column ref
End Function

'Getting the value using Column value
Msgbox "Column Number of Cell" & GetCol(oDic("Row 1 value of the 1st Column"))