5 Replies Latest reply on Jun 12, 2014 8:03 AM by mwtse

    Set column width for exported Excel file w/FMP 13

    mwtse

      Title

      Set column width for exported Excel file w/FMP 13

      Post

           Hello, I need to create 30 Excel files with identical format. Is there anyway to set the format of the output Excel file? Say, I want to set the column width. Is it possible to do within FMP? Or I need to do it in Excel after the file is created? Please point me towards the direction to where the elite users is doing. Thank you!

        • 1. Re: Set column width for exported Excel file w/FMP 13
          philmodjunk

               As far as I know, you'll have to open the files in Excel and set column widths there.

          • 2. Re: Set column width for exported Excel file w/FMP 13
            mwtse

                 I can imagine that this situation is common enough that there should be better ways to handle than setting the column width manually one by one. I have tried to use Paste Special -> Column width to copy the format from the first file so that I can obtain consistent results. However, the job is tedious and after pasting once, I have to copy again before I can paste the column width to the next file.

                  

            • 3. Re: Set column width for exported Excel file w/FMP 13
              gethappy@happypc.com

                   natively it can't be done.  But there is a solution which is fairly involved.

                   Basically you can create a 'template' using an XSLT file.  Then you export (not as an excel document) but as an XSL file, and in the export command you tell it to use the template to format the document.

                   From the user's perspective, they just click a button and the document opens in excel, but under the hood it is very different.

                   The cool thing about this is that you can preformat all sorts of things in excel (font style, column width, hidden columns, calculations in excel ... basically anything.

                   The huge downside though is that the export must contain the same fields each time, since the template is expecting the data in a certain order.  So the user can not just on the fly send random data to excel expecting it to be formatted nicely, but if the report they want to generate contains the same fields each time ... then this is a good solution.

                   The other downside is that the XSLT 'template' must reside on each users computer, so in a network environment you have an added task of making certain each user has the xslt file residing on their computer.

                   This gives you lots of flexibility, but is kind of a pain for the above reasons.

              • 4. Re: Set column width for exported Excel file w/FMP 13
                philmodjunk

                     I have also read of users that set up a Macro in Excel to import the data from a tab or csv file. They'd export to tab or csv, then use send event in a script to open the file--which then imported the data from the text file. This allowed them to set up a "template" excel file with the desired formatting but could still export data from FileMaker to Excel. You could even store such a template in a container field and export it with an option set to open it automatically when exported using Export Field contents to generate a new copy to import your data each time you need to do so.

                • 5. Re: Set column width for exported Excel file w/FMP 13
                  mwtse

                       I finally sort out the solution: use perform applescript.

                       Select the "After saving: Automatically open file" option in the Save Records as Excel command, then use the following applescript.