GP Paste From Excel With Intercompany

jamesdlyn Microsoft Dynamics GP, , , , , ,

As mentioned before, here is my add-in for Microsoft Dynamics GP. The version I use it with is 2013 R2 but I have no reason to believe that it would not work for older or newer versions of GP. While developing it, I made sure to make it conform to the same format as GP’s built-in Paste feature so the order of the columns is typically Description, Account, Debit and Credit. For Intercompany, the company ID is required and for Payables or Sales, the document type is needed. The dll reads the data stored in your Clipboard in the order shown in the examples so you just need to copy data from Excel or any Tab-delimited file.

Installation: Drop the dll into your Microsoft Dynamics installation folder’s Add-In folder and restart GP.

“Paste Entry” is accessible from the “Additional” toolbar at the top of the following windows:
– GL Transaction Entry
– Payables Transaction Entry (Distribution Window)
– Sales Transaction Entry (Distribution Window)

GPPaste(PM) GPPaste(RM) GPPaste(GL)

Features Supported:
– Intercompany
– Multicurrency (values are entered as Originating amounts)

Download: GPPaste.dll (Version 1.0.5578.19478)

Copy template: Paste GL Transaction Example.xlsx

GPPaste(Template)

Enjoy,
~James

Disclaimer: I am very much an amateur programmer. I have never taken an official Microsoft course. I cannot guarantee any code/program that I provide as I only have the ability to test my work on a small sample set. Users accept all the risk of using anything I provide and are encouraged to thoroughly test in a dedicated test environment before moving to production. Files I provide are free for use and are not to be resold. By using my files you agree to not hold me liable for any damages caused by said files.

