Today’s #TipTuesday post is the last in my mini-series of SmartList tips for Dynamics GP. I’m going to walk through a very simplistic example of an export from an Account Transactions smartlist, to Excel, where a macro will do some basic formatting and create a formula for Net Amount.

The method I show below is just one way to do this, and it’s a mini end-to-end example that you can follow if this is your first time using this feature. The intent of SmartList Export Options is to automate repetitive tasks. If you export SmartLists a lot and continually do the same things to the exported file, then read this post to see how you might be able to automate part of your work with that particular export. While the example is simplistic, the options are fairly limitless with what you could do with VBA in Excel!

Step 1 - get the Excel file ready

I start with exporting the SmartList I want to work with. I save that particular set of columns as a Favorite so that it’s “fixed” (more or less!) and it’s at the Favorite level that you will associate the macro to later.

At this point, I have some data to work with to create and fine-tune my macro. If you’re not used to working with VBA, one way to quickly get something to work with is to plan your steps, then record the macro to get some of the commands ready. Here’s a brief .gif example of me doing just that to show you what I mean.

My steps were:

• Rename the tab (optional, just one thing I like to do)
• Formatting - choose your preferred font, size, alignment etc.
• Column resizing
• Number formatting
• Search and replace the C$ prefix on my dollar columns
• Adding a formula for Net Amount

My macro recording looked like this code below (after some cleanup to streamline a few steps). The arrow shows the macro name. I chose to leave it as “TestMacro” but ordinarily I would give it a more meaningful name. This name - whatever it is - is what you would put in the Export Solutions window when you create this later.

I’ve also highlighted an area that I would be fixing if this were real. It’s a specific range of cells for copying my Net Amount formula down to. If this were a production macro, I would be changing this code to “find” the last row and copy the formula down so that it worked no matter how many rows of data exported.

At this point, save this file somewhere as an macro-enabled file (.xlsm). If it’s an Export Solution that others will use, ensure you save it somewhere that is accessible by all users.

One side note: I usually am testing and re-testing the macro thoroughly before moving on, using the original test data. Once I’m happy with how it works in Excel, the rest should be easy!

Step 2 - Export Solution setup

Now that your macro is working, go back into Dynamics GP and SmartList. Click on the SmartList button on the toolbar > Export Solutions.

In the Export Solutions window, click on New at the bottom of the screen.

• Type in a name and note: this is the name the user will see in the Excel drop down menu in SmartList.
• Browse to your Excel macro enabled file.
• Enter the name of the macro for a Preparation Macro if you have one. This would be a macro you want to run before exporting the data to Excel, such as clearing out a pivot table.
• Enter the name of the macro for the Completion Macro. In my case, my VBA macro name was “TestMacro” so that is what I put here.
• Select a Visible To setting - everyone or yourself only.
• Choose the application - Excel or Word
• Browse through the SmartList objects and once you find the one you want this to be attached to, expand and mark one or more SmartList Favorites to associate the Export Solution to. The only caveat is you want to ensure that the favorites all have the same column layout (or, more specifically that the macro would run properly on all of your selections). In my example I’m adding a formula so I need something with F & G as Debit and Credit and then H empty to put in Net Amount. Columns A to E technically could be anything else, and it would still work.
• Click on Save and close the window to test it.

Using an Export Solution

If you select that SmartList object but don’t select a favorite with an Export Solution built for it, the Excel button is normal, like the screenshot below.

If you select a SmartList with an Export Solution built for it, the Excel button will appear as a drop down list, like this screenshot below.

Now clicking on Excel doesn’t immediately export anything, you must select either Quick Export (the default no-macro-enabled export) or one of the Export Solutions. I only have one Export Solution on this favorite, so I see 2 options in the drop down list - the Export Solution and Quick Export.

Note that the name is what I called this from the setup window above so in a practical use, calling it what I did would not make sense; it would be better to name it something indicative of what the solution looks like when I’m done. “Export with Net Amount” or something like that…

If I select that Export Solution example, Excel does its thing and immediately runs the macro. If the macro is small enough (fast enough), it’s so quick you won’t see it working, so you really want to test it with a different set of data than what you mocked up your macro with - i.e. filter the data differently to have more or less rows of data than what you started with for example.

Here is what mine looks like after export, basic, but a good example to follow to practice this!

Summary

There are lots of things you can do with this functionality and it’s fun to play with scenarios. For a client, they had a process where they exporting a certain SmartList, formatting and sorted it a specific way, subtotalled the sheet, and created a pivot table for it. We recreated that in an Export Solution and while it doesn’t save hours of time per use, it saves minutes of time every time they export that particular set of data which adds up over the year. It’s a very simple time-saving or automation tool that anyone can build right away.