Using the Dynamics AX Excel add-in for uploading General Ledger journal transactions into Dynamics AX is already well documented on TechNet, various blog sites and alike (see e.g. the following Website).

Yet, when using this functionality in a live environment you will quickly encounter several issues that considerably limit the usability of this feature. As an example, customer transactions, fixed asset transactions, project related transactions, sales tax information and alike cannot be uploaded without making some enhancements to the standard Excel add-in feature.

Within this and the following blog post I will introduce how those enhancements can be implemented to make the Excel add-in feature more applicable in your daily work. Yet, before doing that, let’s start by having a look on how to set up and use the Dynamics AX Excel add-in for uploading transactions into General Ledger journals.

Setup standard Excel add-in for uploading General Ledger transactions
Step 1: Deploy the LedgerServices service group in the AOT
The first step in setting up the standard Excel add-in is to deploy the LedgerService in the AOT.

Step 2: Activate the inbound port
Thereafter, you have to ensure that the LedgerService inbound port is activated. This can be verified in the System administration module under “inbound ports”. Please see the next screenshots.

Step 3: Setup a document data source
The third step is setting up a document data source in the Organization administration module as illustrated in the following screen-prints.

Step 4: Setup Excel template for upload
After the previous steps have been finalized, you can set up the Excel template that is required for uploading the data. To realize this simply select “Add Data” in the Dynamics AX tab in Excel …

… and select the General Ledger data source that has been setup in step 3 before.

Next, setup the journal header by dragging and dropping the required fields from the Ledger journal table.

Once you setup the journal header section, the journal line template fields can be setup similarly by dragging and dropping the required fields into the Excel sheet.

Note: Rather than setting up your own template, you can make use of the Microsoft template that ships with Dynamics AX. In a Microsoft “Contoso” demo environment you can find this template in the General Ledger Template folder. If you plan to use the Microsoft template in a live environment, please ask your system administrator for details where this file can be found.

Please note that the major difference of the Microsoft template and the one I created is that the Microsoft template uses separate worksheets for the journal header, the lines and the notes. Example:

Shortcomings of the standard Excel add-in for uploading General Ledger Transactions
Once you have finished setting up your template, add the header and line data and publish (upload) your data into Dynamics AX. In my example the upload failed because the standard General Ledger Excel add-in does not support all transaction types; in my case, the customer transaction recorded in the third line of my Excel template.

The following screenshot shows you the error message generated. What you can identify from this message is that the standard Excel upload does only support ledger, bank and vendor transactions.

Enhancing the standard Excel add-in for uploading General Ledger Transactions
To overcome the problem illustrated before, I made a change to the LedgerJournalTransType class to ensure that all account types that I need are accepted. Please note that those changes have to be made in the validateAccountType method and the validateOffsetAccountType method of the LedgerJournalTransType class as exemplified in the next screenshot.

A second major shortcoming of the standard Excel add-in is that important fields required for generating ledger postings are not available in the field section of the Excel template. As an example, the standard Excel add-in does not provide users the possibility to select and upload sales tax information, invoice information, due date information and alike.

Luckily, there is also a solution for this issue available. The solution consists of dragging and dropping the required fields from the LedgerJournalTrans table into the AxdLedgerGeneralJournal query. Please see the following screenshot for an example.

Once you have added all additional account types and fields required for preparing and uploading your transactions, you have to (a) generate a full CIL, (b) refresh the AIF services in the AIF services form that can be accessed from the AOT and (c) de-activate and re-activate the LedgerService in the System administration module.

After you have gone through all those steps and open Excel again, you can identify and select the fields that you have added to the AxdLedgerGeneralJournal query. Please see the next screenshot.

After entering all necessary lines in the Excel template that you want to have uploaded and posted in Dynamics AX, you will find that all transactions have been uploaded including the values that are entered in the newly added fields (in my example, the sales tax and invoice information highlighted in yellow color in the next screens).

Additional tips and tricks
At the end of this post I want to provide you some additional tips that might help you when using the Excel add-in for uploading General Ledger transactions:

Tip 1:
Use a filter similar to the one illustrated in the next screen-print to ensure that Excel does not update and show you all previously posted General ledger transactions. That is because such an update might require some time.

Tip 2:
Accountants often prepare the data they want to upload to Dynamics AX in a separate file that allows them repeating uploads quickly in future periods. When doing that, copy and paste the transactions into the second line of the Excel template as illustrated in the next screenshot.

The underlying reason is that the Excel add-in does often not upload the first line that is pasted from a different source into the Excel upload template.

Tip 3:
Once you try posting the transactions that have been uploaded to Dynamics AX you might see a message that says that your vouchers do not balance. This often happens if the rounding of your data in Excel differs from the rounding that Dynamics AX applies.

Rather than deleting the General Ledger journal and trying to fix this issue in the Excel template, try using the post and transfer option that transfers the sticky transactions into a new General Ledger journal. When Dynamics AX is doing this posting transfer, the Dynamics AX rounding rules are applied and usually you can post the transferred transaction in the newly created journal without any other issue.

Summary
To sum up, the illustrated Excel add-in enhancements allow you preparing and uploading almost all kind of transactions from Excel to Dynamics AX. You can thus use the Excel add-in e.g. for uploading intercompany transactions, vendor invoice transactions and transactions required for data migrations from previous systems. When doing that, please be aware of the following pitfalls:

• General Ledger journals do not have an invoice number check. As a result, users might post the same invoice numbers several times without noticing it.
• Despite the fact that you can post customer revenue transactions in General Ledger journals, those journals do not provide you the possibility to print tax compliant customer invoice documents without making further system adjustments.
• Changes in your account structures require setting up new Excel templates. Making adjustments to existing templates usually does not work.
• Do not try to upload mass data. If you plan to upload more than 1000 transaction lines on a regular basis, think about using other upload tools such as the data migration framework.
• A final pitfall of the illustrated Excel add-in that I want to mention here is that it does not allow you uploading project and fixed asset related transactions even if you made the previously illustrated adjustments to the LedgerJournalTransType class. Within the next blog post I will show you how this pitfall can be fixed.