Creating an Excel file from FileMaker data

[rwoods]

Hello all

I'm a fairly experienced FileMaker Certified Developer and I have a challenge that I cannot think what the best solution might be.

We need to export data held in FileMaker to an Excel file, but not just rows and columns of data starting at cell A1. The required Excel sheet has formatting, headings and totals here and there, and is designed as a quotation that you could hand to a customer.

Does anyone have any ideas on how we could create a heavily formatted Excel sheet from FileMaker? It's needs to work on Windows 10 and Mac OS X. Maybe there is a way by exporting XML or HTML that Excel would import and magically format correctly. Or I wonder if Visual Basic within Excel could take the data and move it around automatically (I've never used VB really)

I wonder if it may just be impossible, and the user would simply have to copy/paste the information from a basic Excel export into the formatted sheet themselves.

Anyone have any ideas on how to automate this? Has anyone tried and succeeded at this before?

I've enclosed a sample of the sort of format required in case it gives anyone any inspiration!

Thanks in advance for any help.

SampleQuote.xlsx

[Wim Decorte]

A couple of options:

• if you can generate the Excel file yourself then you can look into XML and use FM's XML export with a custom XSL stylesheet that will produce the Excel document
• if the Excel file is a given (a template) that you need to fill in then OS-level / Office-level scripting is a good option.  Do not look at VBA (the embedded VB subset inside office documents):
• hard to maintain the code because each file out there will carry its own copy of the code
• likely to generate a bunch of security warnings

Office has an extremely wide and well documented API that you can touch through VBscript (ActiveX) or PowerShell.  Both of those are just plain text files so you can actually store the code in your FM solution and maintain it there and 'finalize' it with your data inserted, then output the VBS/PS1 script file to the hard drive and execute it to create the excel file.

 

Both of these options have been done many times before and you should be able to find plenty of posts on this forum and the FM Community forum.  If you don't want to spend the development time yourself then something like 360Works' Scribe may be an option.

 

[comment]

27 minutes ago, Wim Decorte said:

if you can generate the Excel file yourself then you can look into XML and use FM's XML export with a custom XSL stylesheet that will produce the Excel document

Note that this is generally limited to producing a file in the Microsoft Office Excel 2003 XML format (a.k.a. SpreadsheetML). Producing a file in Excel's native Office Open XML format (.xlsx) is much more difficult. If you must have an .xlsx file as the result, and need to do this on both platforms, then a plugin might well be your best choice.

[rwoods]

As a followup to this, and since a new topic has been started on a similar theme, we solved this by creating an HMTL file as an export, and then Excel will open that HTML file and preserve a great deal of the formatting. It was the best compromise we found, but other may have better techniques.

[comment]

1 hour ago, rwoods said:

we solved this by creating an HMTL file as an export,

I presume you create the HTML file by exporting as XML with a custom XSLT stylesheet?