QTP Knowledge Sharings

Types of recording mode

2012-01-09T09:00:00.000-08:00

There are three types of recording modes
1. Normal Recording Mode or Context Sensitive Recording mode

· Default mode of recording in QTP

· Used for recording the operations performed on the standard GUI objects

2. Analog Recording Mode

· records exact mouse and keyboard operations you perform in relation to the screen / application window.

· Basically this is a object level recording such as drawing a picture, recording signature

· Step are saved in data file and it get attached to test case while running by QTP

3. Low Level Recording Mode

· Record the object and related operation whether those are identified by QTP or not.

· This is use full when we want to know the exact x y location of out object or we want to perform action as per co-ordinates.

· This is a Special recording mode provided by the QTP which is used for recording at least some minimum operations on the Non-Supported Environments also.

Drawbacks
1. All the operations cannot clearly record.
2. Script generated is not understandable and enhance able.

New feature avaialble in QTP 11

2011-10-07T01:41:00.000-07:00

Following are list of new feature available in QTP 11:

XPath and CSS based object identification

Good Looking and Enhanced Results Viewer

Easy Regular Expressions

Identifiation relation between neighbouring objects.

Load Function Libraries at Run Time

Test Your GUI and UI-Less Application Functionality in One Test

Recording suppport for Firefox avaiable ...

Log tracking

Embedded/Run java script in webpage

Automatically Parameterize Steps

Silver light add ins

I will be back with detail of each feature including know issue n limitation.

Thanks,

Deepak

Function to create zip file of folder

2011-07-04T09:52:00.000-07:00

Function ZipAttachment

    ZipAttachment = ""

    ' Declare the required variables
    Dim objZip, objSA, objFolder, zipFile, FolderToZip

    zipFile = Environment.Value("c:\ResultFolder") & ".zip"
    FolderToZip = Environment.Value("c:\ResultFolder")

    'Create the basis of a zip file.
    CreateObject("Scripting.FileSystemObject") _
    .CreateTextFile(zipFile, True) _
    .Write "PK" & Chr(5) & Chr(6) & String(18, vbNullChar)

    ' Create the object of the Shell
    Set objSA = CreateObject("Shell.Application")

    ' Add the folder to the Zip
    Set objZip = objSA.NameSpace(zipFile)
    Set objFolder = objSA.NameSpace(FolderToZip)
    objZip.CopyHere(objFolder.Items)

    ' Wait for the folder to be added in the zip
    ' Increase the Wait time is the folder is too large
    WScript.Sleep 2000

    ZipAttachment = zipFile

End function

Function to Send mail through vbscript

2011-07-04T09:46:00.003-07:00

Function SendMail

    ' Declare the required variables
    Dim strTextBody, MailNotification
    dim strModuleName, intTotalPassed, intTotalFailed, intTotalNotExecuted

    ' Assign value to result.
    strModuleName = "Smoke"
    intTotalPassed = 10
    intTotalFailed = 1
    intTotalNotExecuted = 0

    ' Prepare the body text
    strTextBody = "==================================" & vbcrlf
    strTextBody= strTextBody & "Test Execution Report" & vbcrlf
    strTextBody= strTextBody & "Date :" & Date & " " & Time & vbcrlf
    strTextBody= strTextBody & "==================================" & vbcrlf
    strTextBody= strTextBody & "Summary" & vbcrlf
    strTextBody= strTextBody & "==================================" & vbcrlf
    strTextBody= strTextBody & "Total Tests Passed       : " & intTotalPassed & vbcrlf
    strTextBody= strTextBody & "Total Tests Failed       : " & intTotalFailed & vbcrlf
    strTextBody= strTextBody & "Total Tests Not Executed : " & intTotalNotExecuted & vbcrlf
    strTextBody= strTextBody & "==================================" & vbcrlf
    strTextBody = strTextBody & "The attachment contains the" & vbcrlf & "results for the following modules:" & vbcrlf
    strTextBody= strTextBody & "==================================" & vbcrlf
    strTextBody= strTextBody & strModuleName
    strTextBody= strTextBody & "=================================="

    ' Create the Mail object
    Set MailNotification=CreateObject("CDO.Message")

    ' Set the subject
    MailNotification.Subject = Environment.Value("Smoke result")

    ' Set the Mail From parameter
    MailNotification.From = Environment.Value("Manual Team")

    ' Set the Mail To parameter
    MailNotification.To = Environment.Value("Manager")

    ' Set the Mail CC parameter
    MailNotification.CC = Environment.Value("Test Lead")

    ' Set the mail BCC parameter
    MailNotification.BCC= ""

    ' Set the Bod of the Mail
    MailNotification.TextBody= strTextBody & vbcrlf & vbcrlf & "DO NOT REPLY. This is an auto-generated mail." & vbcrlf & "Contact the administrator in case of any queries"

    ' Add the Attachements (Zip Attachment is global which contain required result in detail)
    MailNotification.AddAttachment ZipAttachment

    ' Configure the mail according the smtp server settings
    MailNotification.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusing")=2
    MailNotification.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = Environment.Value("SMTP server")
    MailNotification.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
    MailNotification.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 0 'cdoBasic
    MailNotification.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 120

    ' Update and send the mail
    MailNotification.Configuration.Fields.Update
    MailNotification.Send

    ' Clear the memory
    Set MailNotification=nothing

    Call DNALogEvent("Mail notification sent.")

