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
Remarks:-
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
Syntax:-
<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"/>
Explanation:-
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:-
<XMLTAGS CELLREFERENCE="A1" xmlns:UDF="TallyUDF">
<!-- This loads and processes the macro-file -->
<VBA LOADFILE="exercise-15-macro.txt" RUNMACROATSTARTUP="HelloWorld"/>
<!-- Create StockItem Masters -->
<MASTER TYPE="STOCKITEM">
<NAME.LIST>
<!-- Fetch the value for NAME tag from Column A of Excel Sheet -->
<NAME COLUMNREFERENCE="A"/>
</NAME.LIST>
</MASTER>
</XMLTAGS>
No comments:
Post a Comment