Export data from Filemaker to Excel

[ping]

I have a simple Excel document one of my partner using as an sales order sheet. A few fields has to be fill out, such as quantity, type of goods and preferred delivery date. At first I tried to learn how to make excel style sheet and export data from Filemaker to xml. Since I am mac user it turns out, it is not a simple task. Then somebody told me to explore use of apple script. It works almost like a charm. Here is what I did so far.

I created script in filemaker as follows:

Set variable [ $quantity ; Value: Orders::Quantitiy ]

Set variable [ $goods ; Value: Orders::Type of goods ]

Perform AppleScript

[ "tell application \"Microsoft Excel\"" & ¶ & "open \"Users:ping:Desktop:Order:Order.xlsx\"" & ¶ & "set value of cell \"C16\" to \"$quantity\" & ¶ & "set value of cell \"D16\" to \"$goods\"" & ¶ & "end tell" ]

By pressing button export I get following result:

Microsoft Excel opens

Order.xlsx opens

Cell C16 is filled with $quantity instead of value in field Quantity

Cell D16 is filled with $goods instead of value in field Type of goods

 

I am not developer and this is not life saving job, but I would like to learn something and automate the process if possible.

 

[comment]

AppleScript cannot read Filemaker variables. You must create the variables in the AppleScript itself, e.g.:

set theQuantity to cell "Quantity" of current record
set theGoods to cell "Type of goods" of current record
tell application "Microsoft Excel"
...
end tell

Or, if you prefer not to hard-code the field names, do something like:

Let (
template = "tell application \"Microsoft Excel\" ¶open file \"Users:ping:Desktop:Order:Order.xlsx\"¶set value of cell \"C16\" to <<quantity>> ¶set value of cell \"D16\" to \"<<goods>>\" ¶end tell" 
;
Substitute ( template ;
[ "<<quantity>>" ; Orders::Quantitiy ] ;
[ "<<goods>>" ; Orders::Type of goods ] 
)
)

 

[Steve Martino]

Why not make the sales order sheet for the partner in FileMaker? Then importing would be quite easy. 

[ping]

10 hours ago, Steve Martino said:

Why not make the sales order sheet for the partner in FileMaker? Then importing would be quite easy. 

My company is fairly small and excel sales order sheet came from a very big oil company. We asked them to create order in pdf or to send them our sales order sheet. They rejected all other solution and insist on getting an order in their predefined format.

[comment]

So did you manage to make it work?

 

Also, upon rereading your question, I am puzzled by this:

22 hours ago, ping said:

At first I tried to learn how to make excel style sheet and export data from Filemaker to xml. Since I am mac user it turns out, it is not a simple task.

What does your being a Mac user have to do with it? Filemaker exports the same way on both platforms. 

 

[ping]

On 2/15/2019 at 4:10 PM, comment said:

So did you manage to make it work?

 

Also, upon rereading your question, I am puzzled by this:

What does your being a Mac user have to do with it? Filemaker exports the same way on both platforms. 

 

I haven't been able to make this work yet. Recently, I do a lot of surfing and reading trying to understand this last part. By implementing your solution I was not able to make it work yet. Since I am not a programmer my daily duties keep me away from this puzzle but I am determined not just to solve this problem but rather to understand how to solve this problem.

When I referred myself as being Mac user I wanted to say that is harder to make XML style sheet on mac or virtually impossible. My first idea was to create an XML style sheet and export data as XML. Of course, Filemaker does not have anything to do with this.

[comment]

1 hour ago, ping said:

I wanted to say that is harder to make XML style sheet on mac or virtually impossible

I am a Mac user myself. I create XSLT stylesheets practically every day, so it is not impossible - and I cannot see why it would be harder on a Mac than on any other platform.

 

1 hour ago, ping said:

My first idea was to create an XML style sheet and export data as XML.

That would be my preference, too - provided that the target application is willing and capable of receiving a document in the Excel 2002/2003 XML format. If they must have it in the default .xlsx format, then it becomes more complicated (though not impossible).

 

 

Edited February 19, 2019 by comment

[flutegirl]

On 2/19/2019 at 3:44 AM, comment said:

I am a Mac user myself. I create XSLT stylesheets practically every day, so it is not impossible - and I cannot see why it would be harder on a Mac than on any other platform.

 

That would be my preference, too - provided that the target application is willing and capable of receiving a document in the Excel 2002/2003 XML format. If they must have it in the default .xlsx format, then it becomes more complicated (though not impossible).

 

 

I know this is a fairly old thread, but I'm struggling with this right now as well, and am running into some of the issues you're referring to here.

I've been trying to create a style sheet that will allow me to export from Filemaker with custom, user-friendly headers, and keep running into issues with Excel telling me the resulting document is not valid.

On a whim, I attempted to open the file in LibreOffice, and was a bit irritated to find that it opens just fine, and then once I save it from LibreOffice as a .xls file, THEN Excel will open it.

I'm curious if you anyone has any ideas why this is, and how I can get Excel for Mac (latest version) to open these files directly, as I can't risk sending out files that won't open in Excel.

[comment]

The best way to find the problem, IMHO, is to save an example file from Excel itself in the Excel 2003 XML format. Then open the file in a text editor and compare it to the file produced by exporting from Filemaker.

The file produced by Excel will have many more elements and attributes; you can find out which of these are essential by removing them in turn and testing if Excel still opens the file. Once you have removed the "noise", you will have an example of what your result needs to look like and you can tailor your XSLT accordingly.

[Mike Duncan]

I have used  some javascript to output an xlsx file. You can get a sample here and customize to your own needs, if that helps: https://github.com/SoliantMike/FM-js_ExcelSheets

[flutegirl]

1 hour ago, comment said:

The best way to find the problem, IMHO, is to save an example file from Excel itself in the Excel 2003 XML format. Then open the file in a text editor and compare it to the file produced by exporting from Filemaker.

The file produced by Excel will have many more elements and attributes; you can find out which of these are essential by removing them in turn and testing if Excel still opens the file. Once you have removed the "noise", you will have an example of what your result needs to look like and you can tailor your XSLT accordingly.

Thanks - I didn't even think of troubleshooting it from that direction.  Hopefully that will help me find the problem.

 

 

[EddyTaka]

On 7/31/2019 at 7:55 PM, Mike Duncan said:

I have used  some javascript to output an xlsx file. You can get a sample here and customize to your own needs, if that helps: https://github.com/SoliantMike/FM-js_ExcelSheets

Hi Mike,

It seems that your sample file does not work with FM17. Can you please have a look ?

Thank you