Thursday, April 10, 2008

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)

Friday, April 4, 2008

Fetch/Export data from Ledger Master

FAQs: Tally to Excel

"I am using UDI-Magic 'Tally to Excel' option to retrieve Ledger Master data from Tally 9 into Excel. This generates an XML parse error. The SELECT-statement and error message are given below".

SELECT statement
SELECT $Name, $Parent FROM Ledger

Error Message
XML parse Error (Invalid unicode character)

Problem cause and Solution
Though the SELECT-statement show above is correct, still udi-Magic generates error message. This is because the data that is returned by Tally contains some Invalid Unicode characters (as the said error message suggests)

On further analysis, we found that Tally has a Ledger named "Profit & Loss a/c" which falls under Group "# Primary". The Group name contains an invalid unicode character as the first character (although we have show # hash). On further analysis we found that the Group Name returned by Tally was "&#4; Primary".

As the cause of this problem is invalid unicode character which relates to Ledgers created under the Group "Primary", we filtered out the list using WHERE clause.

Here is the new SELECT-SQL that works fine:-

SELECT $Name, $Parent FROM Ledger WHERE not $Name="Profit &amp; Loss a/c"

Wednesday, April 2, 2008

Introducing Tally to Tally feature

UDIMagic v3.0 [Professional] Release 1.40 comes with a new feature called Tally-to-Tally that allows you to transform Tally data.

As the name suggests, the Tally-to-Tally feature allows you to retrieve data from one Tally Company; apply transformation rules; and then send it to another Tally Company. This is particularly useful in case of concerns wherein Sales of one firm becomes Purchase of another sister concern firm. Herein, UDIMagic picks data from the Source company; transforms it and then sends it to the Destination Company, thus saving your precious time and avoiding typographical mistakes.

Know more about Tally to Tally feature...

Working with UDFs - Singapore GST

FAQs: Excel to Tally

"Can I use UDIMagic to import UDF's (user-defined-fields) data from Excel into Tally ?"

Yes. UDIMagic supports all Tally Masters and Voucher-types including Tally UDFs (User-defined-fields). To be more specific, UDIMagic can process whatever data that you can manually enter in Tally Software for any Master or Voucher.

Herein, we shall take up sample Excel sheets which contain Purchase and Sales Vouchers data including UDF data that is to be imported into Tally.

Singapore GST
A TCP file (GST72.TCP) has been provided by Tally Solutions, Bangalore that provides new reports/fields required by Tally Customers in Singapore.

The sample Excel-sheets provided herewith contain a field for GST (Goods and Service Tax) that is to be imported into a UDF which is created using TDL (i.e. GST72.TCP file). GST is applicable in Signpore and is similar to VAT that is applicable in INDIA.

Learn more about "Using UDIMagic to import data including UDF (Singapore GST) into Tally Software".