Importing Data into Microsoft Dynamics GP Using a Macro Mail Merge
April 15 |
This articles demonstrates how to importing data into Microsoft Dynamics GP utilizing a macro mail merge. Macros with a Microsoft Word Mail Merge allows you to record the process of data entry, then merge into the fields from the columns in an Excel file. When you run the Macro it will repeat the data entry steps previously recorded, filling in the data from the Mail Merge.
One example we have used this for in the past was for setting up Payment Terms. The customer had over 50 Terms they needed to setup. A Macro was used to import this data.
To begin the Macro, start at the menu. Microsoft Dynamics GP >> Tools>> Macro>>Record
Enter the Path and Filename to save the recorded Macro.
Then choose Microsoft Dynamics GP >> Tools>>Setup>>Company>>Payment Terms
- Enter 1 Payment term, selecting a Due type and entering date or days.
- Enter the Discount Type and Date or Days
- Enter the Discount Type and Amount or Percent
- Mark the checkbox in Calculate Discount on for Sale/Purchase.
- Click Save
- Microsoft Dynamics GP >> Tools>> Macro>>Stop
You can review the Macro by opening the file with Notepad.
When you create the source file, you will need a column for each field you will be importing.
I have listed the value for each field.
Open the .mac file with Microsoft Office Word, go to the Mailings tab, select Start Mail Merge and from the drop down menu select the Step-by-Step Mail Merge Wizard.
- Click Next: Starting document
- Choose use the Current document and click Next: Select recipients.
- After Browsing and selecting your file, use Insert Merge Field to map to the Excel columns.
- Use an existing list
- Browse out to .csv source file
- Insert Merge Fields
- For every ClickHit field replace the data in the field from the Merge and replace it with the Insert Merge Field from the drop down.
- Choose Step 3 Next: Write your letter
- Choose Step 4 Next: Preview your letters
- Choose Step 5 Next: Complete the merge
- Edit Individual Letters, choose all. Save as a .txt file
- Copy data in Text Document and Paste into the original MAC file in place of the recorded .MAC file.
- Play
- Select file .MAC file >
- If you have different checkboxes being used, the macro for those will need to be modified.
When you Click on the field it marks the box, you can’t specify ‘True or False’.
- Also if some discounts are amounts and some are percentages, split in two files and map either <<Discount_Percent_Amount>> or <<Discount_Dollar_Amount>>. Or modify individual pages after merge.
Please contact us (support@isolutionspartners.com) if you need assistance creating a Macro, or using the Word Mail Merge for this or any other entry process when trying to add or modify several records from a source file.
