06 October 2005

Weekly Feature: Excel-ing with Smartlists

If you've used Great Plains for 5 minutes you've figured out how to export a Smartlist to Excel. Great feature, simple interface, push the button. But... there is another dimension to Smartlists and Excel that we're going to explore.

With a Smartlist open, pick Extras->Smartlist->Export Solutions to see what I'm talking about. Smartlist allows you to export to the SAME Excel spreadsheet time after time. You can also specify Excel macros to run before and/or after the export for formatting, analysis, etc.

Maybe you export data to Excel every month for graphing, analysis, etc. This feature lets you automate the process.

So how does it work?

1) First, save your smartlist as a favorite.
2) With a Smartlist open, pick Extras->Smartlist->Export Solutions.
3) Give the Smartlist a name, add your Excel file name and your before and/or after macro names. In "Visibility" set who can see this favorite and select Excel for the application.
4) In the tree view at the bottom, select your favorite and check the box
5) Click save.

Close the window. Close and Reopen Smartlists.

Pick the favorite you just saved and click Excel. You'll see two choices now, the saved export solution you just created and Quick Export. Quick Export is the normal "send to Excel with a new sheet" functionality you're used to. If you click your new favorite, your spreadsheet opens, your before macro runs, the data imports and your after macro runs.

This great for when you want to send data to same Excel file on a monthly basis. You can use a macro to move to the bottom or create a new tab, import the data, and reset a graph with an after macro.

Ideally, you would do a normal export first. Play with your Excel file and macros a couple times to get them where you want, and then setup the export solution. We'll explore some other Excel /Great Plains links coming up.