tracking code
LBMC Technology Solutions
  • Home
  • Solutions
    • Dynamics 365
    • Dynamics NAV
    • Dynamics GP
    • Dynamics CRM
    • Sage Intacct
    • Document Management (ECM)
    • Network Engineering and IT Solutions >
      • IT Security
      • Managed IT
      • Business Phone Systems
  • Events
    • All Upcoming Events
    • Training / CPE
    • Webinars and Workshops
  • Resources
    • Blog
    • Forum
    • On-Demand Webinars
    • Referral Program
    • Services We Offer
    • Support
  • About Us
    • Careers
    • Partner with Us
    • Community
    • Contact Us

Using Excel to Automate Dynamics GP Data Entry

8/15/2019

0 Comments

 
Posted by Paul Haag

Although Dynamics GP has a fairly robust application programming interface (API) for importing or updating data, there are certainly times when a specific window is not covered, especially in a 3rd party product. It can seem like the only solution is to manually click through a long list of changes (or hire a temp worker to do it). 

Fortunately, Dynamics GP also has an engine for recording and then repeating repetitive tasks called Macros. Even more fortunately, the language of the macro is essentially plain text, so it’s easy to record a macro and then replace the bits you need to perform an action at scale. Here’s how. 

​Although this is an area that’s well covered by APIs, as a specific example, let’s look at mass-changing customer classes. 

Get Your Update Data in Excel 
You need to have the values you want to change something to listed in Excel. For our customer class example, we can export the list of customers we want to modify, using the Customers SmartList. 

  1. In GP, go to Microsoft Dynamics GP > SmartList 
  2. Expand Sales and click on Customers. 
  3. Optionally, you can use the Search functionality in this window to narrow down the list to just the set of customers you want to update. 
  4. Once you’re satisfied with the list, click the Excel button in the header to generate an Excel spreadsheet. ​
​​Record the Macro 
When recording the steps you want to perform, it is critical that the window is reset at the end of your action to exactly the same way it was when you start your action. This is because macros run exactly the same steps repeatedly, so if the window was empty at the beginning, but open to a specific record at the end, the macro, for example, won’t be able to enter the customer number again because the field is locked. 

Also, make sure that you manually type in any information you want to vary in Excel when recording the macro (e.g. don’t use lookups). The reason for this is that otherwise the macro won’t record the step to enter the value explicitly and you won’t be able to replace it in the next step. 

  1. Navigate to the window where the data entry should occur (i.e. Cards > Sales > Customer). 
  2. Start recording the macro by clicking Tools > Macro > Record… 
    1. In the dialog that opens, choose a place to save your macro file. 
  3. Record the steps necessary to change the data. 
    1. Type a customer number into the Customer ID field. 
    2. Click the lookup to select the class ID we want to change everyone to (note: this assumes the class should be the same. If you want the class to be different for different customers, type the class ID in instead). 
    3. Click yes when prompted to roll down the class changes. 
    4. Click the Save button. 
    5. Click the Clear button (just to make sure we’re definitely back to the same state as when we got to step 2) 
  4. Stop recording the macro by clicking Tools > Macro > Stop Record 
Use Excel to Generate the Full Macro 
Now that we have the data we want to use and the steps necessary to do it for a single record, we need to use Excel to generate the steps necessary to do it for every record. 

  1. Open the macro file 
    1. Navigate to the saved location 
    2. Right-click the file and choose Open with 
    3. In the dialog that opens, select Notepad or another text editor 
  2. Copy and paste the contents of the macro file (skipping the top line that reads # DEXVERSION) into a cell in your spreadsheet (make sure it’s in a single cell). The content of my macro file looks like this, with the part to replace highlighted: ​​
CheckActiveWin dictionary 'default'  form 'RM_Customer_Maintenance' window 'RM_Customer_Maintenance'  
  TypeTo field 'Customer Number' , 'AARONFIT0001' 
  MoveTo field Hold  # 'FALSE' 
  MoveTo field 'Lookup Button 2'  
  ClickHit field 'Lookup Button 2'  
NewActiveWin dictionary 'SmartList'  form 'Customer_Class_Lookup' window 'Class_Lookup'  
# Key 1:  
  MoveTo line 2 browsewin 'Class_Lookup_Scroll'  
# Do you want to update the customer with information from class USA-MNWI-T4? 
NewActiveWin dictionary 'default'  form DiaLog window DiaLog  
  ClickHit field OK  
NewActiveWin dictionary 'SmartList'  form 'Customer_Class_Lookup' window 'Class_Lookup'  
NewActiveWin dictionary 'default'  form 'RM_Customer_Maintenance' window 'RM_Customer_Maintenance'  
ActivateWindow dictionary 'default'  form 'RM_Customer_Maintenance' window 'RM_Customer_Maintenance'  
  CommandExec dictionary 'default'  form 'RM_Customer_Maintenance' command 'Save Button_w_RM_Customer_Maintenance_f_RM_Customer_Maintenance'  
  CommandExec dictionary 'default'  form 'RM_Customer_Maintenance' command 'Clear Button_w_RM_Customer_Maintenance_f_RM_Customer_Maintenance'

          3.  Use the Substitute function to replace the highlighted bit with a value from your table. In the example below, I have pasted my macro body                   into cell F2. The function I am adding in column D reads: =SUBSTITUTE($F$2,"AARONFIT0001",A2) This means, take the text from F2 and                         replace any instance of AARONFIT0001 with the customer number specified in column A.​
Picture
           4.   Replicate the formula for all rows in the data set. If your data set includes the row that you used to record the macro, exclude it. Because                        macros assume everything will run exactly the same way, if a record has already been modified, GP will behave differently and the macro                      will break. 
            5.   Copy all of column D and overwrite the contents of your original macro file. 
            6.   
Use the “search and replace” functionality to replace all quotes (") with nothing (when you copy and paste from Excel, it adds extra quotes                     around the value for each cell, which is invalid for the macro):
Picture
Run the Macro 
Now that we have built the full macro, all we have left to do is run it. That said, if, for whatever reason, one of the records behaves differently than the macro you recorded, the macro will stop. You will need to fix that problem and then remove all of the already-run steps from the macro file and run it again. 
  1. Open the same window from which the macro was recorded. 
  2. ​Run the macro from Tools > Macro > Play… 
    1. In the dialog that opens, select the modified macro file. 
  3. As discussed above, work through any errors and repeat. 
Especially working through the errors can be somewhat tedious, but it’s still a lot better than typing and clicking your way through everything! 
0 Comments

Your comment will be posted after it is approved.


Leave a Reply.


    Blog

    ​Your one stop shop for technology.

    Categories

    All
    Business Central
    Dynamics 365
    Dynamics CRM
    Dynamics GP
    Dynamics NAV
    Events
    Microsoft Dynamics Add-ons
    Software Decisions
    Tutorial

Solutions
Microsoft Dynamics 365
    Business Central
    Customer Engagement
Microsoft Dynamics NAV
Microsoft Dynamics GP
​Microsoft Dynamics CRM
​Sage Intacct
​
Network Engineering

​ERP Comparison Guide
Learning Opportunities
All Upcoming Events
User Conferences
Training / CPE
Vision
Webinars & Workshops
​

Resources
Blog
Forum
Referral Program
​​YouTube Channel

Company
About Us
Careers
Contact Us
​
Locations
Charlotte
Knoxville
Nashville
Picture
Picture
Proudly powered by Weebly

Sitemap | Home | Careers | Contact Us

share

Copyright © 2019

LBMC Technology Solutions