Replicator - A new way to create GP Macros

Adding/Updating/Deleting multiple records in GP

October 20, 2020 - Replicator has been updated!

A Map Columns button has been added to the new version of Replicator. It makes mapping your {data columns} in the macro SUPER easy! It also simplifies the preparation of your macro for Scrolling Windows (Sales Orders, Purchase Orders...) See the version history below to see all of the changes in the new version. Enjoy!

Map Columns Button demo animation

What is Replicator and why?

Combined with Word's Mail Merge capibilities, macros are a great way to make mass updates in GP. This method for years with much success, but I have been frustrated by this process many times:

  1. I can never remember... - When I start the mail merge, I can never remember which type to choose; Letters, Envelopes... Directory
  2. Can't find the correct source data - Sometimes when I "Select Recipients", I have a hard time figuring out how to choose the correct data range from Excel.
  3. Wrong quotes - When editing macro in word, the smart quotes mess up the macro. You want to type a ' but Word gives you ‘ or ’. GP only works with the ' and bombs with the other two.
  4. Locked files - On many occasions, I'll want to edit the data in the Excel file, but I can't because Word is still open and has the Excel file locked.
  5. Header lines vs Repeating Lines - There are some lines in the macro that only need to be at the top of the file (#DEX... or an open window command for example). You have to remember to take them out of the macro so they are not repeated multiple times and then remember to add it back when you are done.
  6. File Save As / Renaming - After you run the mail merge, you have to save the results into a text file with a ".mac" file extension. Again, another simple step, but you have to remember the details.
  7. I can't remember... - This sounds familiar, but I'm not sure... There are many steps in multiple programs. Keeping them straight can be confusing.

If you haven't done much with mail merge and macros, this all may seem a bit odd and you may be saying to yourself, "What's the deal... what is he complaining about?" But if you have used mail merge and macros, hopefully (and unfortunatly) you can releate to what I am saying. Instead of complaining about it, I thought "Hey, I'm a Software Engineer... I can fix this and make it better and simpler!" So, I have created an Excel spreadsheet (Replicator.xlsm) that you will hopefully find helpful and easy to use.

Files

Replicator.xlsm Excel file. Version 1.1.0.0 October 15, 2020

GPUG - Macros to the Rescue.pptx Version 2020.10.08   is the instruction file for Replicator.xlsm. This PowerPoint slide deck is large (around 200 MB) because it contain embedded video demonstrations.

GP Macro.xml Right click and save as. Custom colors for GP Macros in Notepad++ (It doesn't seem to be a valid xml file, but Notepad++ likes it)

Notepad ++ - free text editor

Version History

Press the "?" button in Replicator to see what version you are currently using.

VersionDateFeatures
1.1.0.0October 15, 2020Added Map Columns to easily map data columns into the macro
Fixed bug that occurred if a ' or ~ was in the data.
Repositioned the Clear Data and Reset Data buttons
Updated "Freeze Panes" so the top of data table is always visible
Renamed "Header Rows" to "Header Lines"
1.0.1.0August 12, 2019Added "Data Table Name" so your data table can live in a different location (worksheet or workbook)
1.0.0.3June 12, 2018Minor changes/fixes
1.0.0.2January 29, 2018Major Update! Added MaxRecordNumber and changed UI layout
PriorOctober ??, 2017Initial release... Please upgrade! :)