The purpose of this paper to demonstrate how to use the excel sheet to automate the testing of calculated web application.
Suppose you have an application that use some complex calculations and you want simply to automate the testing for this application.
Prerequisites:
1- Before we start our excel automation, we need to download IEDev toolbar, as we will use it through our tutorial to find the application objects on the web page.
The IEDev toolbar link is:
http://download.microsoft.com/download/f/3/c/f3c93e70-ccdc-46c9-bbd4-70d94bdd0cc9/IEDevToolBarSetup.msi
2- We will explain our simple automation on iSales application, the application simply create quotes for any product and doing some pricing calculation on the created quotes to get the final price for the quote.
We will select simple TC from the pricing calculation page and will apply our simple excel automation on it:
A. The user first opens application URL.
B. Select desire quote from dashboard by click on quote number.
C. On the pricing page check that Sales Tax Amount = Proposed Sell Price * Sales Tax %
D. Compare the displayed Sales Tax Amount value(actual results) with the expected value (the calculated Sales Tax Amount in the excel)
Steps for Automation:
1- Start writing your calculation formulas in excel :
In this step we will start writing our formulas direct to the excel sheet (Figure4), write in cell G106 the following formula:
G106 = G105 * (F106/100)
Or to enhance our formula to be readable and to use it easily later we will rename the cells to be readable names instead of using cell location (G106), you can do that by right click on the cell you want to rename and choose “Name a Range” then enter the required name.
Now we can rewrite the formula to be:
SalesTaxVal = Price_Before_Taxes2 * (Sales_Tax2/100)
Instead of G106 = G105 * (F106/100)
2- Put new command button n the excel:
a. Ensure that the developer toolbar is displayed in your excel ribbon -from upper left corner on the excel click on this sign 
then choose Excel Options , and from popular tab check the check box “Show Developer tab in the Ribbon”-
b. Go to the developer tab and select insert button
c. After inserting the command a dialog called Assign Macro will appear , click cancel on the dialog
3- Reference to HTML control library:
a. Select the button we add in the previous step then from the developer ribbon select option “View Code”
b. From Microsoft Visual Basic window menu select Toolè References
c. From available references check Microsoft HTML Object library (if you don’t find the reference click browse then locate the file “C:\Windows\system32\mshtml.tlb” )
d. Now press ok to close the references window and return back to the code window.
4- Using the library Microsoft HTML Object library:
a. We will define 2 variables, the first one call “IE” which will navigate to the desire pages and the second is DOC which contains the entire element inside the page (text boxes and labels, etc...)
b. Declaring the variables:
Dim IE As Object
Dim DOC As HTMLDocument
c. IE.navigate : navigate to any given page in our example we will use it to navigate to home page , then to open the first quote on the dashboard
Example:
IE.navigate (http://utc-carrier/ISales_Carrier_Test_MS2/DashBoards/Dashboard.aspx)
d. After navigation we need to wait until the page complete loading by using the following loop:
e. After the page loading we will set all IE objects content to DOC variable by using the following code:
Set DOC = IE.document
f. After navigating to the home page we need to open the first displayed quote o n the homepage
i. Open the home page using your IE
ii. Open IE Developer toolbar then click the arrow on the top left of toolbar
iii. Select the desire link
iv. Copy the hrefvalue value from the IEDev toolbar
v. Pass the href value to the IE.Navigate:
IE.navigate "javascript:__doPostBack('ctl00$ContentPlaceHolder1$LeadsSearch$meetingQuotesGrid','Select$0')"
5- After opening the desire quote we need now to capture the displayed value of the “Sales Tax Value” and compare it with the excel value.
a. First navigate to the pricing page
b. Open IEDev toolbar and select the field that you need to get its value (Sales Tax % value in our example)
c. Then copy the contents of the id value from the IEDev toolbar (in our example the value is “ctl00_ContentPlaceHolder1_ListPricing1_lbl_CostUpSalesTax_Val”)
d. Now return to the code window and write the following command:
i. Declare variable called PageSalesTaxVal and ExcelSalesTaxVal
ii. Capture the value of field “Sales Tax Amount” from the page using method called getElementByid –note that we passed the id that we get in step “c” to this method –and put it in our variable PageSalesTaxVal.
PageSalesTaxVal = DOC.getElementById("ctl00_ContentPlaceHolder1_ListPricing1_lbl_CostUpSalesTax_Val").FirstChild.NodeValue
iii. Capture the value of field Sales Tax Amount from the excel sheet:
ExcelSalesTaxVal = ActiveSheet.Range("SalesTaxVal").Value
iv. Compare Excel vs the page
If ExcelSalesTaxVal <> PageSalesTaxVal Then
MsgBox "Error in Value SlaesTaxVal", vbCritical
End If
6- Return to Excel then press on the button, and enjoy with our little automation code.
7- See The complete code below.
Sub Button1_Click()
Dim IE As Object
Dim DOC As HTMLDocument
Dim PageSalesTaxVal As Long
Dim ExcelSalesTaxVal As Long
Set IE = CreateObject("InternetExplorer.Application")
'The code below to position the IE window
IE.Visible = True
IE.Top = 0
IE.Left = 0
IE.Height = 1400
IE.Width = 1400
''''''''''''''''''''''''''''''
'The code below to open the home page of the application
IE.navigate "http://utc-carrier/ISales_Carrier_Test_MS2/DashBoards/Dashboard.aspx"
' This loop to wait until the page complete its loading
Do Until Not IE.Busy And IE.readyState = 4
DoEvents
Loop
Set DOC = IE.document
' The following code to open to the desired quote (we take this from IEDev toolbar )
IE.navigate "javascript:__doPostBack('ctl00$ContentPlaceHolder1$LeadsSearch$meetingQuotesGrid','Select$0')"
' This loop to wait until the page complete its loading
Do Until Not IE.Busy And IE.readyState = 4
DoEvents
Loop
‘The follwoing code to navigate to the desired quote
IE.navigate "javascript:__doPostBack('ctl00$LeftMenu1$ctl29','')"
Do Until Not IE.Busy And IE.readyState = 4
DoEvents
Loop
' 1.Get the displayed value from the page (Actual Result)
PageSalesTaxVal = DOC.getElementById("ctl00_ContentPlaceHolder1_ListPricing1_lbl_CostUpSalesTax_Val").FirstChild.NodeValue
' 2.Get the Value of the ExcelSheet(Expected Result)
ExcelSalesTaxVal = ActiveSheet.Range("SlaesTaxVal").Value
' 3.Compare the actual vs. expected
If ExcelSalesTaxVal <> PageSalesTaxVal Then
MsgBox "Error in Value SlaesTaxVal", vbCritical
End If
End Sub
