Advanced Macros : Lookup Masters from a different Sheet

Here's a sample Excel file that contains "Journal" Vouchers which are to be imported into Tally.

In this sample, macros have been used to lookup the Masters data. The sample Excel-file contains two sheets as shown below:-
1) Vouchers
2) Masters

Here's how the "Vouchers" sheet looks like:-

Here's how the "Masters" sheet looks like:-

Remarks:-
1) The "Vouchers" sheet contains a Column "Bank Name". The values in this Column are replaced at run-time using VBA (macros). Example :- "Axis" is replaced with "Axis bank Ltd" at runtime.
2) In the "Masters" sheet, we have specified the Group-Name under which the Account is to be created.
3) In case, if a Master is not specified in the "Masters" sheet, UDI-Magic automatically creates that Ledger under a Group called "New Ledger Items".

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

1) As macro file is used, you must enable the feature "Trust Access to Visual basic Project". Refer the FAQ: Trust Access to Visual basic Project

2) Download and unzip the files into "c:\udi-magic" folder.
Though you can keep these files in any folder, the Macro-file must always be kept in the "c:\udi-magic" folder.

3) Start the Tally software and create/open a Dummy Company.
4) Minimize Tally Software
5) Run UDIMagic utility, select option "Excel to Tally -> Voucher".
6) 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.

7) Next, follow the instructions given in the wizard-screen to import data into Tally.

ZIP file contents
1) journal-vouchers-with-service-tax.xls (Excel sheet)
2) journal-vouchers-with-service-tax-xml-tags.xml (XML tags file)
3) journal-vouchers-with-service-tax-macro.txt (contains macros)

In Brief
UDIMagic supports VBA (Visual Basic for Applications), a powerful feature that allows you to automate and perform almost all tasks that can be done manually in MS-Excel. In this sample, we use VBA to lookup the Master's Full-Name and the Group-Name from the "Masters" sheet.

---