Dynamic linking of FM9 + Excel

[EVH5150]

Using FM9 and Excel 2003. I would like to have fields in FM9 scripted to export to specific cells in an Excel file running macros(plugins)/formulae; then have the cells that contain the calculation results import back into separate FM9 fields of the current record.

If this is possible, I am having trouble getting this set up. Anyone have an easy or at least viable solution?

[bcooney]

Well, I usually write a macro that imports the text file from the FM export and puts the data in a range that is then referenced by the excel forumlas. So, I'm not filling "specific" cells, but rather referencing the data source range.

As for importing calc results--why? Can't you do the calcs in FM?

[aholtzapfel]

I do this

Example

This may not be the best way to do it, but it does work, at least for my needs.

(Uses a VBS script exported from a filemaker field to set cells in an excel sheet that is embeded into a container field, and a second script to get values from that sheet and return them to filemaker.)

[EVH5150]

If it is possible to rewrite ALL the functions that this spreadsheet calculates into FM fields, I would still have to write a plugin for FM to utilize the external program that runs some of the calculations.

That's a whole lot of work to redo something that is already done. Seems easier to be able to send the data, have it calculate, and then bring back the answers.

[EVH5150]

I have seen the container field option mentioned elsewhere, but wasn't sure it would work.

I will see if this works. Thank you!

[aholtzapfel]

The method can be adapted to be used on spreadsheets stored anywhere a user has access to. (not just in containers, although that was an easy way to do the sample file.)The VBS script can be a problem depending on your security settings, strong security can prevent it from running, or more often a dialog will appear asking user if the script should run.(should only happen once as long as you use the same name for the script.) If you want to look at the script it is exported to the temp folder (It is rewritten everytime the FM script is run.)

If this won't work for you, you may consider a Plug-In (not sure which one, I don't use them but I'm sure there is one out there able to do this without the security issues.)

(WARNING: using script folders can cause the VBS script to run the wrong FM script, breaking this technique.)

Good Luck

Edited November 15, 2007 by Guest
Warning