There are a number of ways to import data into Dynamics GP. Most commonly known are Integration Manager and eConnect. Another, less-known method, is the ability to upload data by creating a macro. For a day-to-day GP user, without Integration Manager, a macro can often be a good option. Below are the few simple steps needed to create a Dynamics GP macro. In this example, we will be updating shipping method for a group of vendors that are not in the same vendor class.
The first thing we’ll want to do is create a source file in Excel format. At minimum, we will need two columns: “Vendor ID” and “Shipping Method.”
Then we will record a macro. A Dynamics GP macro can be initiated from virtually any GP window. From within the desired window, select: Tools > Macro > Record (or press Alt +F8.)
You will be prompted to choose the location where you’d like to save the macro. Choose the desktop or any other location that you can easily access. As soon as you click “Save,” the macro will begin recording.
For our example, we will be in the “Vendor Maintenance” card. We will enter a Vendor ID and use the mouse to select the “Shipping Method” field. Enter the desired Shipping Method and click “Save.”
Stop recording the Dynamics GP macro, by navigating to: Tools > Macro > Stop Record (or press Alt + F8.) Navigate to the location where the macro was saved, right-click, and choose to open with Word. (If Word is not an option, you can also start by opening word, choose to “Open” a file, look for all file types, and choose the macro.)
From within Word, click on the “Mailings” tab and choose “Start Mail Merge” and “Step by Step Mail Merge Wizard.” Select “Next: Starting Document”
Choose “Select Recipients” and “Browse”
Select the Excel spreadsheet with the data we will import
Validate the tab in the spreadsheet with the data and make sure the checkbox for “First row of data contains column headers” is marked.
The data from the Excel file should appear. Click OK.
Click “Insert Merge Field” button and override the “Vendor ID” and “Shipping Method.” Make sure not to override the quotation marks. It should look like this:
Click “Finish and Merge” and “Edit Individual Letters.” Select “All” and click “OK.”
Merge “All” and click “OK”
Save the document as a “plain text” document and click “OK” when “File Conversion” window appears
Locate the text file you just saved. You will want to see the .txt extension at the end of the file. (If you don’t see it, click the “Start” button on your computer, click “Documents” and “Organize.” Click on “Folder and search options,” click on “View” tab, and unmark the box for “Hide extensions for known file types.”) Right-click on the text file and choose “Rename.” Change the extension from .txt to .mac
Open the vendor card, click Alt + F8, and choose the macro to play. Let the macro play to completion. Do NOT do anything else on your computer while the macro is running or it will interrupt the process.
This article was written by Zandra Montes, Dynamics GP Application Consultant for Tridea Partners. Tridea is a leading Microsoft Dynamics GP provider.