Export the Dynamics GP Payables or Receivables Historical Aged Trial Balance to Excel

This is a modified Dynamics GP report (see download) I’ve provided to my clients for years that can be exported to a text file and imported into excel that includes the customer ID, name, and the aging columns. Through GP security, you can only have one modified report for each user and company if you’re running a shared modified reports dictionary for each workstation. What this means is if you already have an existing modified historical aged trial balance detail report you may have to provide a separate login or shortcut to this report to have access to both. Here’s a screenshot of the report in excel.

You have to add totals and grand total line, be sure to exclude the “Original Amount” column.

Payables:

Download report

 

Receivables:

Download report

 

After you download the package(s) from above by right-clicking and choose ‘save as’, you can import it in version 10, 2010, 2013 by going to Microsoft Dynamics GP>>tools>>customize>>customization maintenance and choose import. Before doing so, be sure to back up your modified reports to ensure nothing is overwritten.

If you need the summary trial balance export packages just create a comment below and I will email them to you.

This entry was posted in Dynamics GP. Bookmark the permalink.

107 Responses to Export the Dynamics GP Payables or Receivables Historical Aged Trial Balance to Excel

  1. Rebecca Walker says:

    Hi Steve,

    I have tried to import the file as explained, but am getting an error stating “unable to open customizations dictionary”. Do you have any thoughts on how to correct this as I need to pull the historical TB for AR and AP into excel and your package seems to be the most efficient I have found.

  2. Steve says:

    You can’t have other users in the reports dictionary you are importing to. This means if you are sharing a single reports dictionary across all users everyone has to be out of GP during the import process, that’s usually why that error message comes occurs.

  3. Rebecca Walker says:

    Thanks. I was able to import after your assistance, however, my report doesn’t show as yours does. I get a messy report, and assume it is a formatting issue.

  4. Rebecca Walker says:

    How do I get this to display as you have above? I get a bunch of lines that do not format as your example above.

    As it stands, I have to go in and reorganize the entire excel spreadsheet and this will take many hours of time. My apology, but this has been the most difficult thing ever.

  5. Steve Reese says:

    Rebecca,
    Assuming you imported the package correctly and set security, you should not have to do any formatting – the data should look just like the screenshot in my post. My guess is you’re choosing the wrong format (delimiter) when exporting to GP or when importing from excel. Here are some things to try – when exporting from GP on the report destination window the file format should be tab or comma delimited, when importing to excel be sure to use the same delimiter. Open excel first, then choose the file to start the import wizard. I prefer tab-delimited.

    Steve

  6. Leyna says:

    Hi Steve,

    Thank you very much for this information! I tried to download the AR aged balance package by clicking on the download button. However, when clicking on it, it does appear some sort of script on screen letting me to save it only in txt and html format”
    Would you have any tips to download this package properly” Thank you very much for your information!

  7. Mario Fiamenghi says:

    HI Steve

    Thanks for providing the reports. I have created the summary payable and receivable my self. Please let me know if you want a copy to share.

    Kindly

Leave a Reply

Your email address will not be published. Required fields are marked *