18 June 2012

Mohammad R. Daoud: Dynamics GP Journal Entry: Paste From Excel–Free Add-in

Ask and you shall receive. My post encouraging you to vote for the ability to Copy and Paste from Excel to the GP GL found its way to Ian Grieve of azurecurve and the Dynamics Community site. Mohammad Daoud found it there.

Then he and his team decided to build the functionality. Mohammad gives us a look at Dynamics GP Journal Entry: Paste From Excel–Free Add-in.

I got a copy over the weekend to play with and it rocked. Mohammad tweaked a few things for me but this is dead simple to use. Simply copy four columns from Excel and paste into GP using the Additional menu or CTRL+F.

In very limited testing:
  • Pasting respects negatives just like GP does. So a negative Debit in the Excel sheet gets pasted as a Credit and vice versa. This means that if all you have is one amount column with negatives and positives everything still works. You still need 4 columns but Credit and Dist. Ref. could be blank.
  • Pasting respects the "Allow Account Entry" setting and won't let you do a JE to an account with "Allow Account Entry" off. Pasting fails for the restricted line and any remaining lines are not pasted..
  • The format is simply Account, Debit, Credit, Distribution Reference. The video shows headers of Credit then Debit. The headers are irrelevant. The transactions are copied and pasted without headers. The data goes in using the order on the GP window.
  • Install is scary easy. Copy two files into the Add Ins folder.
  • Scale seems to be fine. Speed is closer to Integration Manager. 1,000 lines took about 40 seconds. Your mileage may vary but that's roughly 25 lines per second or 1,500 lines per minute. Not SQL import speed but no too shabby and there's essentially no setup..
  • Closing the window and agreeing to delete the transaction seems to kill a long running paste.
  • What this utility does is let you paste into the grid (detail area). You still have full control over the header for batch, date, reversing, reference, etc. Batch approval still applies, etc.
  • No intercompany yet that I can see (I didn't really test this) but this is a first pass. I'm not sure it's even a first release.
  • You can do partial pasting (you don't have to grab a complete range), Paste an unbalanced JE (the trx won't post of course) and paste more than one time into a single transaction. (Paste a range, copy a new range, paste that at the end of the same JE).
  • Pasting also respects blank rows. You can copy beyond the end of a JE and include blank rows. The blanks don't come in to the JE.
  • The range you copy can include blank rows in the middle. Pasting works, although there was an odd visual problem. The last rows didn't seem to be correct but when I scrolled up and down they fixed themselves. It's as if the data was correct but the interface didn't refresh. This is so minor that I'm only mentioning it because it made me double check my test.
This tool is absolutely worth testing. Send Mohammad an email and ask for a copy.