Monarch Batch File Generator

Update September 2010: Again, slight revisions have been made to the Monarch Batch File Generator. Regular site reader Lance wrote today to ask if the utility was capable of creating exports to Access databases (.mdb format), which it is. But his question prompted me to think about the new file format options available when exporting with Monarch v10, and that the Monarch Batch File Generator had not been updated to allow for those formats.

I’m pleased to say that this was addressed today, and you can now select Excel 2007 macro enabled (*.xlsm) files, as well as Access 2007 (*.accdb) files.

Also, since I can now both develop and test with Excel 2007 and Excel 2010, and as anyone interested in exporting to those formats will, in all likelihood, be using Excel 2007 or Excel 2010, I’ve created an Excel .xslm version of the Monarch Batch File Generator. Download the .xlsm file now.

————————

Update February 2010:  I’ve just revised the Monarch Batch File Generator slightly. A warning, and a recommendation, will be presented if you select a PDF file as your report file. This is because Monarch v9, and only v9 (as far as I know), has a bug related to opening PDF files as a report source via the command line. This is not an issue with Monarch v10, and should not be an issue for subsequent releases.

————————

A while ago you discovered Monarch, and you worked hard to learn how to model your data sources to easily extract the data you need and add value to that data. Your models seem to be working very well. You use your models regularly, maybe weekly or even daily, so you’ve got the routine down pat: open the data source and/or open the model, or open the project file, then go about exporting the records in the table or the summary window.

In fact, the process is so repetitive that you’re sometimes finding it almost, well, boring.

Don’t you wish you could have the computer do the work for you? Wouldn’t it be great if you could program it to do your work for you? Of course it would, but there’s one teeny problem: you’re no programmer, right? You don’t know a bit from a byte.

That’s OK. Really. Don’t worry.

The crew at Datawatch made Monarch so that you can give it instructions when you start it up, and it’ll do what you tell it. You don’t even have to click your mouse.

It will open the report you want, apply the right model to it, and send your extracted data to the file you want.

All of this is done with something called “command line parameters”. While it’s all documented in the online help, the process of creating this kind of automation is new to a lot of people, even experienced Monarch users, and they often struggle with this work.

So I thought I’d help.

Introducing Another Exclusive, and Free, Utility

ExcelWithMonarch.com, the web’s first and only site devoted exclusively to Monarch and Excel users, is proud to introduce the ExcelWithMonarch Monarch Batch File Generator (about 1.5MB). This is an Excel file specially programmed to assist you in building small batch files with which you can control your Monarch work. (Alternatively, download the tool in the  .xslm format, at just over 200k).

You can run the batch file by double-clicking on the batch file name that the Generator creates (the name, and location, of the batch file is up to you). This will essentially automate a specific job that you do with Monarch.

You give the program some basic information about the name and location of your data source, your model or project files, the details about how and where you want to store your extracted data (along with other optional data), and with a click of a button the program will make a batch file with which you can control Monarch automatically.

Technical Details

Being an Excel file with program code, there are some specific steps that you must perform before you can use the program on your computer. Those steps are detailed in the file, so I encourage you to carefully review the note included in the file.

The Monarch Batch File Generator has been designed to scan your Monarch model and project files for certain information, namely runtime parameters and project exports. This permits you to assign values to up to three runtime parameters, and either all of the defined project exports you’ve created, or specify up to ten individual project exports by name.

I’ve purposefully left the program code unprotected so that curious and adventurous readers can explore it and possibly use it as a learning tool. Please do not modify the code. The program is distributed under the Creative Commons Attribution-No Derivatives Works license, and your use of the program constitutes your agreement to abide by the terms of the license. Thanks.

Test, Test and Test Some More

The program has been thoroughly tested (I have spent more hours on this than I care to think about), but if you discover any problems please let me know and they’ll be addressed as soon as possible, as much as I can with the software and environments which I have available.

I’ve used the program on a few computers with the Windows XP and Vista operating systems. I have only Excel 2003 with which to develop and test, so you’re on your own if you’ve got something else. Sorry, but for now, that’s the best I can do.

Of course, if you have some programming expertise and have the ability to contribute (that is, volunteer your time and efforts) to improve the program, that would be most welcome. Contact me about how you can help.

Acknowledgements

No man, nor project, is an island, so I have some thanks to pass along.

First, thanks to my family for allowing me the time to devote to this admittedly self-imposed challenge. It definitely became more than I initially envisioned.

The program makes use of some code published by Microsoft Excel MVP’s Chip Pearson, and Ken Puls, and little bit on accessing help files (thanks guys!).

I began developing this program in earnest some time ago, and I am exceedingly grateful to Nick Osdale-Popa for his fantastic help in getting the program off to a solid start.

Finally, to the many others who also assisted or otherwise contributed in many ways in getting this thing launched: Cheers, and thanks!

This Tool is Not a Cure-All

As much as I wish I could tell you that this little program will solve all of your Monarch troubles, realistically it just won’t.

You still need to create good Monarch building blocks.

You still need to create good Monarch models, optionally with filters and runtime parameter calculated fields.

You still need to create project files with well defined exports.

You still need to give the Monarch Batch File Generator the right inputs in order to get the right output.

Final Notes

On my XP systems, the program feels very integrated with Monarch, in that it detects where the Monarch program is located on the computer, and it uses the favorite folders for reports, models, projects and exports, as defined in the Monarch folder options.

Unfortunately the same couldn’t be said for my Vista system, so some compromises had to be made. It still works very well, but it doesn’t quite have that “integrated with Monarch” feel you get when using the program on an XP box.

Well, I suppose that it’s time to let go of it now. All I can do is all I can do. It might not be absolutely perfect in every possible way, but if this utility program helps one single user such as it is, then I’ll be happy.

If you didn’t click the ealier links, download it now: Monarch Batch File Generator.xls (about 1.5MB). Give it a try, and let me know what you think. (Again, the tool is available in the  .xlsm file format).

I really do hope that you find this to be a useful addition to your toolkit, as you continue to excel with Monarch.