Eclectic Videos!


My TestPartner


Posted on 2/26/2011 01:51:00 PM by Abhijeet Bhagat


Most usually and likely you must be using VBA macros for some excel automation. There is other side of VBA too i.e. creating small applications to be applied to automation. This article discusses some of these facets.

The Excel Gift
VBA editor
The day MS office was installed on your computer, you were gifted. You were gifted with a free editor that comes with it so that you could churn out the code with all the features and facets of any wonderful code editor.
Most of you must have used the code editor to write snippets called macros in MS terminology. The code editor window gives you something interesting i.e. a project explorer. Thus exposing the excel sheets so that you could manipulate it whichever way you want. Since it’s a Project explorer you know that you are creating a project typically like a development project. Yes! Like a development project, where you can add window forms, add business logic modules and treat the excel cells as data layer. The gift also provides you with some more powerful features like referencing an external dll (dll, tlb.ocx, wsc, .net ineterops etc) thereby providing the extensibility to excel to its existing vast set of functionalities.
You had an automation tool right in front of you all these years
If automation is defined as anything that saves human effort then excel as an automation tool was there right in front of your eyes all these years.
You need to do some file manipulation?
You need to access windows services and check the status?
You need to access the web services?
You need to access web browser?
You need to send out an email?
… And the list goes on. If above are some of the activities that you do routinely and want to automate, Excel is the right tool for you.

UI (Web) Test Automation
The other perspective of using the Excel’s VBA editor is that it can be used to drive IE. Capture the objects rendered on the screen and do actions on it. The automation level can be from simple HTML controls to advance RAD controls like component one if you know how to do it. 

Learning VBA
If you are a VBScript developer or novice or a mix VBA is equally good for all. The learning curve of VBA is smooth and short and in minimal time you can master the VBA language. I was always addicted by the power that a simple excel sheet can offer. With little step by step mastering the Excel has led to building small to full blown applications.
Just with the intent that how can the excel sheets be used as an automation tool, the following examples show case as well as probably instigate you to think in this direction so that even in lack of tools like selenium, QTP, Robot, SilkTest you would be able to achieve much.

The tools to create SOAP Xmls, Submit SOAP requests and get SOAP responses. In Core this is what essentially is done by industry standard tool named SOAPUI.

• You can create an Template to create XML with feature to add /delete multiple nodes on the fly with a custom menu item added via macros
• Embedded textbox controls to edit/modify xml
• Embedded web browser control to view SOAP response

To Submit a SOAP Request. Use reference MSXML2
objXMLHTTP.Open "post", <>, False
objXMLHTTP.setRequestHeader "Man", Post & " " & <> & " HTTP/1.1"
objXMLHTTP.setRequestHeader "MessageType", "CALL"
objXMLHTTP.setRequestHeader "Content-Type", "text/xml"
objXMLHTTP.setRequestHeader "SOAPAction", <>
objXMLHTTP.send Sheet4.txtPreview.Text

EXAMPLE 2: XML to XML Convertor
The tool to convert one form of XML to another using a simple XSLT transformation
To Transform XML to XML using XSLT
Set xml = New DOMDocument60
Set stylesheet = New DOMDocument60
xml.async = False
stylesheet.async = False
xml.Load <>
stylesheet.LoadXML <>' "SOA-SOAP.xsl" 

EXAMPLE 3: UI Automation1 A tool to navigate web pages, access the web links and get the BSE/NSE codes. The VBA code inside the excel sheets mock user actions on the page. The tools does not make use of any third party tools and merely uses IE and MSHTML objects to achieve automation.

Access IE Dom to create IE objects and access the API Clicks the same way SIlkTest would be achieveing.

EXAMPLE 4: UI Automation2 Web UI automation achieved again for an order entry application. Typically this approach can be used to create /pump test data into the applications.

Just use Excel Application Object, Scripting Run time as reference and FileSystemObjects.
Just use MSHTML reference to achieve UI Automation.
Thank You!. Hopefully these examples are good enough for one to think in the direction of using Excel as an automation tool. IWould be glad to help anyone.  Abhijeet Bhagat

No Response to "VBAbility"

Leave A Reply

free hit counter code
Visitors Count