We all know that Monarch can export to wide range of file formats, and that here the local favorite for Monarch exports is clearly Excel files. But do you know the three secret things Monarch does when it creates those Excel files that we can always easily exploit when working with that data in Excel?
I’ll give you a hint: it’s in the names.
Restricted Spaces
When you tell Monarch what you want to name the tables (worksheets) within the Excel file that you’re about to export to, it will create the worksheet in the Excel file with underscore characters instead of spaces. Monarch v9 will allow you to specify a name like “No Spaces”, and it will produce the worksheet named No_Spaces. Monarch v10 however will complain loudly when you tell it to create “No Spaces”, with a nasty “Illegal name syntax” error. As much as I’ve complained to the Datawatch development team about such messages in the product that were clearly designed by programmers for programmers, a few of them still seem to persist. Honestly, would it have been that difficult to actually tell the user that spaces in the table name cannot be used? Or silently convert spaces to underscores as was done in v9? And I still don’t know how v11 reacts.
This is fairly obvious when you manually export from the Table window, but it may not be when you create project exports, especially where summaries are concerned. This is because spaces are perfectly acceptable in summary names. Just remember that Monarch will convert all of those spaces into underscores when it exports to Excel worksheets. Same goes for any worksheets named after key values, if you’re exporting to new worksheets based on the key field values in a summary.
Nonetheless, forcing names without spaces is a good thing, because it simplifies the development of formulas that reference cells or areas on other worksheets. For instance, the formula that you create in cell B10 on the worksheet named “Chart_Data” is =Customer_Sales!A2 it’s very easy to read. Perhaps that’s a fairly simple example, but when your formula references multiple sheet names (meaningful) brevity is wonderful.
Easy Printing
Another step that Monarch does for us automatically for every exported worksheet is that it defines the first row as a repeating row in the worksheet’s print titles, when you choose to output field names as the first row. This actually triggers a second automatic event: Excel automatically creates a new range name for the print title row, conveniently named Print_Titles.
Home on the Range
Lastly, Monarch creates another custom range name for the data that is contained on the exported sheet, and it names it exactly the same as the worksheet name. So if the last populated cell on our “No_Spaces” worksheet is G150, the formula for the range name “No_Spaces” will be “=No_Spaces!$A$1:$G$150”.
This brings up another point. If the first row of this worksheet is deleted manually, then the range name will essentially break, and it’s formula will change to “=No_Spaces!#REF!”. No big deal, right? You’ve got the data that you want anyway. Wrong.
Because the range name is not working properly, Monarch will not be able to export to this worksheet. Future Monarch exports will fail, and the only error message that you’ll see from Monarch will be “Export failed” when you run the export manually. This is particularly disastrous if you’re running Monarch from a batch file and don’t see the failure notice. You’ll assume that everything went fine, when in fact your file will be populated with old, potentially misleading data. There’s no way to programmatically check for such errors when running Monarch batch files.
In Summary
So what do we now know about what’s happening behind the scenes in our export Excel files? Three things:
1) All exported worksheets names have underscores where there may have been spaces in Monarch.
2) Every exported worksheet will have a range name called Print_Titles that refers to the first row of the data set.
3) You can refer to your exact data range exported by using the range name named after the worksheet exported.
Now that we know some hidden secrets, the question becomes: What can we do with this information?
That’s a topic for another day, and another way to excel with Monarch.
