My own Excel Automation Framework

Part 2

  • We will       start in the VBA       module.(note: In the  developer   tab   click Visual Basic ,It will  open the VBA module.)
  • In the Tools menu, click at the References sub menu and check the boxes to the below mentioned for   adding references,

Visual Basic For Applications

Microsoft Excel 14.0 Object Library

OLE Automation

Microsoft Office 14.0 Object Library

Microsoft Forms 2.0 Object Library

Microsoft ActiveX Data Objects 6.1 Library

Microsoft HTML Object Library

Microsoft Internet Controls

Microsoft XML, v6.0

references

  • In the project window ,click at VBAProject (MyOwnExcelframework.xlsm)       highlighting the same, right click VBAProject (MyOwnExcelframework.xlsm),click Insert Module, It will insert a module with default name Module1.
  • From the view menu click the Properties Window, change the name of the module from module1 to” BrowserFunctions”
  • Hence forth all our browser handling code will reside in the module which has an extension .bas and is a text file. This will help us address the version control challenges.
  • In the code       window ,first declare the 2       public variables as below.

Public AppIE As WebBrowser

Public m As Integer

module

 

  • Copy our first procedure for opening a browser window as below.

Public Sub Open_Browser()

10   On Error GoTo Error_Handler

20   Set AppIE = CreateObject(“InternetExplorer.Application”)

30   AppIE.Visible = True

40   Do While AppIE.Busy

50       DoEvents

60   Loop

70   Exit Sub

Error_Handler:

80   Handle_Error “Open_Browser”, Erl

End Sub

  • The procedure for Navigation to any defined Url is as below , here the Url is parameterized, The Value for the Url will be passed thru the Excel worksheet Run Manager.

Public Sub Navigate_Url()

Dim strUrl As String

 10   On Error GoTo Error_Handler

20   strUrl = Worksheets(“RunManager”).Cells(m, 5)

30   AppIE.navigate strUrl

40     Do While AppIE.Busy

50     Application.Wait (Now() + TimeValue(“00:00:2”))

60     Loop

70   Exit Sub

Error_Handler:

80   Handle_Error “Navigate_Url”, Erl

End Sub

  • The procedure for entering text into a text box is as under. Here we have parameterized the textbox name and the text to be entered.

Public Sub Enter_TextByID()

Dim strTextboxname As String

Dim strTexttoEnter As String

10   On Error GoTo Error_Handler

20   strTextboxname = Worksheets(“RunManager”).Cells(m, 5)

30   strTexttoEnter = Worksheets(“RunManager”).Cells(m, 6)

40   AppIE.Document.getElementById(strTextboxname).Click

50   AppIE.Document.getElementById(strTextboxname).Value = strTexttoEnter

60   Exit Sub

Error_Handler:

70   Handle_Error “Enter_TextByID”, Erl

 End Sub

 

  • The procedure for simulating a button click is as under. Here we have parameterized the button name.

Public Sub Click_Button()

Dim strButtonName As String

 

10   On Error GoTo Error_Handler

20   strButtonName = Worksheets(“RunManager”).Cells(m, 5)

30   AppIE.Document.getElementById(strButtonName).Focus

40   AppIE.Document.getElementById(strButtonName).Click

50   Application.Wait (Now() + TimeValue(“00:00:1”))

60   Do While AppIE.Busy

70   Application.Wait (Now() + TimeValue(“00:00:2”))

80   Loop

90   Exit Sub

Error_Handler:

100   Handle_Error “Click_Button”, Erl

 End Sub

 

 

  • The procedure for Handling Error and Logging the same is as under.

Public Sub Handle_Error(strModule As String, intLineno As Integer)

Dim lngLastRow As Long

 

lngLastRow = Worksheets(“ErrorLog”).Cells(Worksheets(“ErrorLog”).Rows.Count, “A”).End(xlUp).Row

lngLastRow = lngLastRow + 1

 Worksheets(“ErrorLog”).Cells(lngLastRow, 1) = lngLastRow – 1

