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

• 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

 

• 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

Part1

Part3