End Function

How to initiate with Keydriven Framework

2011-06-02T10:53:00.000-07:00

Hello Friend as I have already discussed about framework & types in my last post. Then my one of blog follower asked me how to initiate with any framework, especially Keyword driver.

So I have tried to share my thoughts on the same with example of sample application “Flight” which we usually get with QTP. I know it’s very simple application compare to our practical work but as this command available to person who use QTP, hence I decide to go with it.

Here I will not get deep in coding but will go in concept ually way.

Let’s consider the basic 3 test for flight application as follow.

1. Login into application and logout

2. Login, book the order, confirm it and log out

3. Login, open the order, delete it and confirm the deletion and logout.

Of course these test will not be single line, there will be specific steps with verification point. This step will be our first target to identify the keyword. Definitely there will be 4-5 steps which we have to perform in single window so we take the as single activity as a keyword. It also depends on how the test cases are written.

For example:

Test 1 – Type 1:

· Login into application with correct credential

· Confirm the Home page of application

· Log out from the application

· Confirm the logout that it returns to “Login page”

Test1 –Type2:

· Open the application by from start or writing “Test Application” in run

· Confirm the login page

· Enter User name

· Enter Password and click “Login” Button

· Wait and confirm the Home page.

· Select logout option from the menu item

· Confirm the logout that it returns to “Logint page”

This is very simple and small test but you can see in both type we are doing same thing process of writing is different. These similar things we have to take care while deciding the key word.

Because Login step can be perform by single keyword

· Login(“UserName”,”Password”)

It can also be perform using following 4 keyword

· OpenApplication(“Argument”)

· EnterValueinEditbox(“LoginPage”“UserEdit”, “Username”)

· EnterValueinEditbox(“LoginPage”,“PasswordEdit”, “Hello123”)

· ClickonButton(“LoginPage”,”Login”)

· IspageExit(“Pageopen”)

Well you think no one will split the login step this way, But looking to broader picture to any huge n complex application where single test contain many steps you have decide step with proper scope and which don’t have any dependency.

Many people avoid identifying keyword for the entire test or at least the core area which is our first target as automation ROI. But as I have experience predefining of keyword help you lot and save you most of time.

For above three tests will required following keywords.

· Login
· Bookorder
· Isorderexist
· Deleteorder
· Logout

Similarly for remaining functionality we can identify the keyword which will be max to max 15. And using those 15 keywords we can automate at least 100 or more test. Which is quality or you can say best quality of Keyword driven

While identifications of keyword we should take care it should not perform much operation or more then window, because what we ever operation the keyword do for that it might required input data which we have to provide and manage for expected output.

While identify keyword we should make sure following things

What stage of application/Window will be required?
What input will be provided?
Each key word will provide either true or false and base on that Driver script will execute the next step otherwise it will move to next test marking the current test as Failed.