Worksheets(“ErrorLog”).Cells(lngLastRow, 2) = Date

Worksheets(“ErrorLog”).Cells(lngLastRow, 3) = Time

Worksheets(“ErrorLog”).Cells(lngLastRow, 4) = strModule

Worksheets(“ErrorLog”).Cells(lngLastRow, 5) = intLineno

Worksheets(“ErrorLog”).Cells(lngLastRow, 6) = Err.Description

Worksheets(“ErrorLog”).Cells(lngLastRow, 7) = Err.Number

Worksheets(“ErrorLog”).Cells(lngLastRow, 8) = Err.Source

Worksheets(“ErrorLog”).Cells(lngLastRow, 9) = Err.HelpContext

End Sub

  •  I believe we have good nos. of procedures for POC on the automation of navigation , architecture and flexibility aspect of the framework .
  • Its time to write our first test case.
  • Go back to the excel worksheet, In Worksheet “Run Manager” create       the   Header as under in Bold.

Row       8 Column   C Header label as   “Procedure/Test Step Name

Row       8 Column   D Header label as “Exe

Row       8 Column   E Header label as “Control Identifier

Row       8 Column   F Header label as “Data to be Passed

  • Your Test case starts from Row nos 10. Test Step       1 as under..

Row       10 Column   C as         “Open_Browser”

Row       10 Column   D as       “Y”

  • Test Step 2 as under.

Row 10 Column   C       as         “Navigate_Url”

Row 10 Column   D       as “Y”

Row 10 Column   E       as “https://www.google.co.in

  • Test Step 3 as under.

Row 10 Column   C       as         “Enter_TextByID”

Row 10 Column   D       as “Y”

Row 10 Column   E       as “gbqfq”

Row 10 Column   F as       “which is the best Automation framework”

  •  Test Step 4 as under.

Row 10 Column   C       as         “Click_Button”

Row 10 Column   D       as “Y”

Row 10 Column   E       as “gbqfba”

  •  Test Step 5 as under.

Row 10 Column   C       as         “Click_Button”

Row 10 Column   D       as “Y”

Row 10 Column   E       as “gbqfb”

  •  We have our first test case ready to execute.
  • Just click at RunManager

Ruun Manaageerr

 Part1                                   Part3 

