Copy Excel data to a Journal in NAV

With the ability to “Send to Excel” from Microsoft Dynamics NAV and “Copy and Paste” back into Microsoft Dynamics NAV, we have the ideal way to manipulate data in an Excel spreadsheet and quickly paste the data into a journal for posting.

This method is much quicker and easier than adding the lines in Microsoft Dynamics NAV or using Rapid Start.

In conjunction with manipulating the posting groups, this could be used to bring in other sub ledger history (Customer, Vendor, Bank, Inventory, etc.).

I have used this method to import years of G/L history when performing a new implementation or adding a new company with history to an existing database.  If there are a large number of entries to bring in, I usually split them into manageable chunks (around about 20,000 to 40,000 lines) for reconciliation purposes. I’ve also used it to bring in payroll journal data where no interface exists between the payroll package and Microsoft Dynamics NAV.

Here’s how:

1. Open up the journal batch in Microsoft Dynamics NAV that you want to use.
2. Sort and display all the columns that you need to complete the journal (including dimensions used).
3. If this is the first time using this ability then as an example, create and complete a single journal line as if you were going to post it manually. See example below:

‍

4. Click the “Microsoft Excel Send To” icon on the action ribbon. Now you have a row of data in the correct format for copying across to NAV.

5. Create other rows in Excel as required, remembering that any dimension data needs to be in capitals, see example below:

‍

‍

6. Highlight the newly created rows and press “Ctrl + C” to copy them.

7. Go back to the journal screen in Microsoft Dynamics NAV and press “Ctrl + Shift + V” to paste the rows.

8. Process the journal as normal.

During today’s coffee break, we learned how to copy a Dynamics NAV journal to Excel, add and manipulate the data in Excel and copy it back into a Microsoft Dynamics NAV journal, ready for posting. But this is not the only use of this functionality. I challenge you to think of ways that it could help you and your organisation, and feel free to share your findings with us all.