For Input:

1. From where we are going to provide input. While deciding this we should take case the person who write test case for execution has manage n provide this input data which should not be any lengthy or complex process.

2. Expected output day which always depend on our input data so that we have to maintain also. This specially comes in picture when we are dealing with financial or online web application where small input is going to affect many places & we have to verify at the end of test.

Once you identify the keyword we can start development of function/Keyword. And development of keyword is not dependent on any other keyword which will help to speed up the development. This independency comes only when we identify the keyword properly.

Keyword to Input & Expected Output:

Most of time we maintain keywords/Tests in excel only. So for the test in which we have to provide 2 or 3 value as input data we can put them in column of the excel beside to the keyword only.

But there will be time when you have provide more data around 10 -20 and even every data is not required to that keyword in each test, so for such keyword we can maintain the excel where we can have the unique ID of each test cases. For example

TestCaseID	First Name	Last Name	Class	Contact no	Email
TestCase_001	Sachin	Tendulkar	8	9988998899
TestCase_002	Shewag		6		Shewag@abc.com
…	..		..	.	.
TestCase_025	Zaheer	Khan		9879879899

As you see in this example we can provide input to test as per our requirement and also we can use this same table for expected output. In Blank cell will reflect to keyword that we don’t want to provide this value to test.

Please correct me if I am wrong at any place and feel free ask any query related to QTP or automation framework

Automation Framework & It's Type

2011-05-11T09:52:00.000-07:00

Friends, one of my regular blog reader asked “What exactly Automation framework is”? She was new to automation and while goggling the same she was confused the basic definition. So I thought to share my understanding in simple words. Please free to correct me at any point.

Let’s take it by word itself “Automation Framework”

Automation: To automate a manual task

Ø
Framework: Framework in general words it is outline structure or Master which control complex process or we can say control the execution for various child process.

For testing, automation framework is just combination Master and slave script where master script which control execution of your test (Slave scripts). Nothing more than that. Now the type of automation framework depend on you how you want to implement test and your requirement. It also depends on type of test like business scenario, functional test, regression (Same functional test with different set of data).

Type of automation also depend on the parameters like scope of testing, depth of testing, visibility of product enhancement or further development, product may be developed in more than one technology. In short answer of the question “What we expect from automation”.

Well basic thing is application should be stable and if there is any change you should be well aware of that before planning, designing & implementation of the same. Of course we can’t get visibility of the all parameter in real environment but we should try to cover as much as possible.

Master scripts:

Ø
Master script is the script from where we start our execution. This script will take care of configuring the test environment and ensure the same before executing and test cases and at the end collect the all the result & log and provide them in required format to end user.

Ø Configuration of test environment include number of things like production install, verify required version AUT and supportive application, loading test case and test data, updating registry or other system parameter, etc which we can handle by calling function for different activity.

Type of automation framework:

These types are not specifically decided but these are most possible ways of implementation depending on your requirement.

1.
Data Driven:

Ø When you have the functional test case with different set of data one can use this approach.

Ø In this approach we write the scripts/Function for each functional test where we pass the different set of data as input and expected output is compared as criteria of test case to pass & fail.

Ø Also there will be common function which we will be using in different test.

2. Keyword driven

Ø We take this approach when we have number of test case which we can divide them in number of steps and many of the steps are common in different test.

Ø And we take each step as different “Keyword”. In this framework we maintain the test case and input data in excel.

Ø Implementation of this type of framework includes 4-5 level of hierarchy in execution.

1. First level will be master script to set test environment call the second level.

2. Second level will read the excel and decide which module is set for execution and depending on that it will load required functional library, object library, test data ,etc and pass the call to Third level

3. Third level will read the excel for respective module select in second level and load the test case and pass the call to fourth level

4. In fourth level we read keyword of test cases and execution them one by one.

Ø Here there will for loop in 2^nd, 3^rd & 4^th level which will make sure all the module and respective test case are executed.

Ø Logging of result and generating the final report we can decide as per our requirement. Handling of exception, error, passing & failure condition, moving to next test when current one is failed are implemented in each level as per requirement.

