You can export to an existing file and append to an existing tab (worksheet) if the file and worksheet exists. If it doesn’t, Monarch will create the file and worksheet for you. However, you will need to designate what Monarch should do if the file and worksheet exist. To do so…try the following:
- Go to File>>>Project Exports
- Click ‘New’
- You may designate an export name if you like or Monarch will place a default value.
- Under Data – select Table
- If you have any filters setup that you wish to apply, you may select it in the ‘Filter’ section.
- If you have any sort criteria setup, you may apply that as well in the ‘Sort’ section.
- Click ‘Next’
- In the ‘Save as’ section…you will need to browse out to the existing file by clicking on the folder icon next to the file input field. Once you have designated the file path, click save. You should now see the file path and file name displayed.
- If the file does NOT exist, you will need to designate the file path and file name
For example: C:Monarch Reports & ModelsTrialBalance.xls
- In the section ‘When output files exist’ select ‘Add data to file’
- Click ‘Next’
- If the file exists, you will see ‘Existing Tables’ listed. Select the table (Worksheet) desired. This will move the file name into the ‘Table Name’ field.
- In the section ‘When output tables exist’, select ‘Append data to existing table’ OR if you want the worksheet data to be overwritten with the new data, select ‘Overwrite existing table’
- Click Finish
- You should now see the project export listed. Select the export and click ‘Run’
You should have achieved your desired result.
Also, if you want Monarch to apply the format to an Excel file, you will need to go to: Options>>Export And Clipboard Options>>Click the box next to XLS Files that says 'Apply formatting to XLS files'.
If you have any problems or need further clarification, don’t hesitate to ask!
The Existing Tables list doesn't display sheet tabs from Excel, but rather named ranges that are available in the Workbook. If you want to append data to an existing worksheet, you have to name the range.
In Excel, highlight the range of cells that consists of your data. From the Insert Menu, Select Name|Define... and give the Range a name. Save and Close Excel.
Now when you select that file to append for the Export in Monarch, that Range Name will show up in the list of Existing Tables. I believe the column names and order need to be consistent in both your Monarch Table and the Excel worksheet.
I think Todd is right on this. When you create a Named Range you probably see a box that is tagged "Refers To" and the values in there define the boundaries of the range within the worksheet. (Also the worksheet within the workbook!).
It probably makes sense to set the the named range to represent the whole worksheet. ( $1:$65536 )
You can do this in the Define option by editing the 'refers to' or simply by clicking in the box at the extreme top left of the Excel display window - the one that selects the entire workbook. (I assume your version of excel will do that, it works for me!).