Jump to content
ping

Export data from Filemaker to Excel

Recommended Posts

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.

 

Share this post


Link to post
Share on other sites

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 ] 
)
)

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

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. 

 

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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 by comment

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

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.

  • Like 1

Share this post


Link to post
Share on other sites
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.

 

 

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.