Tuesday, June 18, 2024

Excel VBA - Decoding API responses as JSON

 A blog after 12 years.... I was back to coding and creating utilities for solving operational problems

We decided to have an automated report based on TestCases in TestRails. I decided to use the TestRails API(https://support.testrail.com/hc/en-us/articles/7077083596436-Introduction-to-the-TestRail-API) for pulling information around TestCases in Excel and then massage those numbers for creating an automated report to understand automation progress.

It is kind of tricky in Excel + Mac while writing macros for reading JSON, as the options available are very low. Here are the steps I followed -

1. Download the VBA-JSON file(https://github.com/VBA-tools/VBA-JSON) and run Jsonconverter.bas file

2. Download the VBA-Dictionary file(https://github.com/VBA-tools/VBA-Dictionary) and import Dictionary.cls file

Now you can create objects from the string response. 

sResult = <String response from TestRail API>

Set jsonObject = ParseJson(sResult)

For each dictCase in jsonObject("cases")

    ' strID = dictCase("id")

Next


This helps in navigating through the response from API.

Monday, February 13, 2012

New city new dreams

Finally a blog after 18 months... Don't know what stopped me this long. After frustrating 50 days in new city Chandigarh yesterday was a complete surprise for me.
It all started at KFC outlet in Panchkula where I relished chicken and gulped a bucketful. I came back to apartment and went for a walk in garden which is just across the street. This is what i love about this city. Gardens, Parkways and dedicated lanes for cyclists. All of sudden I saw 2 guys playing badminton and I started watching them from distance. After a while they felt pity on me i think :) and they asked me to play a game. It was just me after that. I bet those guys real flat and they were so impressed that they asked me to join their team for a game of cricket on Sunday. They had probably thought that I would play good cricket if I was playing better badminton. Had a sweat drenching game yesterday morning and I was completely taken back in the history with cracking sound of timber and smell of grass and that too on ground prepared for ICL.

Visited HUDA sports complex(includes ICL cricket ground) which has facilities for Badminton, Kabaddi, Volleyball, Basketball, Hockey and Athletics. This was the first facility where there are avenues for games but no kids to play :). This sports complex is about 1 km from my apartment so I will be visiting it quiet frequently now.

After having lunch I decided to tour the city alone. While I was travelling from Panchkula to Chandigarh saw Kalagraam - city of Adivaasi and made my first stop there. The place has rock sculptures with a open air amphi theatre in center. I was a witness to making of a punjabi video and was relieved that the bollywood heriones are far better and even better dressed. Then I visited the Rock garden(I would describe it Best from waste), Sukna lake and Cacti garden. I was simply amazed about meticulous planning and the way this city has been shaped. Took the giant swings in Rock garden and even dared to sit on camel(I was skeptical about camel not being able to get up but the poor guy did a dash of 200 mtrs with me on his back). It was sheer fun. I came out and was wondering on bike and came across this one road I would never imagine. I would rate 'Jan Marg' the best road I have witnessed in India. It has seperate lanes for Ambulance and emergency vehicles plus additional lanes for cyclists and pedestrians. I had never expected something of this sort in India. In the evening went to sector 17 for bird watching. Wonderful place indeed.

Overall a very good weekend for the first time since I am here. You might just fall in love of this city.

Friday, August 6, 2010

Trek atop Tunnel Top

Tunnel Top again after 2 years... It were the same exciting incessant rains this time around when we visited Tunnel Top (a small place near Karjat) 15 days back with a group of mixed breed people. By breed here I mean there were first timer trekkers, few more experienced ones while some lazy guys who had different thoughts about the venue.
We left Khadki(Pune) railway station at 4pm to reach the spot at around 5:30pm only to get down on a small pavement near the railway tracks before Karjat. There was this huge valley on left side of ours and train gushing from 6 inches on the right. It was terrifying for few among us. As soon as we reached the cottages we were greeted by a small cup of tea. SATISFACTION !!!
We spent our time roaming around the area untill it was dark then had a good dinner with chickne specially prepared for us in authentic maharastrian village style. We spent next 2 odd hours playing cards and went off to bed at around 2 am. The next morning was very beautiful as we set off for a small walk to lashing water nearby. We got our backs turned red as we tried standing tall under the waterfall but to vein. Then we returned to cottages had lunch and played poker for about 2 hours. Neitherless to say I lost again. At around 4:30 pm we started our return journey by waiting at railway tracks only to find that the train was late by 30 minutes and we got drenched in monsoon rains for the last time before we gasped for running train where one of friend dislocated his shoulder (He is fine now :) )and reached back to our destination. Off we went to bed after a hot shower for much needed sleep and with hope of another trek this year in same season.

Wednesday, May 5, 2010

Decoding the Excel VBA password

This thing haunted me since ages. I was able to figure out a way to do this in unique style. I was thinking about finding the password for these many days but later found that we could change it the way we want. Following are the steps to accomplish this little trick:
1) Make a copy of excel file for which you need to find the VBA password (This is just for future recovery just in case the conversion fails)
2) Create a new excel file and set VBA password (eg: testpwd)
3) Download any HEX editor like "Hex-editor XVI32"
4) Open the source file and newly created excel file using this editor
5) Find following string CMG=" and copy the hex code till double quote ends
6) Paste it in the source file at same CMG=" location
7) Repeat steps 5 and 6 for search string DPB=" and GC="
8) Open the source file and enter VBA password 'testpwd' to open the VBA editor ....VOILA

