6 Replies Latest reply on Sep 20, 2014 6:42 AM by monkeybreadsoftware

    Adding a row to exported excel file

    fmpdan

      I have to export a file and add a header to it.

      I would prefer to just export it as an excel file if i can get the header record in there.

       

      The excel file would have in row one the 1st 4 columns populated with for consistent pieces of data

      Row two is the field names

      Then the data

       

      Has anyone done this before. I am looking for the easiest way out here.

      I have trio file and base elements so I can certainly manipulate a file.

       

      And I can send it as .csv if i have to.

      I have attached the sample output they want.

        • 1. Re: Adding a row to exported excel file
          mikebeargie

          I've done it as a virtual table, or by creating my header row records and adding them to the table (and removing them when the export is done).

           

          Scripting the adding of the header row records is pretty easy. Virtual listing is a bit tougher to explain.

           

          The key to adding records that act as the header row is sorting it correctly. If you use a hidden field, EG "SortOrder", then you can easily do the following:

           

          Replace Field Contents [ table::SortOrder ; serial numbers, start at 5 ]

          New Record

          Set Field [ table::SortOrder ; 1 ]

          //Set header row fields

          //Repeat for other header rows

          Sort Records [ no dialog ; SortOrder ASC ]

          Export to Excel [ do not use fields as header row ]

          • 2. Re: Adding a row to exported excel file
            mikebeargie

            Oh, and you can delete the header rows after the export by doing a find for SortOrder = 1...4

            • 3. Re: Adding a row to exported excel file
              beverly

              + 1 on Mike B's reply, as a method I've used.

               

              #2 might be to make a ".csv" as text in one field and export field contents (with this help on HOW: <http://filemakerhacks.com/2012/09/23/export-field-contents-as-utf-8/> )

               

              #3 might be as xml and apply stylesheet

               

                   a. to make it HTML table - which Excel can open easily

               

                   b. to make it XML (.xsl extension) - that is the format for open office that Excel will allow formulas, formatting and other cool things you can't get in a "table" otherwise

               

              Beverly

              • 4. Re: Adding a row to exported excel file
                user19752

                Add one more way using vbscript. (works only on Windows)

                 

                Set Variable [ $path ; Value:Get ( DesktopPath ) & Get ( UUID ) & ".xlsx" ] //use any path you like.

                Save Records as Excel [ File Name: “$path” ; Records being browsed ; Use field names as column names ]

                [ Restore; No dialog ]

                Set Variable [ $path ; Value:Substitute ( Replace ( $path ; 1 ; 1 ; "" ) ; "/" ; "\\" ) ]

                Send Event [ open document/application ; "MSHTA vbscript:Close(Execute(\"

                Set E=CreateObject(\"\"Excel.Application\"\"):

                E.Workbooks.Open(\"\"" & $path & "\"\"):

                Set B=E.Workbooks(1):

                Set S=B.Worksheets(1):

                S.Rows(1).Insert():

                S.Range(\"\"A1\"\").value=\"\"This is set by FM\"\":

                S.Range(\"\"B1\"\").value=\"\"This is set by FM too!\"\":

                S.Range(\"\"C1\"\").formula=\"\"=1+2+3\"\":

                B.Save():

                E.Quit()\"))" ]

                 

                I like MSHTA that avoid making any field / file to run vbscript from FM script, but if more long vbscript you need, write it in a (global) field and export with 'automatically open' to run.

                • 5. Re: Adding a row to exported excel file
                  wimdecorte

                  user19752 wrote:

                   

                  I like MSHTA that avoid making any field / file to run vbscript from FM script, but if more long vbscript you need, write it in a (global) field and export with 'automatically open' to run.

                   

                  A variation that is the combination of both methods:

                   

                  - store the VBscript and store it in a field & use Export Field Contents to export it, but do NOT set it auto-open

                  - use Send Event to run the script: now you can pass parameters to that script

                  • 6. Re: Adding a row to exported excel file
                    monkeybreadsoftware

                    or you use MBS Filemaker Plug-in.

                    Using libXL it can read/write excel files.

                    You can either write a script to do the export yourself or load exported file from FileMaker and modify it.