Using VBA to manipulate Excel sheet data at run-time

Here's a sample Excel sheet named (v-link-collection.xls) that contains Receipt Voucher entries which are to be posted into the Tally Software.

Understanding the Excel sheet data
The Excel sheet contains details of Cash received from various parties. Though there are several entries (parties), a single entry is to be passed as shown below:-

Cash a/c Debit
Party A a/c Credit
Party B a/c Credit
Party C a/c Credit

For this purpose, we have written a MACRO (v-link-collection-macro.txt) which calculates the TOTAL AMOUNT that is to be posted to the Cash A/c in this case. Note that this is calculated at runtime using the commands specified in the macro-file.

How does UDIMagic function ?
When you run UDIMagic, you are prompted to select
a) the Excel sheet which contains the data and
b) the XML tags for it.

Next, when you click the START button, UDIMagic loads the Excel file and the XML tags. If you view the XML tags file (in Internet Explorer), you will notice that we have specified a tag that load VBA macro file. It is shown herewith for your reference :-

<VBA LOADFILE="v-link-collection-macro.txt" RUNMACROATSTARTUP="NewColumns"/>

UDIMagic processes /executes the Macro file which calculates the Total Amount and stores it in Column AB. Though this may sound simple, using VBA you can almost do anything that you could manually do in MS-Excel. That's the power of VBA. In case, if you using Excel 2003 or higher, you may get an error-message, and if so please refer this FAQ: "Programmatic access to Visual basic project is not trusted" to solve this issue.

For more information on VBA, please refer this FAQs: "Using VBA with UDIMagic"

Follow these steps to import the Excel-sheet data into Tally Software.

1) Download and unzip the files into "c:\udi-magic" folder.
2) Start the Tally software and create/open a Dummy Company.
3) Minimize Tally Software
4) Run UDIMagic utility, select option "Excel to Tally -> Voucher".
5) Next, select the Excel sheet and the XML-tags file when prompted. Note that the XML-tags file is to be selected in the same screen in which you are prompted to select the Excel-sheet.
6) Next, follow the instructions given in the wizard-screen to import data into Tally.

ZIP file contents:-
v-link-collection.xls (Excel sheet)
v-link-collection-xml-tags.xml (XML tags)
v-link-collection-macro.txt (Macro file)

---