Friday, June 20, 2008

Exercise 15: Welcome to the world of VBA / Macros

VBA or Visual Basic for Applications is a light weight programming language (quite similar to Visual Basic) that allows automation of tasks in MS-Office Applications.

UDI Magic v3.0 or higher supports the use of VBA. Using VBA, you can sort data; perform calculations; insert or delete columns or rows in MS-Excel at run-time. In simple words, VBA allows you to do almost any task that you can manually do in MS-Office Applications.

In this exercise, we shall take up "How to use VBA (Visual Basic for Applications) with UDI Magic ?"

STEP 1: Creating a macro file

To begin with, we shall write a simple macro that displays a message "Hello World". You can use Notepad (or any text-editor) to create macro files. Here's a simple macro file (Exercise-15-macro.txt) which displays a message.

Sub HelloWorld
MsgBox "Hello World !!!"
End Sub

1) Sub...End Sub is used to declare a procedure / routine.
2) Google for "Excel VBA" for more help on writing VBA macros.

Before we write XML tags for importing data from our Excel sheet into Tally, let's have a look at the UDI Magic supported XML tags for loading and executing macros.

XML tags for Loading / executing the Macro file

<VBA LOADFILE="macro-filename-with-path" RUNMACROATSTARTUP="macro-name"/>

Example 1:-
<VBA LOADFILE="exercise-15-macro.txt" RUNMACROATSTARTUP="HelloWorld"/>

Example 2:-
<VBA LOADFILE="c:\exercise-15-macro.txt" RUNMACROATSTARTUP="HelloWorld"/>

1) In the first example, we have not specified the full-path for the macro-file. Hence, UDIMagic searches for the macro-file in the UDI Magic startup folder. In the second exmple, we have specified the full-path for the macro-file.
2) The tag <VBA> is UDI Magic specific.
3) The attribute LOADFILE="macro-filename" is used to specify the macro-filename.
4) The attribute RUNMACROATSTARTUP is used to specify the macro name which is to be executed at startup.
5) The Macro-file is executed before the Excel sheet data is processed. As a result,the macro used in this exercise displays a message "Hello World".

STEP 2: Create an Excel Sheet and write XML tags for it

To make the task simple, we shall take up an Excel sheet with Stock Item Masters records (having only one field i.e. the NAME field).

Download the Excel sheet with the XML tags. For your reference, the XML tags given below:-


<!-- This loads and processes the macro-file -->
<VBA LOADFILE="exercise-15-macro.txt" RUNMACROATSTARTUP="HelloWorld"/>

<!-- Create StockItem Masters -->
<!-- Fetch the value for NAME tag from Column A of Excel Sheet -->

No comments:

Excel to Tally and lots more...

UDI-Magic is a software utility that allows you to Import data into Tally. It supports Import of data from various sources like Excel, SQL Server, MySQL, Access, DBF and various other DBF formats.

Website :-
Blog-site :-

If you are new to this blog-site, we advice that you read the Posts from the beginning.i.e. Exercise 1 onwards. The bog-archive is given on the left-hand-side.