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.