In Touch with Dynamics GP

This blog is intended to be a useful resource for Microsoft Dynamics GP users and professionals in the UK and the wider Microsoft Dynamics GP community as a whole.

Tuesday 13 April 2010

How to Use Microsoft Word mail merge to create a Dynamics GP import macro

Hi folks as promised in the previous article, here are the steps to use Microsoft's Word mail merge to create an import macro for Dynamics GP. The below will work for any repetative task in Dynamics GP.

Before starting this you will need an excel spread sheet with the values you wish to update. See below example of content.


Segmentid Number description

Segment1 100 Administrator

Segment2 100 Manager

Segment3 100 Tickets

Segment4 100 revenue



Open the window you wish to update. In this case Tools setup financial segment

Then go to Tools Macro Record

Give the macro a name e.g. Segment.

Save macro (the macro is now recording and all your key strokes will be recorded).

Enter one set of data you wish to update e.g. Segment ID , number and description.

Then select save.

Then go to Tools  macro  stop record

Go to where you saved the macro (.mac file) and right click and open Macro with word. You should have text similar to below example.

# DEXVERSION=10.0.193.0 2 2

ActivateWindow dictionary 'default' form 'GL_Segment_Maintenance' window 'GL_Segment_Maintenance'

MoveTo field 'Segment ID'

TypeTo field 'Segment ID' , '100'

MoveTo field Description

TypeTo field ' Description ' , 'Administration'

MoveTo field 'Save Button'

ClickHit field 'Save Button'

ActivateWindow dictionary 'default' form sheLL window sheLL



Now in the word document goto mailings and start the mail merge wizard.



Select directory as document type and got to next starting document



Select current document as starting document and go to next select recipients.



Use an existing list and the browse option to browse out to your spread sheet with the values you wish to update.



Do not go to the next stage of the wizard.



Highlight the value between the ‘’ on the type to line of the script. In the above example this would be the value 100, make sure you highlight just the value and not the’’ either side.



Now go to the insert merge field option from the tool bar, it should give you the column headers from your spread sheet (in this case we want number).



The field should now look like this '«Number»'

In the script.



Now repeat the process with the other type to fields in the script you wish to update e.g. description



TypeTo field Description , '«description»’



Now go next , next next in the wizard and save to a new document. You should end up with a script similar to below.



# DEXVERSION=10.0.193.0 2 2

ActivateWindow dictionary 'default' form 'GL_Segment_Maintenance' window 'GL_Segment_Maintenance'

MoveTo field 'Segment ID'

TypeTo field 'Segment ID' , '100'

MoveTo field Description

TypeTo field Description , 'Administrator’

MoveTo field 'Save Button'

ClickHit field 'Save Button'

ActivateWindow dictionary 'default' form sheLL window sheLL # DEXVERSION=10.0.193.0 2 2

ActivateWindow dictionary 'default' form 'GL_Segment_Maintenance' window 'GL_Segment_Maintenance'

MoveTo field 'Segment ID'

TypeTo field 'Segment ID' , '100'

MoveTo field Description

TypeTo field Description , 'Manager’

MoveTo field 'Save Button'

ClickHit field 'Save Button'

ActivateWindow dictionary 'default' form sheLL window sheLL # DEXVERSION=10.0.193.0 2 2

ActivateWindow dictionary 'default' form 'GL_Segment_Maintenance' window 'GL_Segment_Maintenance'

MoveTo field 'Segment ID'

TypeTo field 'Segment ID' , '100'

MoveTo field Description

TypeTo field Description , 'Tickets’

MoveTo field 'Save Button'

ClickHit field 'Save Button'

ActivateWindow dictionary 'default' form sheLL window sheLL # DEXVERSION=10.0.193.0 2 2

ActivateWindow dictionary 'default' form 'GL_Segment_Maintenance' window 'GL_Segment_Maintenance'

MoveTo field 'Segment ID'

TypeTo field 'Segment ID' , '100'

MoveTo field Description

TypeTo field Description , 'revenue’

MoveTo field 'Save Button'

ClickHit field 'Save Button'

ActivateWindow dictionary 'default' form sheLL window sheLL



Before this script will work we need to remove the repeated line DEXVERSION=10.0.193.0 2 2

This should only appear once at the start of the script so the script now looks like the below.



# DEXVERSION=10.0.193.0 2 2

ActivateWindow dictionary 'default' form 'GL_Segment_Maintenance' window 'GL_Segment_Maintenance'

MoveTo field 'Segment ID'

TypeTo field 'Segment ID' , '100'

MoveTo field Description

TypeTo field Description , 'Administrator’

MoveTo field 'Save Button'

ClickHit field 'Save Button'

ActivateWindow dictionary 'default' form sheLL window sheLL #

ActivateWindow dictionary 'default' form 'GL_Segment_Maintenance' window 'GL_Segment_Maintenance'

MoveTo field 'Segment ID'

TypeTo field 'Segment ID' , '100'

MoveTo field Description

TypeTo field Description , 'Manager’

MoveTo field 'Save Button'

ClickHit field 'Save Button'

ActivateWindow dictionary 'default' form sheLL window sheLL #

ActivateWindow dictionary 'default' form 'GL_Segment_Maintenance' window 'GL_Segment_Maintenance'

MoveTo field 'Segment ID'

TypeTo field 'Segment ID' , '100'

MoveTo field Description

TypeTo field Description , 'Tickets’

MoveTo field 'Save Button'

ClickHit field 'Save Button'

ActivateWindow dictionary 'default' form sheLL window sheLL #

ActivateWindow dictionary 'default' form 'GL_Segment_Maintenance' window 'GL_Segment_Maintenance'

MoveTo field 'Segment ID'

TypeTo field 'Segment ID' , '100'

MoveTo field Description

TypeTo field Description , 'revenue’

MoveTo field 'Save Button'

ClickHit field 'Save Button'

ActivateWindow dictionary 'default' form sheLL window sheLL



Now save this document as a .mac file



Go into dynamics, Open the window you wish to update. In this case Tools setup financial segment



Then go to Tools Macro Play and select the .mac file you just created.

The macro should now run and update the data for you

4 comments:

  1. This comment has been removed by a blog administrator.

    ReplyDelete
  2. Hi,

    This is such an wonderful article for all GP consultants and end users.

    Thanks,
    Prakash

    ReplyDelete
  3. nice share
    thanks for information

    ReplyDelete
  4. Helpful artical.
    Thanks for a well written and informative post.
    How to Use Mail Merge With Word & Excel

    ReplyDelete