Ø Numbers of level completely depend on your application & number of test case and scope also.

Ø But make sure to keep thing s independent which will be easy to debug, identify the issue and to make any big change in framework in future. It will be also easy to understand.

Ø Implementation of key driven framework required lots of planning, knowledge of domain and application and visibility of possible risk in term technical implementation.

3. Modular driven:

Ø This type approach is taken when different kind of test case for different module or we want to business scenario specific to module

Ø Here we prepared the single script for each module which will execute independently from other.

Ø Here we can also remove the master script and run the each module independently and to run module in single script we can have sort of wrapper script which will just control the sequence of execution

Ø Environment configuration, test data, result, error handling all these are handled in module scripts itself.

4. Hybrid:

Ø Hybrid is combination of keyword and data driven framework. And as per my experience most of keyword driven and converted to hybrid at the end of implementation.

Basic Parameters of Framework

· Configuration file: This can be file or a function which will take care of configuration of test environment before execution. It will install, verify the required version of AUT and supporting application. To test thing on multi OS booting from OS to anther can also be the part of Configuration.

· Make base state: This function will take care to set AUT in required/base after any failure and exception occurred. We can call this function after each failure.

· Global/Environment Variable: This file include the entire global variable which are very frequently used in automation like application/library path, results path, failure detail (Video/Snap), test data path, test cases path, etc.

· Reusable: This file will contain all the reusable application independent function. For example string/Table/excel comparison, loading excels file, converting string to specific format, split the string, write into excel or test file, etc.

· Common function: This file will contain all the common function which specifically developed for application like select combo box item, verify or select tree item, etc.

Friends I have try to give basic idea about automation framework to beginner as per my experience. Please feel free to correct me if I am wrong at any point or if you have any query by putting comment on blog or drop me a email @ deepakpesu@gmail.com

Thanks

Verify Coloum exist in Datatable

2011-05-02T10:00:00.000-07:00

'Arguments Passed         : 1. objSheet - Datatable object
'                      2. strColumnName - Parameter name
'Return Value                    : True or False
Function Fn_VerifyDataTableColumnExists(byRef objSheet, byval strColumnName)

   Dim intParamTotal
   Dim intIterator
   Dim intValue

   ' get total nbr of columns in datatable
   intParamTotal = objSheet.GetParameterCount

   ' loop all columns
   For intIterator = 1 To intParamTotal

          'get name of column
          intValue = objSheet.GetParameter(intIterator).Name

          ' compare if column name matches
          If strComp(strColumnName,intValue)=0 Then
           Fn_VerifyDataTableColumnExists=TRUE
           Exit Function
          End if
   Next
   Fn_VerifyDataTableColumnExists = False

End Function

Function to verify whether any excel instance is open or not. If opened terminate the same.

2011-04-11T10:38:00.001-07:00

