Microsoft Dynamics GP Macros: Macro By Excel Formula

In this series I am taking a look at how macros can be used to update data in Microsoft Dynamics GP.

Over the last couple of posts, I’ve shown how data can be merged into the macro template using either mail merge or a SQL script. This post is going to show the final method I use for merging data into the macro template, which is to use an Excel formula.

I am using an Excel spreadsheet with two columns, USERID and PASSWORD. I am going to add a third column to the spreadsheet to hold the formula containing the macro.

Due to the length of the macro, I can’t just add the macro to the formula as is (Excel only allows strings of 255 characters in a formula, so instead I will use the CONCATENATE function and break the macro down into smaller chunks.

In the formula, I have added the cells into the formula to replace the User ID, Password and Confirm Password variables:

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (https://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 2.0 UK: England & Wales (CC BY-NC-SA 2.0 UK).
*/
=CONCATENATE("# DEXVERSION=16.00.0034.000 2 2
CheckActiveWin dictionary 'default'  form 'Enter_User_Names' window 'Enter_User_Names' 
  TypeTo field 'User ID' , '",TRIM(A2),"'","
  MoveTo field 'User Name' 
  MoveTo field Password 
  TypeTo field Password , '",B2,"'","
  MoveTo field '(L) Confirm Password' 
  TypeTo field '(L) Confirm Password' , '",B2,"'
  CommandExec dictionary 'default'  form 'Enter_User_Names' command 'Save Button_w_Enter_User_Names_f_Enter_User_Names' ","
NewActiveWin dictionary 'default'  form 'Enter_User_Names' window 'Enter_User_Names' 
ActivateWindow dictionary 'default'  form 'Enter_User_Names' window 'Enter_User_Names' ")

When dragged down through all rows containing data, a valid macro is produced for each line:

The resulting macro can be copied and pasted into a Notepad file, but each row will be wrapped with double quotes; to fix this problem, which would prevent the macro running, before saving the file, perform a global find and replace for ” and replace with nothing:

The macro file can now be saved and run.

As with macros produced via SQL script, the Excel formula can have conditions built in; this can allow the macro to handle variations in the steps taken.

I have used this method recently when doing some work with a client to update Extended Pricing in the inventory module. The price lists being updated had a variable number of lines on and the macro had to deal with this as well as catering for for a variable number of price breaks within the lines.

This resulted in a fairly complex formula, but worked well and allowed the client to make the required updates. And will be used again going forward whenever prices need to be updated.