#GPPT Using the Clipboard to copy from Excel into Microsoft Dynamics GP

Today, we are covering an exciting new sample customization that is now possible due to Clipboard and Parsing Data Helper Functions added to recently released Build 29 of GP Power Tools.

The customization shows how any data in a Microsoft Excel spreadsheet can be copied via the Clipboard into any window of Microsoft Dynamics GP.

The specific examples in this project will add lines to an existing transaction in the Item Transaction Entry window or create new transactions in the same window. The project contains two triggers, one for copying lines and one for copying entire transactions.

The triggers add menu items to the window using the Additional menu. The trigger scripts copy the contents of the clipboard to a text field which can then be parsed as a tab delimited data set. The first row is parsed to check that the column headers match the expected data set.

Then each subsequent row is parsed and the data entered into the window as though it is being typed by the user using the Dexterity map command.

[Ed] The map command is undocumented and was added to Dexterity to facilitate the Service Based Architecture (SBA) web services that “drive” the user interface of a window. Using the standard “set a field and run script” method often fails if there is a pre script or if the change script uses old() or diff() functions. The issue is the control of the current field in focus (see links below). The map command simulates moving the focus into a field and then changing the value of the field. It does not simulate tabbing off the field as this is handled by the next map command. However, to force the final field change script to execute, you will need to use the Window_PullFocus() command to tab off the final field. A final point to make about the map command is that you must pass in a variable of the same type as the field you are mapping to, as the command cannot evaluate an expression or handle data conversions.

The trigger for entering entire transactions uses a sequence number to define when a new transaction starts. It can also automatically create the Batch record if it does not exist.

This is just an example and the same techniques can be applied to copy data from Excel into any window in Microsoft Dynamics GP. Note it does required Build 29 of GP Power Tools.

Note: The archive file contains the exported project and a sample Excel spreadsheet that was used to test with the Fabrikam sample company. This sample project does not support Serial Numbers, Lot Numbers or use of Bins, but that functionality could be added fairly easily.

Downloading and Installing

Using Build 29 or later, download the example code, import using the Project Setup window:

• GP Power Tools Settings Clipboard Trx.dbg.zip

The code will be active on next login or after switching companies, or you can use start the triggers manually from the Project Setup window.

More Information

Here are some links to talk about driving the User interface and handling old() and diff():

• #MSDynGP Development: Driving the User Interface of Microsoft Dynamics GP using Code
• #GPPT Best Practices for Programmatically Updating Fields on Windows
• Unhandled Script Exception: Attempt to use old()/diff() from a field that does not have focus
• Quick Tip: Handling scripts that use old() when using triggers to set default values

For more information see:

• GP Power Tools Portal: http://winthropdc.com/GPPT
• GP Power Tools Samples: http://winthropdc.com/GPPT/Samples
• GP Power Tools Videos: http://winthropdc.com/GPPT/Videos

Enjoy

David

20-Aug-2022: Updated with more information on the map command and driving the UI.

This article was originally posted on http://www.winthropdc.com/blog.