6 Replies Latest reply: May 15, 2014 9:56 AM by Grant Perkins RSS

    Exporting to Excel

    Michael J. Hansen

      I am trying to create a project export that exports the table view to an existing spreadsheet and I want it to append the data to an existing tab (table) - however, the existing tabs do not show up so that I can pick the tab I want to export to. I suspect it could be because this spreadsheet wasnt originally created by Monarch, but am really at a loss - If I try to force it by typing the name of the tab, the export just creates a new tab with a 1 after that name. HELP!

        • Exporting to Excel
          Nick Osdale-Popa

          Try giving the existing data range a Range Name that will give Monarch a placeholder to export the data to.

           

          I believe you'll also have to make sure that the column names match and are in the same order, otherwise the Export may fail.

          • Exporting to Excel
            Michael J. Hansen

            Sorry, Nick, I dont quite understand what you mean - could you elaborate?

            • Exporting to Excel
              Michael-B _

              Michael -

               

              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!

              • Exporting to Excel
                Nick Osdale-Popa

                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.

                • Exporting to Excel
                  Michael J. Hansen

                  Thanks, Nick - now at least I get the name listed in existing tables - BUT, when running my project export, i get the following error: 'Jet database internal error: (3434) Cannot expand named range)' what does that mean?

                  • Exporting to Excel
                    Grant Perkins

                    Hi Michael,

                     

                    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!).

                     

                     

                    HTH

                     

                     

                    Grant