Charting in Microsoft Excel using the FileMaker ODBC Driver

Microsoft Office does not install Microsoft Query as part of the standard installation, so you'll need to do a custom install to use this tool. Note that you don't need Microsoft Query to refresh queried data. To edit a query or create a new query you'll need to install Microsoft Query.

Mac OS ODBC Setup:

1. Open the ODBC Setup PPC control panel to define a User DSN for FileMaker Pro.
2. Click the Add button to add a DSN for FileMaker Pro.
3. FileMaker Pro will appear near the bottom of your list as ODBC 3.11 FileMaker Pro PPC.
4. Click the Finish button. Give this DSN a name and description. You only need a single User DSN for all FileMaker Pro data hosting. I've named mine "FMP.fp3"
5. Click OK and OK again to finish with the control panel.

Windows 95/98 Setup:

1. Open the 32bit ODBC control panel and define a User DSN for FileMaker Pro.
2. Click the Add button to add a DSN for FileMaker Pro.
3. FileMaker Pro will appear near the top of your list. If it's the first item on your list it will automatically be highlighted for you.
4. Click the Finish button. Give this DSN a name and description. You only need a single User DSN for all FileMaker Pro data hosting. I've named mine "FMP.fp3"
5. Click OK and OK again to finish with the control panel.

In FileMaker Pro:

1. Select Edit...Preferences...Application Preferences...Plug-Ins...check Local Data Access Companion to turn on ODBC hosting for your copy of FileMaker Pro 4.1.
2. Select File...Sharing...check Local Data Access Companion to turn on hosting for each file.

Just as with Web Companion and Multi-User file sharing, you can selectively give or deny users access to each open file on your machine.

Creating a SQL Query In Microsoft Excel:

1. Select Data...Get External Data...Create New Query to launch Microsoft Query and use the Query wizard to create your SQL query.
2. In the Choose Data Source dialog, select the FileMaker Pro DSN you created, and click OK. Note that this dialog refers to FileMaker Pro as the database; Query will refer to each open and LDAC-shared file as a table in the next dialog. You can also save repeat queries and select them from this dialog.
3. The Query Wizard--Choose Columns dialog comes up. In the left hand side of the window are your FileMaker Pro files which are both open and LDAC-shared.
   Let me emphasize here again that you can have files open yet choose to only give selective access to files via ODBC, Web Companion or FileMaker Pro's peer-to-peer file sharing.
4. Click on the plus (+) sign to the left of the file(s) you want to extract information from to see the fields in that file.
5. Double-click on fields to include them in your query. If you want all fields in a database, simply click on the database name and then on the ">" button, and all fields will be listed on the right. Note that you can select fields from multiple files ("tables") to import into your spreadsheet. Click on the Next button to continue.
6. Include your filter ("find") criteria, if any, and click on Next to continue.
7. Specify your sort criteria ("order by"), if any, and click on Next to continue.
8. Save your query if you will be using it again in the future, and click Finish to return your data to Microsoft Excel. If you want to massage the query further or play with the SQL, select the View Data or Edit Query in Microsoft Query option.
9. Specify where to return the data in Excel: to the currently selected cell, a new worksheet, or to a Pivot Table.

You're ready to chart or further manipulate your FileMaker Pro data.

Refreshing data from a saved query:

1. Select View...Toolbars...External Data to view the query toolbar.
2. Select any cell within the data range from FileMaker Pro. When you do this several of the icons on your external data toolbar will become active, including the Edit Query, Data Range Properties, Refresh Data and Refresh All icons.
3. Refresh your data by clicking on the Refresh Data icon on the external data toolbar, or select Data...Refresh Data.
4. Any charts or calculations you've built using this data will also be refreshed.

Executing a saved query:

1. Select Data...Get External Data...Run Database Query to run a saved query.
2. Specify where to return the data in Excel: to the currently selected cell, a new worksheet, or to a Pivot Table.

Copyright 1999 by MacLane Nova New Media. May not be reproduced in any form without expressed written permission by MacLane Nova New Media.