Function IsAnyExcelInstancesOpen 'As Boolean

    IsAnyExcelInstancesOpen = True

    Dim objWMIService, objProcess, ProcessCollection
    Dim strComputer, strMessage
    Dim bolTerminate
    Dim InputUserSelection
    ' Make it true if you want to terminate process without any user;s permission.
    bolTerminate = False

    strComputer = "."

    Set objWMIService = GetObject("winmgmts:" & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")

    Set ProcessCollection = objWMIService.ExecQuery("Select * from Win32_Process")

    For Each objProcess in ProcessCollection
    If StrComp(objProcess.Name, "Excel.exe", 1) = 0 Then
        ' If true it will terminate process without any message or intimation
        If boolTerminate Then
            objProcess.Terminate()
        ' It will ask user(Permission) input "Yes" to terminate excel instance opened.
        Else

            strMessage = "An Instance of Excel is running on the Machine. The Framework needs all the Excel instances to be closed for running the Tests." & vbCr & vbCr &_
                "Do you want the Framework to CLOSE all the instances of the Excel?" & vbCr & vbCr &_
                " - Yes:" & vbCr & "Click on Yes to close all the instances of Excel and proceed with the Test Execution." & vbCr & vbCr &_
                " - No :" & vbCr & "Click on No to Stop the test execution and close the Excel files manualy." & vbCr & "--" & vbCr &_
                "Note: All unsaved data in the open excel files will be lost on clicking Yes."

            oUserSelection = MsgBox(strMessage, vbYesNo + vbExclamation , "Excel Instances Open")

            If oUserSelection = vbYes Then

                boolTerminate = True
                objProcess.Terminate()

            Else

                Exit Function

            End If

        End If

    End If

    Next

    IsAnyExcelInstancesOpen = False

End Function

To get list of specific file from specific folder

2011-04-11T10:37:00.000-07:00

'Funtion to get list of the specific type of files(Based on extension) in specified folder. In this example will filter 'out file having "txt", "qfl" & "vbs" extension. To get the list of file you have keept Array global for atleast that 'file (depend on implementation).
Public Function GetFileList (folderLocation, RequiredfileNames()) 'As Boolean

    'Initialize the function and declare the variables
    GetFileList = False
    Dim fso, sfileName, files, fileCount, folder, selectedFile
    fileCount = 0
     On Error Resume Next
    Set fso = CreateObject("Scripting.FileSystemObject")
    Set folder = fso.GetFolder(folderLocation)
    Set files = folder.Files

    'Browse through all the files in the selected folder
    For each selectedFile In files
           sfileName = selectedFile.Name

           'Filter the file based on the extension of the file
        If (StrComp(Right(sfileName,3), "txt", 1) Or StrComp(Right(sfileName,3), "qfl", 1) Or StrComp(Right(sfileName,3), "vbs", 1)) Then
            ReDim Preserve RequiredfileNames(fileCount)
            RequiredfileNames(fileCount) = sfileName
            fileCount = fileCount + 1
        End If
    Next

    'Finalize the function and return the value
    If(fileCount > 0) Then
        GetFileList = True
    End If

End Function

Read a Bookmark in a Word Document

2011-02-21T11:39:00.001-08:00

Set objWord = CreateObject("Word.Application")

objword.Visible = False

Set objDoc = objWord.Documents.Open("e:\copy.doc")

Do until objDoc.AtEndofStream

stxt = objDoc.Readline

MsgBox stxt

Loop

objdoc.Close

objWord.Quit

Get Detail of Object Drive

2011-02-21T11:38:00.001-08:00

Dim filesys

set filesys = CreateObject("Scripting.FileSystemObject")

Set drv = filesys.GetDrive("e")

select case drv.DriveType

Case 0: drtype = "Unknown"

Case 1: drtype = "Removable"

Case 2: drtype = "Fixed"

Case 3: drtype = "Network"

Case 4: drtype = "CD-ROM"

Case 5: drtype = "RAM Disk"

End Select

WScript.Echo "The specified drive is a " & drtype & " type disk."

WScript.Echo " "

WScript.Echo "Total size is " & drv.TotalSize & ". "

WScript.Echo "Available space is " & drv.AvailableSpace & ". "

WScript.Echo "Type is " & drv.DriveType & " "

WScript.Echo "Path is " & drv.Path & " "

Dim filesyst, drive

Set filesyst = CreateObject("Scripting.FileSystemObject")

WScript.Echo "Before Condtion"

drive = filesyst.DriveExists("z")

WScript.Echo drive

If filesyst.DriveExists("z") Then

WScript.Echo("The specified drive does exist.")

Else

WScript.Echo("The specified drive does not exist.")

End If

WScript.Echo "After Condtion"

Sort Excel Worksheet

2011-02-21T11:32:00.001-08:00

Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = True

Set objWorkbook = objExcel.Workbooks.Add

Set objWorksheet = objWorkbook.Worksheets(1)

objExcel.Cells(1, 1).Value = "4"

objExcel.Cells(2, 1).Value = "1"

objExcel.Cells(3, 1).Value = "2"

objExcel.Cells(4, 1).Value = "3"

objExcel.Cells(1, 2).Value = "A"

objExcel.Cells(2, 2).Value = "B"

objExcel.Cells(3, 2).Value = "C"

objExcel.Cells(4, 2).Value = "D"

Set objRange = objWorksheet.UsedRange

Set objRange2 = objExcel.Range("A1")

objRange.Sort(objRange2)

Copy Specifc Data from One Excel to Other

2011-02-15T21:16:00.001-08:00

' Bind to Excel object.
On Error Resume Next
Set objExcel = CreateObject("Excel.Application")
If (Err.Number <> 0) Then
    On Error GoTo 0
    Wscript.Echo "Excel application not found."
    Wscript.Quit
End If
On Error GoTo 0

'Create an object for Excel file

Set xlBook = objExcel.Workbooks.Open("C:\Documents and Settings\deepakpe\Desktop\Mine_doc\new.xlsx")
Set xlSheet = xlBook.Worksheets(1)

' Select the range of data to be copy
objexcel.Range("D1:D46").Copy
set xlSheet = xlBook.Worksheets("Sheet2")
' Select the range where we want to copy
objexcel.Range("C3").Select
objexcel.Range("c3").Copy

objExcel.ActiveWorkbook.Close
objExcel.Application.Quit

'Release an object

Set xlBook = Nothing
Set xlSheet = Nothing

Write in the Excel

2011-02-15T21:10:00.001-08:00

strExcelPath = "C:\Documents and Settings\deepakpe\Desktop\Mine_doc\new.xlsx"

' Bind to Excel object.
On Error Resume Next
Set objExcel = CreateObject("Excel.Application")
If (Err.Number <> 0) Then
    On Error GoTo 0
    Wscript.Echo "Excel application not found."
    Wscript.Quit
End If
On Error GoTo 0

'Delete the Excel
objexcel.Worksheets.Delete strExcelPath

' Create a new workbook.
objExcel.Workbooks.Add

' Bind to worksheet by creating an object
Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)
'objSheet.Name = "User Groups"