56 thoughts on “GP Paste From Excel With Intercompany”

  1. Great Job to the couple of tools you have made available. I did notice that if you have default purchasing accounts that they will be overwritten by the paste into the journal entry, however you do end up with two Accounts payable lines. A couple of solutions are to paste in just the purchasing accounts or you can click the delete button to delete all distribution accounts and then past in your journal entry.

    I have tested with GP2015 and it appears to work just fine for normal and intercompany transactions.

    Looking forward to reading your blog posts and seeing what other tools you come up with.

    Like

    Reply

  2. Thanks Paul!
    With respect to the AP pasting, that is *kinda of* by design. It actually starts the paste on the active row and goes down so if you do not have any row activated the default is the top/first row. If you create and select a new blank row below the default AP lines the paste will start there.

    At our company we disabled manual entry to the AP accounts so the default AP account is necessary for us so that’s the way I handled it. I guess I could automatically go to the bottom on the first paste but scroll windows in GP aren’t the friendliest things so I just thought it easier to tell them to select the last row. To take care of the first blank row, just right click and select delete row.

    Like

    Reply

  3. Hello – we have followed the instructions, unblocked the dll after pasting it in the local Addins folder, we are logged in as Administrator and get the following error – GP Stopped Working, with the following details:
    Problem signature:
    Problem Event Name: CLR20r3
    Problem Signature 01: Dynamics.exe
    Problem Signature 02: 14.0.84.0
    Problem Signature 03: 55510502
    Problem Signature 04: mscorlib
    Problem Signature 05: 4.0.30319.34209
    Problem Signature 06: 534894cc
    Problem Signature 07: 27cb
    Problem Signature 08: 0
    Problem Signature 09: System.IO.FileLoadException
    OS Version: 6.1.7601.2.1.0.256.48
    Locale ID: 1033
    Additional Information 1: 0a9e
    Additional Information 2: 0a9e372d3b4ad19135b953a78882e789
    Additional Information 3: 0a9e
    Additional Information 4: 0a9e372d3b4ad19135b953a78882e789

    Do you know why we can’t this to work (GP 2015 R2).

    Thank you!

    Like

    Reply

  7. I’m testing it out with my DGP2013 install. I dropped the dll into add-ins, unblocked it, and restarted the app.

    I don’t see the paste option as shown.

    Like

    Reply

    • Other people have commented that the “Unblock” doesn’t actually work so their work arounds are to either:
      1. Copy the file onto a usb, delete it from the Addins folder, and paste it back from the USB
      or
      2. Zip the file, delete the file from the Addins folder, unzip the file back into the folder

      Is your Dynamics installation local or through remote software such as Citrix?

      Like

      Reply

  9. I did initially have the errors, but after the unblock there weren’t any. I will try this anyway.

    This is a local install.

    Like

    Reply

  10. Okay. I downloaded and unblocked, copied to usb and then to Add-ins folder.

    PC was restarted.

    No errors, but no listing for the option.

    Like

    Reply

    • 1. I know this is a simple thing but bare with me. For Sales/Payables, are you in the distributions window and not the document entry window (making sure “Distribution” is in the title bar)? The paste option only appears on specific windows:
      -Manual General Journal Entry
      -The distribution window of sales transaction entries
      -The distribution window of payables transaction entries

      2. Is the “Additional” menu visible at all and are there any other options underneath?

      3. There is another General Journal free paste developed by a dynamics MVP http://di.jo/GPExcelPastePredownload.aspx. If you have the time, see if that one works. If it does then its an issue with my add-in, if not then it has something to do with your system configuration not allowing the add-in to run properly. Either way, both would let us troubleshoot further.

      Like

      Reply

  11. The Journal Entry already has this option for us and it appears that the other specific windows are not used by us. STE without the “Distribution” is what we would need.

    I did look into the DI version from Mohammad Daoud, but it looks like they have merged it with the paid version. I don’t see that the free version is available.

    Thanks for the help.

    Like

    Reply

    • Hi Kara,
      The company I worked with did not have/use analytical accounting so I am not sure. Best I can say is try it and test. It will not post the entry by itself so if it does not work as intended, you can always discard the entry.

      Like

      Reply

  15. James,

    I’m attempting to do a similar project where I have to populate multiple lines of a GP scrolling window. I can’t seem to get the code to advance to the next line though. I don’t see any documentation on how to do this in the MSDN pages for GP Tools for Visual Studio.

    I’ve tried including a reference to the Continuum DLL and using the ExecuteSanscript() method to call the Dex Window_ScrollScrollingWindow() function, but that isn’t working. It keeps returning FOCUS_NOT_IN_SWIN or FOCUS_DIVERTED.

    Can you point me in the right direction?
    thanks!

    Like

    Reply

    • Is this a stand-alone application or a GP add-in?

      Assuming you’re successfully within the GP instance, I use 4 basic steps to move and populate through fields in a row and then a single command to move to the next line. I do this using Sanscript as I couldn’t get some of the commands to work in the C# libraries:
      1. Focus the field of the window/transaction scroll of the form you are in
      2. Move to the field
      3. Set the data value of the field
      4. Execute the run script for the field (this is the field validation)
      5. Execute the Window_ScrollScrollingWindow command on the Transaction_Scroll with command SCROLLTYPE_NEXT

      Here is a snippet from my add-in: Scroll Sample Code

      Let me know if this helps or if you need me to go more in-depth!

      Like

      Reply

      • Thanks! Your sample code works for my GP AddIn.

        My original code used the SDK to Focus(), set “field.Value”, and call RunValidate() for each field being populated. But I couldn’t figure out how to advance to the next scrolling window. Using Continuum to call Window_ScrollScrollingWindow() didn’t work.

        But when I use the Continuum library to focus, set the value, and run the change script per your sample code, the subsequent call to Window_ScrollScrollingWindow() works.

        Too bad the SDK is incomplete. Would be much cleaner to code in either C# or Dexterity/Sanscript rather than calling one from the other…

        Like

      • Glad it worked, it took me a long time to find the correct command to get it to work because I ran into the exact problem that you did where the c# library commands wouldn’t work.

        From my understanding, all the SDK does is wrap the Sanscript commands into nice C# commands for you, as GP runs off of Sanscript. They just must have broke some of the commands after a version update or something. I’m just glad they let us execute Sanscript from the library else we would be stuck in these situations 🙂

        Like

  18. I am trying out the AP copy/paste. For each transaction we need the invoice # in the vendor history, but when we put that in the description column it does not show up in vendor history. Also when there are multiple transactions they get lumped together as one amount, 4 invoices of $100 becomes 1 payable of $400 and it should be 4 payables of $100. Is there any way to do this?

    Like

    Reply

    • Hi Quentin,
      First, I am not sure which field you are referring to when you say “vendor history”. Anything in my description column when you copy should be pasted into the description of each distribution line.
      When you say they get lumped together as one amount, do you mean entries to the same GL account get posted as the $400?
      The idea of my add-on is not to paste multiple invoices at once. It is for one invoice that needs to be posted across many different GL accounts in the distribution window. If you are trying to post multiple invoices at once, I would suggest you use Integration Manager as it is already built into Dynamics GP.
      James

      Like

      Reply

  19. Hello James,
    I’ve attempted to use the GPpaste.dll, however, in a GL batch, when I check the Intercompany box prior to pasting the paste function goes away. I have done some investigation and it seems since we dont use currency that this might be the issue. Can you offer any insight?

    ~Allen

    Dynamics GP 2016 R2 16.00.0552

    Like

    Reply

    • Hi Allen,
      Just to make sure, are you referring to the Paste button on the tool bar or my paste option under the Additional menu? My option will appear under the Additional menu where as Microsoft’s paste will go away if you try to use intercompany/multicurrency.

      Like

      Reply

  22. Hi James,
    I am testing the Excel Paste for Intercompany General Journal Entries for a customer. I’ve downloaded the GPPaste.dll file, and unblocked security. I open up the Transaction Entry window and fill out the header including checking the Intercompany checkbox. When I go to Additional > Paste Entry (JL) I get the message: You can’t change a company ID; delete the row and retype your entry. I have tried placing the cursor in various places before doing the paste and it makes no difference. I’ve tried it in my GP2015R2 and GP2016R2 and have the same results in both versions. Do you have any recommendations?

    Like

    Reply

    • Hi Rochelle,
      Just to be sure, are you able to type all of the information that you are copying, into the Transaction Entry window? Have you double checked that the company ID is valid and your user account has posting access? My code does not factor in the cursor position, but the column order you are copying does matter. Be sure that you are using my template for intercompany JEs and are not copying the header titles (just the data).

      Have you tried the non-intercompany version or the AP paste to see if those work?
      ~James

      Like

      Reply

      • Thank you James for responding. I am able to manually enter the same transaction in the Transaction Entry window. I am logged in as sa so I have posting rights.

        For both the General Journal and the Accounts Payable distributions if I do not select Intercompany, I can go Additional > Paste Entry and it will work. When I click the Intercompany box on Payables Distributions I receive the message: You must use a distribution type of PURCH, FNCHG, FREIGHT, MISC or UNIT when distributing to a destination company.

        Is there a different download for intercompany vs non intercompany? It appears to be working ok for non intercompany transactions.

        Like

      • For the Intercompany Accounts Payable data, are you using a distribution type other than 6, 5, 9, 8? Any other type is not supported in GP Intercompany transactions I believe.

        For the General Journal, silly question but are you making sure to copy the company ID as part of the entry? And can you make sure there are no extra spaces in the company ID field (i.e. from my template it should be “TEST” and not “TEST “).

        It is all one add-on so there is no separate download, just the one .dll.

        Like

      • I got it to work now. I downloaded your template and pasted it into a blank excel spreadsheet. I modified the account numbers to match ones in my system, and it worked! I went back and used entries I created in my original excel spreadsheet – same columns – and I get the error. There must be something corrupt with that original excel spreadsheet I created. Thank you for working with me to get it resolved!

        Like

      • Awesome! Not a problem. Let me know if there is anything you would like to see differently or something that might be handy for others and I can see what I can do.

        Like

  23. Hi James,
    We just tried this with GP version 2010 for a client that did not want to purchase Integration Manager. We got the following error: Add-in Initialization Error: GPPaste.dll: Could not load file or assembly ‘file:///C:\Program Files(x86)\Microsoft Dynamics\GP2010\Addins\GPPaste.dll’ or one of its dependencies. This assmbly is built by a runtime newer than the currently loaded runtime and cannot be loaded.
    Can you possibly assist in resolving this issue?
    Regards,
    Daron

    Like

    Reply

  24. Hi James,
    Thanks for all of your contribution to the GP community!!

    We tried the Copy/Paste functionality on GP 2010, however, it gave the following error:
    GPPaste.dll: Could not load file or assembly ‘file:///C:\Program Files(x86)\Microsoft Dynamics\GP2010\Addins\GPPaste.dll’ or one of its dependencies. This assembly is built by a runtime newer than the currently loaded runtime and cannot be loaded.
    Is it possible for you to recompile it for GP 2010?
    Regards,
    Daron

    Like

    Reply

      • Hi James,
        If you would like we can give you access to a GP 2010 environment (the old good GP 2010 Demo image provided by Microsoft). If that works, we can send you the details on how to login.
        That is the environment that we tested and failed.
        Regards,
        Daron

        Like

  25. Hi
    We have been using this wonderful tool for several years not, and we just did the Year End Update for 2020 and now it will no longer work for the intercompany. We are on GP2015. Has anyone reported this?

    Like

    Reply

    • Hi Brenda,
      Could you elaborate as to what you mean by it no longer works? Does the option no longer appear on the menu? Does it still appear on the regular journal entries?

      Like

      Reply

    • Hi!
      Have you attempted to run the DLL? What error are you receiving? I do not believe there should be limitations with running my add-on on future versions unless they ultimately change the underlying functions/code.

      Like

      Reply

  27. We were just upgraded to GP2018 and can’t use the Excel paste with Intercompany, so I dropped GPPaste into the Add-In folder, restarted, but aren’t seeing “Additional” show up on the menu bar. Don’t know how to turn off security on it, if that’s the issue.

    Like

    Reply

    • If Windows blocked the file, usually it will cause GP to crash on startup. This blog discusses methods to remove the block so see if that applies:
      https://dynamicsgpland.blogspot.com/2014/06/dynamics-gp-crashes-after-installing.html?m=1

      The Additional menu will only appear on the applicable windows (i.e. Transaction Entry), not everywhere. The only other thing I can think of is maybe you put it in an old GP installation add-in folder and not the current version as you did mention you upgraded?

      Like

      Reply

      • Unsure. It’s in Program Files (x86)\Microsoft Dynamics\GP2018\AddIns. Is it supposed to replace the Excel icon with Additional on the transaction menu bar, or does it get added?

        Like

      • It should add an “Additional” menu on Transaction Entry to the right similar to this:

        Unfortunately I do not have access to GP anymore so I cannot test on the new versions, I built these all on 2013 and folks have had success with 2015. Not sure if 2018 changed something.

        Like

      • Okay, we learned this morning that MS isn’t using Microsoft Dynamics GP2018 as the name of the app, but they’re going generic moving forward calling it just Microsoft Dynamics GP, and then the versions will be updated like from MS GP 18.3.1200 (2020) to 18.4.### or whatever. Consequently, dropping the paste file into the Addins under GP2018 doesn’t work because they’re using a different GP folder and AddIns folder. Path that now has this working again for us is: Program Files (x86) >Microsoft Dynamics>GP>Addins.

        And then “Additional” shows up exactly like your screen shot.

        Thanks for your help! We almost gave up.

        Like

  28. Hey this was a great resource for me to get an addin working so thank you.

    I am however having one problem… when I set Distribution Account Number to a value.. say 00-020-0003 for example I can pause after that field has been validated and see the correct code. However when I run the Window_ScrollScrollingWindow(window ‘{window}’ of form ‘{form}’, SCROLLTYPE_NEXT); it defaults the distribution account number back to 00-090-0100 or whatever we have set as our default. Do you have any idea what I could be doing wrong?

    Like

    Reply

Leave a comment