2 Replies Latest reply on May 2, 2013 4:22 AM by PeerSchuimer

    Formatting exported Excel file

    FileMakerNovice

      Title

      Formatting exported Excel file

      Your post

           Hello,

           I would like to know the best way to automate the formating of an exported Excel file.  I have a script that exports a list of employees.  Filemaker's export looks horrendous.  I would like to do the following to the Excel file.  I am thinking that this will need to be done with AppleScript or Automator but am very unfamiliar with them.
           1.  Change color or multiple cells.  For instance A1 to C1 should be blue.  C1 to F1, red.  Etc.
           2.  Format cells.  For instance A2 to A200 should be centered, integer value without decimal.
           3.  Add a row and column at the beginning of the sheet.

           Is there any way to do this?  Is there any helpful documentation that you guys can point me to?  Thanks in advance.

        • 1. Re: Formatting exported Excel file
          FileMakerNovice

               This ended up being my apple script.  Hope this helps someone:

                

          do shell script "open ~/downloads/FileName.xlsx"

          tellapplication "Microsoft Excel"

          setcolorofinterior objectofrange "D1:H1" ofactive sheetto {81, 130, 187}

          setcolorofinterior objectofrange "I1:q1" ofactive sheetto {217, 150, 149}

          setcolorofinterior objectofrange "r1:Ah1" ofactive sheetto {54, 134, 154}

          setcolorofinterior objectofrange "A1:C1" ofactive sheetto {191, 191, 191}

          setcolorofinterior objectofrange "Ai1:Ak1" ofactive sheetto {191, 191, 191}

          setcoloroffont objectofrange "A1:Ak1" ofactive sheetto {251, 251, 251}

                

          setvalueofcell "A1" to "Employee"

          setvalueofcell "B1" to "ID"

          setvalueofcell "C1" to "Manager Name...ETC"

          tell active sheet

          autofit column "A:AJ"

          endtell

                

          insert into range row 1 of active sheet

          insert into range column 1 of active sheet

          setcolumn widthofrange "A:A" ofactive sheetto 4

          endtell

                

          • 2. Re: Formatting exported Excel file
            PeerSchuimer

                 This works very nice. 

                 But i can't figure out how to use it with variable filenames.

                 If you just produced this Excel file with a script, giving it a name that is defined by a variable that is used in that script. What would the Apple script line be to get that variable filename in the place of "FileName.xlsx"

                 Thanks in advance