' Populate spreadsheet cells with user attributes.
objSheet.Cells(1, 1).Value = "User Common Name"
objSheet.Cells(2, 1).Value = "sAMAccountName"
objSheet.Cells(3, 1).Value = "Display Name"
objSheet.Cells(4, 1).Value = "Distinguished Name"
objSheet.Cells(5, 1).Value = "Groups"

' Enumerate groups and add group names to spreadsheet.
k = 5

objSheet.Range("A1:A4").Font.Bold = True
objSheet.Select
objSheet.Range("B5").Select
objExcel.ActiveWindow.FreezePanes = True
objExcel.Columns(1).ColumnWidth = 20
objExcel.Columns(2).ColumnWidth = 30
objexcel.Worksheets.Add
Set objSheet = objExcel.ActiveWorkbook.Worksheets(4)

Set objSheet = objExcel.ActiveWorkbook.Worksheets(2)
objSheet.Cells(1, 1).Value = "User Common Name"
objSheet.Cells(2, 1).Value = "sAMAccountName"
objSheet.Cells(3, 1).Value = "Display Name"
objSheet.Cells(4, 1).Value = "Distinguished Name"
objSheet.Cells(5, 1).Value = "Groups"

objSheet.Cells(1, 1).Value = "User Common Name"

' Save the spreadsheet and close the workbook.
' Specify Excel7 File Format.
objExcel.ActiveWorkbook.SaveAs strExcelPath
objExcel.ActiveWorkbook.Close

' Quit Excel.
objExcel.Application.Quit

' Clean Up OR Realease the object
Set objUser = Nothing
Set objGroup = Nothing
Set objSheet = Nothing
Set objExcel = Nothing

Wscript.Echo "Done"

Get Cell Data

2011-02-15T21:08:00.001-08:00

Dim objExcel
Dim oSheet
'Create an object for Excel Application
Set objExcel = WScript.CreateObject("Excel.Application")
Set temp = objExcel.Workbooks.Open("C:\Documents and Settings\deepakpe\Desktop\Mine_doc\new.xlsx")

'Loop to go through each cell n get the data

Dim intRow , intCol
intRow = 1 : intCol = 1
WScript.Echo " Coloumn : Data "