Reply back to me if you need to know passwords for different file formats. Mail me if you have any query or suggestions regarding this.

Thursday, February 4, 2010

My Rajgad trek

Hmm...Finally something to cheer about after long QTP blogs and here I am writing my first travel blog. First of all let me thank all 5 great trekkers who accompanied me and kept my morale high troughout our journey ;).
We had an exciting last weekend wher we spent the whole night chilling out ...literally. It was around 12 degrees but the wind flow gave you an hint about what chilling out meant. We left Pune at 6 (We were supposed to leave at 3:30 but who cares about punctuality in India...thanks to my fellow members) crossed the 12 km stretch on NH4 towards Satara with 4 stops last being at new McDonalds outlet. It was almost dark when we reached the base of Rajgad so decided to have some food. We started around 9 pm and made our way to the top from the 'CHOR DARWAJA'. It was the brightest full moon night and we had some interesting photo shoots during our journey. One of our trekker was about to leave his baggage at base after 10 mins of walking (or I should say was eager to come along without luggage) so we had no other option to carry it on other shoulders. At about 12:30 we had reached the temple on top and then were scavenging for a place to sleep. Finally got one behind the temple had our fire place lit up and we were fast asleep in our bags. Morning sun was extremely beautiful from the fort top and we clicked in heaps. We then started climbing the BAALEKILLA early(9 am is early) in the morning reached the top at about 10AM and enjoyed the beauty of Western Ghats from the top. Visited MACCHI on our way down and came back to the temple for our lunch. Then we started our looong journey down hill as few of our trekkers were afraid of heights. Kicked bikes and zoomed off to Pune with again break at McD. It was fun altogether.

Tuesday, November 10, 2009

Conencting to open internet explorer instance

We come across this situation many times wherein we need to connect to some application already invoked by some other part of code. Simple goggling gives examples about invoking an application but there are very few examples which explains connecting to already open application. Here is small snippet which enables user to connects to existing IE.
Dim objInstances, objIE, strURL
strURL = ""
Set objInstances = CreateObject("Shell.Application").windows
If objInstances.Count > 0 Then ' To make sure we have IE instance open.
For Each objIE In objInstances
Dim sName
sName = UCASE(objIE.FullName)
If Right(sName, 12) = "IEXPLORE.EXE" Then ' Searching for internet explorer
objIE.Navigate strURL ' Navigate to intended URL
Exit For
End if
Next
Else
wscript.echo "No explorer windows are open."
End if


Sometimes there are simple solutions to complex problems, isn't it ?

Friday, August 28, 2009

Testing WebServices using QTP

It is tricky but extremely simple to test web services using QuickTest. I was given this task of creating a framework for testing a particular set of Use Cases which had to be done using services as no UI was available. I started with using the Web Services wizard to pass input parameters and get the output data sets or values. Later I found that the same could not be extended as the Object Repository holds the Webservice name with wsdl path and port name. This forced me think differently to make every parameter configurable.Let's first know few points:

1) SOAP : SOAP, which stands for Simple Object Access Protocol, is defined by the SOAP standard available at http://www.w3.org/TR/SOAP. The SOAP protocol defines the format of standard XML messages that are used to communicate among systems. Because the message format is standardized and based on the XML standard, SOAP can be used to communicate among multiple computer architectures, languages, and operating systems. SOAP enables a new class of applications called Web services, which expose services in a standard way so that application developers can create new applications by putting together services from many different sources on the Web.There are four main areas covered by the SOAP specification: a required SOAP Envelope format that defines what the envelope that surrounds the XML content of a SOAP message must look like; an optional set of encoding rules that defines how language types are mapped to XML in a SOAP message (since this is defined in section 5 of the specification, it is referred to as "section 5 encoding"); an optional RPC format that defines how function calls are expressed in SOAP messages; and an HTTP binding that defines how SOAP messages may be exchanged through HTTP.

2) WSDL: WSDL stands for Web Service Description Language. In order to successfully call a Web service you will need to know how to get to the service, what operations the service supports, what parameters the service expects, and what the service returns. WSDL provides all of this information in an XML document that can be read or machine-processed.
The next necessary steps to call this SOAP method using the high-level SOAP API are:create a SOAPClient objectinitialize the SOAPClient object with the WSDL fileand call the method
Below is a snippet for same:
Set oSOAPClient = CreateObject("MSSOAP.SoapClient")

oSOAPClient.mssoapinit wsdl_path, service_name, port_name

oSOAPClient.Methodname param1, param2, return_param1

Above snippet might give error while running on windows 2003 of ActiveX component. To correct it download the SOAP Tool kit from msdn version 3.0 and install it and following change to first line of script:

Set oSOAPClient = CreateObject("MSSOAP.SoapClient30")


Reply back to me if you need any clarifications or updates.