6 Replies Latest reply: May 15, 2014 9:53 AM by Shep _ RSS

    Export ->Excel - Format \ Style question

    Shep _

      *I am new to Monarch.

      Can I control the ExcelFormatStyle criteria generated by export to Excel?  If yes. . .I'm thinking (for my current effort) I'd like it to reflect font=Courier versus Tahoma.  Which, so far, Tahoma seems to always be in the background regardless of font displayed.

      My goal:

      for the Monarch field display     emplate width (column width) to be literal and set Excel column width upon export (based on Courier monospace font)

      My doubt:

      I was not sure if Monarch was actually setting the Excel sheet FormatStyle upon export.

      As stated, I'm new to to this app so I hope I'm not   :confused:  and barking up the wrong tree

        • Export ->Excel - Format \ Style question
          Data Kruncher

          Hi Shep. Welcome to the forum.

           

          There are a couple of things you can do to control the Excel formatting. First, be sure to use the most recent Excel export driver: go to Options, Export and Clipboard... and on the General tab, select the Excel 97+ (Monarch V9) list item for XLS Files. Turn on the Apply formatting to Excel files and Apply "Advanced Excel Features" checkboxes.

           

          Now, in your table or summary windows, set the current font to Courier. You'll probably see the font list in the toolbar next to the calculator icon.

           

          Finally, assuming you're in the table window, go to the Edit menu and Select All. Edit menu again, and Autosize column widths.

           

          This will get you the look you want in Excel.

           

          Kruncher

          • Export ->Excel - Format \ Style question
            Shep _

            Thanks for the info.

            All that was already set.

            But,I don't want to auto-fit.

            If I set a templatedisplay width to 22 I was hoping it would export and set the Excel column to a literal 22.  Even if no cell in the column contains all 22 characters.

             

            But what I see in Excel after exporting is that the font displays as Courier but the ExcelFormatStyle option has a font of Tahoma.  This implies the sheet font (in a blink during export creation) was Tahoma changed to Courier.

            Then - the column I'm expecting to be 22 wide is something like 23.87 wide.  Or, if I auto-fit before exporting - naturally the columns mide be much more narrow than desired.

             

            I can always fix it once in Excel - but just thought this width couldwould carry through from the Monarch table.

            • Export ->Excel - Format \ Style question
              Data Kruncher

              For this type of fine formatting control, you may wish to consider using an Excel macro. You could store the macro in your "Personal.xls" file in a manner which would allow you to easily run the macro on any active sheet. That way you woundn't obliterate the macro when Monarch overwrites a regularly updated (exported) xls file.

               

              Finally, to make it easy to execute this macro, you could create an icon in your toolbars that would run the macro just by clicking that icon.

               

              The code for the macro would include lines similar to:

               

              [font="courier"]    Columns("A:G").ColumnWidth = 22

                  Columns("B").ColumnWidth = 11

                  Columns("D").ColumnWidth = 11

                  Columns("F").ColumnWidth = 11[/font][/quote]Just an idea.

               

              Kruncher

              • Export ->Excel - Format \ Style question
                Shep _

                Thanks Kruncher, for that info as well.

                I'm still seeking clarification to my original question:  "Can I control the ExcelFormatStyle criteria generated by export to Excel?"

                 

                Those settings can affect the behavior of the entire worksheetworkbook.  I use the Excel FormatStyle menu commands to default format a worksheet(workbook) to use a fixed-width font such as Courier. . .throughout.

                 

                What Monarch seems to be doing is initially exporting the table data to Excel with a 'FormatStyleFont=Tahoma'(in the background).

                The resulting sheet I'm looking at may display data in Courier (or whatever font I set the table to) in the populated cells, but, the rowcolumn headers, format of the sheet, and inserted new worksheets arewill be font=Tahoma - because, I assume, Monarch set the initial FomatStyle font during the export process.

                 

                Maybe that is a hardcoded no alternative action of Monarch.  If yes, then I'll reside to making adjustments after I get to Excel.  But, if I do have some option to control that factor of export I might save myself tweaking time.

                • Export ->Excel - Format \ Style question
                  Gareth Horton

                  Shep,

                   

                  We use the default styles when we create a new workbook, there is no control over that in Monarch.

                   

                  You could create an empty spreadsheet with your style included, then add sheets to it via an export instead.

                   

                  At least then you could easily reapply the style to the new data.

                   

                  Gareth

                   

                  Originally posted by Shep:

                  Thanks Kruncher, for that info as well.

                  I'm still seeking clarification to my original question:  "Can I control the ExcelFormatStyle criteria generated by export to Excel?"

                   

                  Those settings can affect the behavior of the entire worksheetworkbook.  I use the Excel FormatStyle menu commands to default format a worksheet(workbook) to use a fixed-width font such as Courier. . .throughout.

                   

                  What Monarch seems to be doing is initially exporting the table data to Excel with a 'FormatStyleFont=Tahoma'(in the background).

                  The resulting sheet I'm looking at may display data in Courier (or whatever font I set the table to) in the populated cells, but, the rowcolumn headers, format of the sheet, and inserted new worksheets arewill be font=Tahoma - because, I assume, Monarch set the initial FomatStyle font during the export process.

                   

                  Maybe that is a hardcoded no alternative action of Monarch.  If yes, then I'll reside to making adjustments after I get to Excel.  But, if I do have some option to control that factor of export I might save myself tweaking time. /b[/quote]

                  • Export ->Excel - Format \ Style question
                    Shep _

                    I believe that answers it, Gareth.

                    I apprecite the information.

                     

                    Now - forum etiquette question:

                    Am I supposed to acknowledge "issue resolved" or somehow 'close' this post?

                    Or, do entries sit out here until they drop off over time?