Do Until objExcel.Cells(intRow,intCol).Value = ""
    Do Until objExcel.Cells(intRow, intCol) = ""
        WScript.Echo   objExcel.Cells(intRow, intCol).Value
        intRow = intRow + 1
    Loop
    WScript.Echo " "
    intCol = intCol + 1
    intRow = 1
Loop

objExcel.Workbooks.Close ()
objExcel.Quit

'Release the object
Set objExcel = Nothing

Get Sub Excel Name

2011-02-15T21:06:00.001-08:00

Dim objExcel
Dim oSheet
'Create an object for Excel Application
Set objExcel = WScript.CreateObject("Excel.Application")
Set temp = objExcel.Workbooks.Open("C:\Documents and Settings\deepakpe\Desktop\Mine_doc\new.xlsx")

MsgBox temp.Worksheets.Count
MsgBox temp.Worksheets.Application

'Loop get the name of sub excel

For Each oSheet In objExcel.Worksheets
WScript.Echo oSheet.Name
MsgBox oSheet.name
Next

objExcel.Workbooks.Close ()
objExcel.Quit

'Release the object
Set objExcel = Nothing

Delete the Excel sheet

2011-02-15T21:02:00.001-08:00

Dim objExcel

'Create an object for Excel Application

Set objExcel = WScript.CreateObject("Excel.Application")

Set temp = objExcel.Workbooks.Open("C:\Documents and Settings\deepakpe\Desktop\Mine_doc\temp.xlsx")

MsgBox temp.Worksheets.Count

MsgBox temp.Worksheets.Application

temp.Worksheets.Application.DisplayAlerts = false

temp.Worksheets.Delete

objExcel.Workbooks.Close ()

objExcel.Quit

'Release the object

Set objExcel = Nothing

VBscript: Sample script for msg box with class

2011-02-14T03:44:00.000-08:00

Class Hello_World

Public Sub Say_Hello(Name)

MsgBox "Hello, " & Name & ", welcome to " & Garden & "."

End Sub

Public Garden

End Class

Dim MyHello_World

Set MyHello_World = New Hello_World

MyHello_World.Garden = "Fountain"

MyHello_World.Say_Hello(MyHello_World.Garden)

MyHello_World.Say_Hello "Sachin"

MyHello_World.Say_Hello(MyHello_World.Garden)

Debug vbscript & Jscript from Command prompt

2011-02-09T21:23:00.001-08:00

How to debug Windows Script Host (WSH) scripts, which can be written in any ActiveX script language (as long as the proper language engine is installed), but which, by default, are written in VBScript and JScript.

There are certain flags in the registry and, depending on the debugger used, certain required procedures to enable debugging.

To debug WSH scripts in Microsoft Visual InterDev, the Microsoft Script Debugger, or any other debugger, use the following command-line syntax to start the script:

wscript.exe //d

This code informs the user when a runtime error has occurred and gives the user a choice to debug the application. Also, the //x flag can be used, as follows, to throw an immediate exception, which starts the debugger immediately after the script starts running:

               wscript.exe //d //x

After a debug condition exists, the following registry key determines which debugger will be used:

HKEY_CLASSES_ROOT\CLSID\{834128A2-51F4-11D0-8F20-00805F2CD064}\LocalServer32

The script debugger should be Msscrdbg.exe, and the Visual InterDev debugger should be Mdm.exe.

If Visual InterDev is the default debugger, make sure that just-in-time (JIT) functionality is enabled. To do this, follow these steps:

Start Visual InterDev.
On the Tools menu, click Options.
Click Debugger, and then ensure that the Just-In-Time options are selected for both the General and Script categories.

Additionally, if you are trying to debug a .wsf file, make sure that the following registry key is set to 1:

HKEY_CURRENT_USER\Software\Microsoft\Windows Script\Settings\JITDebug

Creating dictionary using Vbscript

2011-02-04T03:11:00.000-08:00

Dim vKey

Dim sItem, sMsg

Dim oDict

'--- Creating a dictionary object

Set oDict = CreateObject("Scripting.Dictionary")

'--- Adding items to dictionary

oDict.Add "One", "Engine"

oDict.Add "Two", "Wheel"

oDict.Add "Three", "Tire"

oDict.Add "Four", "Spanner"

