How to use Word Mail Merge and Macros to Import Data
This post explains how to use Microsoft Office Word's Mail Merge functionality to Import Data into Microsoft Dynamics GP. This method can be useful when working with master records or transactions not supported by any of the existing tools such as Integration Manager, eConnect or Web Services.
When attempting to import data into Microsoft Dynamics GP for a master record or transaction not supported by the usual tools, the usual option left is to perform a direct table import via the built-in Import Utility or using SQL Server tools such as Data Transformation Services (DTS) and Bulk Copy (BCP). The problem with a direct table import is that it bypasses all the application business logic and that the resulting data created could not be 100% correct. This could then cause issues later when using the application with the imported data.
The method described in this article uses Word's Mail Merge functionality to combine a recorded macro with a data set to create a new Macro file with will then enter the required data as though it was being entered by the user.
Note: This method works well when the entry of each record is identical. If additional dialogs are displayed or different actions are required to import the transactions, the macro would not handle this. It would be possible to use Word's IF conditional field functionality to make a macro which could handle these more complex situations, however that is beyond the scope of this article.
To demonstrate the method, this article will show how to import Sales Prospects into the Sales Prospect Maintenance window. Below are the contents of the comma delimited source data text file:
Prospect ID, Customer Name, Contact Person, Address 1, Address 2, Address 3, City, State, Zip
PROSPECT 1, Name 1, Person 1, Address Line 1, Address Line 2, Address Line 3, City 1, State 1, 00001
PROSPECT 2, Name 2, Person 2, Address Line 1, Address Line 2, Address Line 3, City 2, State 2, 00002
The steps to import this data into Sales Prospect Maintenance are shown below:
STAGE A: Create Template Macro
Launch Microsoft Dynamics GP and login. You can use a Test company or the lesson company for this stage.
Click Cards, click Sales and click Prospects to open the Sales Prospect Maintenance Window.
Click on a field other than the primary key field (Prospect ID), for example, click on the Name field to take the focus away from the Prospect ID field. This will ensure that the macro records the focus changing to the primary key field.
Click Tools, click Macro, click Record and enter a macro file name to start recording the template macro.
Click on the primary key field, for example Prospect ID and enter a dummy Prospect ID then press Tab. Use the name of the field where possible, PROSPECT ID.
Enter dummy data into each of the fields that will be imported into and press Tab. Where possible enter the name of the field into the field as this will make the creation of the mail merge document easier. For example: Name, Contact, Address, City, State, Zip.
When all the fields have been populated with dummy data, click Save.
Click Tools, click Macro, click Stop Record.
Optional: To clean up, please select the just created record and delete it, it was only needed to create the macro file.
Optional: Open the created Macro file with Notepad.exe. It should look similar to the following example:
# DEXVERSION=XX.XX.XXXX.0 2 2
CheckActiveWin dictionary 'default' form 'SOP_Prospect_Maintenance' window 'SOP_Prospect_Maintenance'
NewActiveWin dictionary 'default' form 'SOP_Prospect_Maintenance' window 'SOP_Prospect_Maintenance'
ActivateWindow dictionary 'default' form 'SOP_Prospect_Maintenance' window 'SOP_Prospect_Maintenance'
MoveTo field 'Prospect ID'
TypeTo field 'Prospect ID' , 'PROSPECT ID'
MoveTo field 'Customer Name'
TypeTo field 'Customer Name' , 'Name'
MoveTo field 'Contact Person'
TypeTo field 'Contact Person' , 'Contact'
MoveTo field 'Address 1'
TypeTo field 'Address 1' , 'Address Line 1'
MoveTo field 'Address 2'
TypeTo field 'Address 2' , 'Address Line 2'
MoveTo field 'Address 3'
TypeTo field 'Address 3' , 'Address Line 3'
MoveTo field City
TypeTo field City , 'City'
MoveTo field State
TypeTo field State , 'State'
MoveTo field Zip
TypeTo field Zip , 'Zip'
MoveTo field 'Save Button'
ClickHit field 'Save Button'
STAGE B: Create Word Mail Merge Document (based on Word 2007)
Launch Microsoft Office Word.
Click on the Office Button and click Open.
Select the document type drop down list as All Files (*.*).
Locate the Macro file created in the previous stage and click Open.
Find the Macro command "CheckActiveWin" at the top of the list of Macro commands and change it to "ActivateWindow". This will ensure that the window is selected at the beginning of the data entry cycle.
Click on the Mailings tab in the Office ribbon.
Click Start Mail Merge and click Step by Step Mail Merge Wizard.
Select the document type as Letters and click Next.
Select the starting document as Use the current document and click Next.
Select recipients as Use an existing list and click Browse and locate your data file.
Check that Word has read your data file correctly on the Mail Merge Recipients window, click OK.
Click Next to get to the Write your letter stage.
Look for the TypeTo Macro commands and replace the text between the single quotes with merge fields. Use Insert Merge Field from the Office Ribbon.
The resulting document should look like the following example:
# DEXVERSION=XX.XX.XXXX.0 2 2
ActivateWindow dictionary 'default' form 'SOP_Prospect_Maintenance' window 'SOP_Prospect_Maintenance'
NewActiveWin dictionary 'default' form 'SOP_Prospect_Maintenance' window 'SOP_Prospect_Maintenance'
ActivateWindow dictionary 'default' form 'SOP_Prospect_Maintenance' window 'SOP_Prospect_Maintenance'
MoveTo field 'Prospect ID'
TypeTo field 'Prospect ID' , '«Prospect_ID»'
MoveTo field 'Customer Name'
TypeTo field 'Customer Name' , '«Customer_Name»'
MoveTo field 'Contact Person'
TypeTo field 'Contact Person' , '«Contact_Person»'
MoveTo field 'Address 1'
TypeTo field 'Address 1' , '«Address_1»'
MoveTo field 'Address 2'
TypeTo field 'Address 2' , '«Address_2»'
MoveTo field 'Address 3'
TypeTo field 'Address 3' , '«Address_3»'
MoveTo field City
TypeTo field City , '«City»'
MoveTo field State
TypeTo field State , '«State»'
MoveTo field Zip
TypeTo field Zip , '«Zip»'
MoveTo field 'Save Button'
ClickHit field 'Save Button'Click Next on the Mail Merge Wizard to preview the data.
Click Next on the Mail Merge Wizard to Complete the merge.
Click Edit individual Letters ... to merge to a new document.
Click the Office Button and click Save As.
Select the Save as Type as Plain Text (*.txt).
Enter the output file name and click save.
On the File Conversion window, click OK to accept the default settings.
Close Output document's window in Word.
Select the Mail Merge Document based on the initial macro file.
Click the Office Button and click Save As.
Select the Save as Type as Word Document (*.docx) or Word 97-2003 Document (*.doc)
Adjust the name if desired and click Save.
Exit Word.
STAGE C: Running the Macro to Import the data
Using File Explorer, Locate the Output text file from the previous stage.
Rename the extension from *.txt to *.mac.
Note: You might need to uncheck the "Hide extensions for known file types" folder option to be able to rename the extension.
Return to Microsoft Dynamics GP, if necessary change the company logged into.
Click Cards, click Sales and click Prospects to open the Sales Prospect Maintenance Window.
Click Tools, click Macro, click Play and enter now renamed output macro file name.
The Macro will run and display a dialog when completed saying how long it took to run.
Optional: Use the Lookup or Navigation buttons to view the newly imported records.
Please consult the online help documentation for Word if you have any questions about how the Mail Merge functionality works.
This is a technique that can be used when there is no eConnect or Integration Manager alternatives.
David
Ref: Internal KB 953437