Microsoft Office 98 FileMaker Importer
By Paul Evad (paul@filemakermagazine.com)
BONUS: None
PLATFORM: Macintosh
RATING: General
WEB: http://www.Microsoft.com/mac/fmtools.htm
Microsoft announced in April, 1999, the release of an add-in package for Office 98 that would allow Excel and Microsoft Word to access data from a FileMaker database. The Office 98-FileMaker Pro Importer was created using AppleScript™ and Microsoft Visual Basic for Applications. I was excited to hear this development and welcomed a chance to put it to the test. After all, anything supporting the FileMaker community is a welcome addition, isn't it?
Getting Started
After downloading the installer package from the Microsoft site, (http://www.Microsoft.com/mac/fmtools.htm) installation was simple. The installer places things in the necessary areas of your Office 98 folder (non-english users may need to manually place things, see the readme file that the installer displays for details).
After the Microsoft Installer does its thing you have to perform a few more steps with Excel to get things to work properly. Launch Excel, then choose the Add-ins command under the Tools menu. Excel FileMaker Pro Importer should be in the list. Click the box next to it (to place a checkmark there) and turn it on.
To use the feature, you choose Get External Data: Import from FileMaker... found under the DATA menu. You'll be prompted to select a FileMaker database residing on your hard drive. With this 'add-in' you cannot access a 'hosted' database off your network. Since you can access a hosted database with a simple AppleScript, I believe this is an oversight on the part of the programmers who developed this solution.
Welcome to the Real World
The first few times I tested this new add-in, Excel crashed on my computer. Fortunately, a restart seemed to clear up the problems. The readme mentions performance degradation with large database files, or files with a lot of fields. It wasn't kidding. Using my G3 300Mhz tower with a test database of 104 fields and 6.4Mb of data, it took this Excel add-in over three and a half minutes to display the list of field names to choose from. This boggles my mind considering that the following 3 line AppleScript returns the entire field list of a database in under 2 seconds (possibly less, I didn't time it).
tell application "FileMaker Pro"
set fieldList to (get name of every field of layout 0 of database "My Database Name")
end tell
After you waith through the somewhat painful process of selecting your fields, the actual data import does proceed at a moderate pace. However, on further testing with both small and large database files, I found this 'solution' to be too cumbersome for any real world application. The whole process is just too slow to be accommodating.
The Quicker, Better Solution (aka the old way)
Personally, the faster method of getting data into an Excel spreadsheet is to simply export the desired fields from FileMaker to a tab delimited file. After exporting the file drag it on top of your Excel application icon. With no surprise, almost instantly Excel will create a new spreadsheet with your data displayed in all it's raw glory.
The Office 98 FileMaker importer licensing agreement is generous enough to allow developer's the freedom of including the solution with their own packages, at no cost. But in my opinion, what developer would want too? The good news, according to the license agreement, if you aren't happy with the Office 98 FileMaker importer, you can "return it to your place of purchase for a full refund"
The big question for Microsoft is why even bother to lock down the supplied AppleScripts and Visual Basic 'add-in'. If they had released these files as open source, AppleScript and Visual Basic developers around the world could add to and improve this 'solution'.
A Word About Word
On the other side, we have the Word add-in as part of this package. The Mail Merge with FileMaker..., found under the Tools menu in Microsoft Word may be of some benefit to a few people. Though I do recommend being VERY patient with the process, and scheduling coffee break while the 'solution' does it's work. The solution is, like its Excel counter part, a bit slow. After working with the plug-in for a number of hours I found, once again, that sticking with FileMaker to do the task was a much more suitable approach.
To sum things up it looks like I'll be using FileMaker to do what I've always done before. I admire the effort Microsoft made with this offering - however it seems like more of a marketing ploy than a real solution to what might not be a common problem. Understanding how to simply export data from FileMaker Pro is basic enough that users should take this approach than depending on a slow solution such as these add-ins.
In closing, I'd like to make an open challenge to developer's out there, who know Visual Basic and AppleScript, let's see what you can do. Show Microsoft how it really should be done. Send your solutions in to me and I'll make them available to the public off of our website.
AppleScript (and how I did it my way)
During this review, I played around with AppleScript to try and make some sense of what the Microsoft solution was doing. I came up with the following crude script which imports data from a specified database (and yes, it will work on a network copy provided it is opened on your computer). It is not intended to be 'better' solution than the Microsoft solution by any means, in fact it's rather slow. But it shows that what is offered in the add-ins is nothing close to amazing.
The following AppleScript is released under the GNU licensing agreement. So hack away and enjoy. It was created for test purposes only and doesn't include any error trapping.
-- You must have OS 8.5 or higher installed. This script relies on a new command called
-- Choose from list found in the Standard Additions of OS 8.5 or higher.
-- Original code by Paul Evad, modified by Matt Petrowsky
set myDatabase to "My Database Name" -- you must change this to the name of your database
set row to 1
set col to 1
tell application "FileMaker Pro"
set fieldList to (get name of every field of layout 0 of database myDatabase)
end tell
choose from list fieldList with prompt "Select the fields you wish to have imported." with multiple selections allowed
set selectedFields to result
tell application "Microsoft Excel"
Activate
Create New Workbook
end tell
repeat with theData in selectedFields -- this routine creates the field headers
set myrange to "R" & row & "C" & col
tell application "Microsoft Excel"
Select Range myrange
set FormulaR1C1 of ActiveCell to theData
end tell
set col to col + 1
end repeat
set row to row + 1
tell application "FileMaker Pro" -- this will get all records in the database, not just the found set
set recordCount to count of (every record of database myDatabase)
end tell
repeat recordCount times
set col to 1
repeat with theField in selectedFields
set myrange to "R" & row & "C" & col
tell application "FileMaker Pro"
-- cell foo of record row of layout 0 of database dbasename
set fieldData to get data cell named theField of record row of database myDatabase
end tell
tell application "Microsoft Excel"
Select Range myrange
set FormulaR1C1 of ActiveCell to fieldData
end tell
set col to col + 1
end repeat
set row to row + 1
end repeat
beep
display dialog "I'm Done!"
Happy Filemaking!
Paul Evad
## END ##