There are several ways to get your FileMaker data into a format that can be used by excel. First, the Export Records script step – also available under File->Export Records – allows you to create a new file containing the found set in a format that Excel can open. Then there is the Save as Excel script step, also available under File->Save/Send Records As, which is similar to exporting but provides controls that are more like printing than exporting in that it allows you to save the found set or just the current record. Both of these options allow you to create a new file that can be opened in Excel.
But what if the file you want to put the data into already exists? You might have a file already prepared with a lot of time invested in creating formatting, formulas, charting etc. You might just want to add a few fields of data to a part of that file. None of the options mentioned above will help you. It seems, at first glance, that you would have to do it manually.
You may also want to get data out of and excel file and place it into FileMaker Pro. You could import the data but that will give you all the data in a given sheet. What if you only need a few fields or maybe just a single row of data?
We could use ODBC but I decided not to cover it here due to the wealth of existing information on the subject. If you want more information on using FileMaker with ODBC please check out the following links:
- ODBC Overview (filemaker.com)
- Using ODBC and JDBC with FileMaker Pro (filemaker.com)
- FileMaker 11 ODBC Drivers (skeletonkey.com)
There are two additional tools we can use accomplish the goal of sending data to and getting data from a preexisting, excel document.
On the Macintosh we can use AppleScript to send data from a FileMaker field to a specific cell or group of cells and also get data from a cell or group of cells and return that data to FileMaker.
DDE on Windows
DDE gives us a capable set of similar commands to accomplish the same task as we can on the Mac. The syntax is quite different but the results are the same.