7 responses to “My own Excel Automation Framework

  1. Eldad

    Great idea !!
    Do you have more ideas for WPF and Mobile.

    Liked by 1 person

  2. Akhlesh

    Cool… good if someone do not want to use standard commercial tool QTP … using native VBA/DOM …

    Liked by 1 person

  3. m4i3

    using Comments for sharing reusable functions

    Public Sub unzip_file()
    Dim strmyCsvfile As Variant
    Dim strcsvSavefilepath As Variant
    Dim oApp As Object

    ‘file to be unzipped to be selected via a dialog box
    strmyCsvfile = Application.GetOpenFilename

    ‘path where unzipped file is to be saved is parameterized this is to be taken from test
    ‘ case driver 5th column of respective row in the excel worksheet RunManager.

    strcsvSavefilepath = Sheet3.Cells(m, 5)

    ‘windows shell utility for unzipping
    Set oApp = CreateObject(“Shell.Application”)
    oApp.Namespace(strcsvSavefilepath).CopyHere oApp.Namespace(strmyCsvfile).items
    Set oApp = Nothing

    End Sub

    Like

  4. m4i3

    have followed the convention of commenting the code with explanation.

    Procedure for selecting item from combo list in a browser.

    To access elements in a browser we need to reference Microsoft HTML Object Library (mshtml.tlb),
    Microsoft has provided this type library which when used in conjunction with Microsoft internet controls allow one to access all elements in a web page using DOM elements.

    In the example below we have used getelementbyid method to access the elements.

    Assumptions
    Public AppIE As WebBrowser ‘ has been declared in the initial declarations section.
    The browser has been initialized & navigated to the appropriate web page in an earlier sub or function.

    Public Sub Select_Combo()
    Dim strComboselection As String
    Dim strTexttoEnter As String

    strComboselection = Worksheets(“RunManager”).Cells(m, 5) ‘ id tag/attribute value of the combobox ,data to the variable is parameterized and is passed thru the run manager worksheet
    strTexttoEnter = Worksheets(“RunManager”).Cells(m, 6) ‘ combo box Value to be set, data to the variable is parameterized and is passed thru the run manager worksheet

    AppIE.Document.getElementById(strComboselection).Click
    AppIE.Document.getElementById(strComboselection).Value = strTexttoEnter

    Do While AppIE.Busy
    Application.Wait (Now() + TimeValue(“00:00:2”))’ wait for 2 seconds

    Loop

    End Sub

    Like

  5. m4i3

    have followed the convention of commenting the code with explanation.

    Procedure for downloading File from internet.

    Public Sub Save_File()
    Dim strFileurl As String ‘ url of the file to be downloaded this can be parameterized or created programmatically
    Dim strFilepath As String ‘path where the file is to be saved on the desktop the path must exist
    Dim strFilename as String ‘ name of the file to be saved this can be parameterized or created programmatically
    Dim strmyurl As String

    strFileurl =Worksheets(“RunManager”).Cells(m, 5) ‘ data to the variable is parameterized and is passed thru the run manager worksheet
    strFilepath =Worksheets(“RunManager”).Cells(m, 6) ‘ data to the variable is parameterized and is passed thru the run manager worksheet
    strFilename =Worksheets(“RunManager”).Cells(m,7) ‘ data to the variable is parameterized and is passed thru the run manager worksheet

    strmyurl = strFileurl

    Dim WinHttpReq As XMLHTTP
    Set WinHttpReq = CreateObject(“Microsoft.XMLHTTP”)
    WinHttpReq.Open “GET”, strmyurl, False
    WinHttpReq.send

    strmyurl = WinHttpReq.responseBody

    If WinHttpReq.Status = 200 Then
    ‘HTTP_STATUS_OK for more on HTTP Status Codes visit https://msdn.microsoft.com/en-us/library/windows/desktop/aa383887(v=vs.85).aspx

    Set ostream = CreateObject(“ADODB.Stream”)
    ostream.Open
    ostream.Type = 1
    ostream.write WinHttpReq.responseBody
    ostream.SaveToFile strFilepath, 1
    ostream.Close
    End If

    ‘housekeeping closing the objects

    Set WinHttpReq = Nothing
    Set ostream = Nothing
    End Sub

    Like

  6. Lou Wilson

    My experience using Excel as the front end for test automation.

    In 2005 I was running tests with WATIR with Ruby as the language and IE as the browser. I used VBA to generate the ruby scripts then fire off ruby in that script. Great, but manual testers were uncomfortable putting Ruby on their systems…

    In 2006 I read a book “.NET Test Automation Recipes” and saw that I could attach IE as a com file and run it from Excel/VBA. Did that for several years. Disadvantage: limited to running in IE / Windows.

    A few years ago I started using Selenium for VBA, and running 4 browsers from IE: FireFox, IE, Chrome, and PhantomJS. At the time Selenium VBA still had some issues. And, I was still stuck to running in Windows.
    What I love about using Excel as the front end is a) you don’t have to build up a data input and reporting UI, and can do all your test management from the spreadsheet; b) you can set up your VBA classes or subs as a page description language so that the higher end scripts don’t have to be individually managed whenever an object has been modified; and c) you can pass off your finished test sheet to a manual tester to execute

    More recently, I started using Python as the language, and Google Drive for the table/ui. Thus, I was able to break out of the Microsoft platform. The link from Python to Google Drive was slow but adequate.

    So nowadays, I am back to using Excel, and all of my interface to Selenium is through com files I write in C#, and a very thin veneer in VBA. I typically write a Page Object Model utility in C#, and the high end scripts in C#; using the Excel for the parameters for data driven testing, and for the result output.

    My next idea I would like to play around with is to use Excel / Mac to drive that model, with python as the back end language.

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s