Month: November 2017

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

Despite the limitations of GP macros, they are a very good way to either insert or update data in Microsoft Dynamics GP. Using the mail merge method does not require a user to be especially technical and the Excel formula method is actually quite an easy approach for many Finance users as they are usdually very capable in Microsoft Excel.

I, personally, favour the SQL method a lot of the time, due to the ease and flexibility afforded by T-SQL. I’m also fairly good at writing SQL so can get code written quickly.

However, if I am working on macros either alongside or for clients to run, I will always use the Excel formula method, again because of the flexibility possible in the formula, but mainly for the ease of use for the client.

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.

Continue reading “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.

In the last post in this series, I showed how to merge the data into the macro template; another approach I have used a few times is to write a SQL query which returns the data already in the macro. This is done by embedding the macro file into the SQL Query:

/*
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).
*/
SELECT '# DEXVERSION=16.00.0034.000 2 2
CheckActiveWin dictionary ''default''  form ''Enter_User_Names'' window ''Enter_User_Names'' 
  TypeTo field ''User ID'' , ''' + RTRIM(USERID) +'''
  MoveTo field ''User Name'' 
  MoveTo field Password 
  TypeTo field Password , ''' + LEFT(RTRIM(REPLACE(USERNAME,' ','')),14) + '1' + '''
  MoveTo field ''(L) Confirm Password'' 
  TypeTo field ''(L) Confirm Password'' , ''' + LEFT(RTRIM(REPLACE(USERNAME,' ','')),14) + '1' + '''
  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'''
FROM
	DYNAMICS..SY01400
WHERE
	USERID <> 'sa'
AND
	USERID <> 'DYNSA'
AND
	USERID NOT LIKE 'LESSON%'

Continue reading “Microsoft Dynamics GP Macros: Macro By SQL”

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

The most common way I update a macro template with the data to be updated, is to use Mail Merge in Microsoft Word.

Make sure you have your data prepared: in the demo system I’m using, I have only four users. I have created some new passwords based on the User Name field and stored them in an Excel spreadsheet:

Continue reading “Microsoft Dynamics GP Macros: Macro By MailMerge”

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

As I said in the last post, which covered the playing of a macro, in this post I am going to show how the macro can be played quicker than using the standard Play option.

Credit for this one goes to MVP Leslie Vail, who blogged about it back in 2014. There is a Dex.ini setting which needs to be set to make this function available:

ShowAdvancedMacroMenu=TRUE

This needs to be configured on any client machine which will be used to run macros. If you are a user of the GP Power Tools you can use the Dex.ini Configuration to roll out this switch to all clients:

Continue reading “Microsoft Dynamics GP Macros: Playing A Macro Quickly”