'--- Looping the Collection

For Each vKey In oDict

sItem = oDict.Item(vKey)

'sMsg = sMsg & sItem & vbCrLf

'WScript.Echo (oDict.Count)

'oDict.Item("one") = "temp"

'WScript.Echo sMsg

'wscript.Echo (odict.item(one))

oDict.Key("One") = "nine"

'oDict.Item("nine") = "nine"

oDict.Add "Ten", "ten"

For Each vKey In oDict

sItem = oDict.Item(vKey)

sMsg = sMsg & sItem & " - " &vKey & vbCrLf

WScript.Echo sMsg

WScript.Echo "===============Again============="

WScript.Echo (oDict.Exists ("One"))

WScript.Echo (oDict.Exists ("nine"))

WScript.Echo (oDict.Exists ("Ten"))

'WScript.Echo (odict.item(One))1

'WScript.Echo (odict.item(nine))

'WScript.Echo (odict.item(Ten))

For Each vKey In oDict

sItem = oDict.Item(vKey)

WScript.Echo (sItem & " - " & vKey)

If oDict.Exists("One") Then

WScript.Echo "heello"

End If

If oDict.Exists("nine") Then

WScript.Echo "heello"

End If

Dim temp()

Dim count, counter

count = oDict.Count

WScript.Echo (count)

WScript.Echo

ReDim temp(count)

counter = 0

For Each key In oDict

temp(counter) = key

' WScript.Echo key & " - " & counter

' WScript.Echo temp(counter)

counter = counter+1

'WScript.Echo ("oDict.Keys")

Vbscript Function Related to Date

2011-02-04T03:10:00.000-08:00

dtm1="18-Feb-10"

dtm2="19-Aug-10"

'To Get difference between two date

WScript.Echo "Difference between " & dtm1 & " and " & dtm2

'To Get month difference

intMonthsDifferent=DateDiff("m", dtm1, dtm2)

WScript.Echo "In month: " & intMonthsDifferent

'To Get date difference

intDateDifferent=DateDiff("d", dtm1, dtm2)

WScript.Echo "In date: " & intDateDifferent

If intMonthsDifferent > 0 Then

WScript.Echo "postive"

Else

WScript.Echo "negative"

End If

'Difference In date with difference format Another example

dtm1="18/02/1999"

dtm2="18/03/1998"

WScript.Echo "Difference between " & dtm1 & " and " & dtm2

intMonthsDifferent=DateDiff("m", dtm1, dtm2)

WScript.Echo "In month: " & intMonthsDifferent

intDateDifferent=DateDiff("d", dtm1, dtm2)

WScript.Echo "In date: " & intDateDifferent

If intMonthsDifferent > 0 Then

WScript.Echo "postive"

Else

WScript.Echo "negative"

End If

WScript.Echo "-----------------------------------------------"

WScript.Echo "Function To Get different value of current Date"

WScript.Echo "-----------------------------------------------"

WScript.Echo "Current Date:- " &(Date ())

WScript.Echo "Current Time:- " &(Time ())

WScript.Echo "Current Time:- " & TimeValue (Time)

WScript.Echo "Current Time and date:- " & Now ()

WScript.Echo "Hours:- " & Hour (Time)

WScript.Echo "Month:- " & Month (Date)

WScript.Echo "Second:- " & Second (time)

WScript.Echo "Total Seconds:- " & Timer ()

WScript.Echo "Time with User's Argument:- " & TimeSerial (13,56,8)

WScript.Echo Weekday (Date)

WScript.Echo "Day's name as per index:- " & WeekdayName(Weekday(1))

WScript.Echo "Day's name as per index:- " & WeekdayName(Weekday(2))

WScript.Echo "Day's name as per index:- " & WeekdayName(Weekday(3))

WScript.Echo "Day's name as per index:- " & WeekdayName(Weekday(8))

WScript.Echo "Month's name as per index:- " & MonthName (1)

WScript.Echo "Month's name as per index:- " & MonthName (12)

Data Type Validation in Vbscript

2011-02-04T03:09:00.000-08:00

Dim temp

WScript.Echo (VarType (temp))