Dynamics GP Macros to Import Data without Integration Manager

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.” Source File in Excel
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.)
Dynamics GP Macros to Import Data 
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.
Location to Save Macro  
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.”
Vendor Maintenance Card
 
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.)  Macro in Word
From within Word, click on the “Mailings” tab and choose “Start Mail Merge” and “Step by Step Mail Merge Wizard.” Step By Step Mail Merge Wizard  Select “Next: Starting Document”

 Macro in Word

Choose “Select Recipients” and “Browse”         

 Macro Data Import

Mail Merge- Browse

Select the Excel spreadsheet with the data we will import

 Select Excel Spreedsheet with the data

Validate the tab in the spreadsheet with the data and make sure the checkbox for “First row of data contains column headers” is marked.

Validate the Spreadsheet

 The data from the Excel file should appear. Click OK.

Dynamics GP Macros to Import Data

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:

 Insert Merge Fields

Click “Finish and Merge” and “Edit Individual Letters.” Select “All” and click “OK.”

Finish and Merge

 Merge “All” and click “OK”

Merge All

 Save the document as a “plain text” document and click “OK” when “File Conversion” window appears

 File Conversion

File Conversion

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

Change extention 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.

2 thoughts on “

Dynamics GP Macros to Import Data without Integration Manager

  1. After I do all these step and “save as” plain text the file goes onto my desktop but the icon does not look like the one above. And when I go to “play” the macro in GP the file does not pop up when I browse for it. What am I doing wrong?

Leave a Reply

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