5 Replies Latest reply: May 15, 2014 10:01 AM by Data Kruncher RSS

    Exporting to Excel - multiple tabs in one spreadsheet

    stembe _

      Monarch Pro 7


      Hello Group,


      We have a report that has detail sales information and has sub-totals by SalesPerson. The question I have is: can we export the report to Excel in such a way that it creates multiple Excel spreadsheet tabs, one for each SalesPerson?


      The other question I have is whether we can apply different colors to columns based on the column name, and whether the color scheme can be exported to Excel (so that when the excel spreadsheet is opened, the columns already have the colors applied)?



        • Exporting to Excel - multiple tabs in one spreadsheet
          Nigel Winton

          Hi there

          Yes you can, if you set up filters for the individuals you should be able to export the table using indivdual Project Exports setting the filter in each one as individual tabs to one spreadsheet. Once you have set up the first you should be able to duplicate the export for the remainder but remember to change the option to add to the spreadsheet and not overwrite.

          If you are using summaries you will need to set up a seperate one for each person and then use Project Exports to make the spreadsheet.

          As for the colours, I had some limited success in geting colours to Excel with V7, but it was limited and only available from a summary. All I can suggest is that you experiment with the colours option in the summary setup and see what you can get.





            • Exporting to Excel - multiple tabs in one spreadsheet
              stembe _

              Thanks Nigel.


              I would like to do this in an automated environment by running a batch file, for example, to import the report into Monarch and export it to Excel. The issue I am facing is that the number of SalesPerson's on the report could be different each month when the report is executed. This month I may have 10 (and thus 10 Excel tabs), and the next month 12 (12 Excel tabs). I am not sure if I can tell Monarch to dynamically create separate tabs for each SalesPerson (based on the number of SalesPersons).


              Is something like this possible in an automated enviornment?



                • Exporting to Excel - multiple tabs in one spreadsheet
                  Data Kruncher



                  Unfortunately there's no easy fix for this type of challenge as yet.


                  You've touched on your solution, and therein lies a bit of another problem, as the automation solution is a bit on the complicated side as it necessitates some custom programming and that may be a challenge if you're not famaliar with that kind of work. If you're comfortable it with however, or at least want to give it a go, we can work toward a solution that I know will do what you want.


                  What do you say? Want to play?

                    • Exporting to Excel - multiple tabs in one spreadsheet
                      Nick Osdale-Popa

                      As Kruncher has stated, it will take some doing with a batch file.  Your profile doesn't indicate what version of Monarch you are using. In V9, you can provide a value for a runtime parameter, that would go a long way in setting up the automation.


                      I envision a 2-pass, 2-model solution with a batch file.  1st Pass/1st Model would export a distinct list of your salespeople to a text file.  The 2nd Pass/2nd Model would read that list of salespeople and pass them as a runtime parameter which you would use as your filter criteria.



                      EDIT: To clarify, all this can be done with 1 batch file, the report itself would be processed twice.