Saturday, August 7, 2010

Income Tax Returns using OpenOffice

The income tax department makes some spreadsheets available(ITRx) for online filing of the return. The spreadsheets are for Excel and contain VBA macros.

I could fill the form but could not get it to generate the XML files. I filed the return on paper this year. However, I decided to get started for next year and see if I could make these forms work on OpenOffice. Since I do not have access to Excel and my knowledge of VBA macros was zero, it was hard even to know what was expected.

Finally, I feel a little less ignorant.

On Fedora and Arch Linux, the macros were commented. On Ubuntu, they were visible. It turned out that vba module is enabled in all; however, on Fedora and Arch Linux, I had to enable 'Executable Code' in Options => Load/Save => VBA Properties. I was confused and thought that VBA module was only available in go-oo.

I am still puzzled as 'Load Basic code' was enabled and I am still ignorant about the difference between Basic code and Executable code!

The ITRx uses the event Worksheet_Change to validate the data as it is being entered. However, the validation was happening even if the macros were commented. It appears that the protected cells within the ITRx sheets are doing the validation as well. Hence, the Worksheet_Change function is not really critical.

OpenOffice 3.2 seems to have had a regression error in handling VBA code. Hence, I had problems on the current versions of Fedora and Ubuntu. Since few complaints were visible, it would appear that not too many Open Office users use VBA macros.

In any case, OpenOffice 3.2.1 worked better. However, the Worksheet_Change method was not called on it and neither were the macros triggered by the buttons in the spreadsheets.

Finally, I downloaded the development version. Now the Worksheet_change macro is being called as well as the calling of the macros on clicking a button. So, I can concentrate on checking out the core of what does or does not work in the ITRx macros. And, hopefully, find an easy way to get over the limitations.

Incidentally, it seems much easier to experiment with development version of OpenOffice while keeping the original installation intact. Arch Linux makes it even easier by offering beta and development versions of Open Office in its repository. All three can be installed concurrently.

4 comments:

  1. Hi, I'm Terence Monteiro, a FOSS enthusiast from Bangalore. I'd migrated the ITR form 1 in 2009 to Open Office: see http://freedom-matters.in/. They make some changes each year, so planning to do it this year too to keep it up to date. Stumbled upon this article while searching if someone has done it already for this year. Let me know if you're doing it for this year.

    ReplyDelete
  2. I suppose this is the month! I do plan to try it with LibreOffice 3.4.

    ReplyDelete
  3. This has a bit more of the methodology of what I tried: http://start.sethanil.com/exploring-software/6

    ReplyDelete
  4. Read your article, was a delight to see your excellent documentation, which must have come after all those lessons learnt, lessons which I too learnt along the same path. We can collaborate on this work, maybe divide the spreadsheets up. My mail is terencemo AT-THE-RATE cpan DOT org.

    ReplyDelete