Use FileMaker data as source for PivotTable xl2004

[Theodore_MH]

Version: 2004
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

Hi.
I'm trying to set up an Excel 2004 Pivottable that uses a FileMaker Pro file as its data source. I do this all the time in windows (Excel 2003 or 2007 with Access), and it's very simple.
I bought FMP v10 and I've created my database and I've followed the rather cumbersome instructions available from FMP to install and configure the ODBC drivers. I can see and select the ODBC driver when I try to import external data into Excel, but the Query can't make a connection and the import fails.
When I try to create a Pivottable using my FMP database as the source, I can see the database file in the dialog box, but it's grayed out so I can't proceed with the PivotTable.
I've set up all the permissions and turned on sharing for the database file, so it should be working as far as I can tell. Filemaker's tech support is only marginally helpful, so I'm stuck.
Any assistance would be greatly appreciated.

 

[Bob Greenblatt]

Version: 2004
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

Hi.
I'm trying to set up an Excel 2004 Pivottable that uses a FileMaker Pro file
as its data source. I do this all the time in windows (Excel 2003 or 2007 with
Access), and it's very simple.
I bought FMP v10 and I've created my database and I've followed the rather
cumbersome instructions available from FMP to install and configure the ODBC
drivers. I can see and select the ODBC driver when I try to import external
data into Excel, but the Query can't make a connection and the import fails.
When I try to create a Pivottable using my FMP database as the source, I can
see the database file in the dialog box, but it's grayed out so I can't
proceed with the PivotTable.
I've set up all the permissions and turned on sharing for the database file,
so it should be working as far as I can tell. Filemaker's tech support is only
marginally helpful, so I'm stuck.
Any assistance would be greatly appreciated.

According to Help in Excel 2008, only file maker Pro 7 (.fp7) files can be
handled. So, I would seriously doubt that Excel 2004 can import FM10 files.
I think you will need to have filemaker convert the database to fm7 format.

 

[Theodore_MH]

Hi Bob,

Thanks so much for the reply. My database files are all .fp7, which is what FMP v10 defaults to. Any other suggestions?

 

[Jim Gordon MVP]

Version: 2004
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

Hi.
I'm trying to set up an Excel 2004 Pivottable that uses a FileMaker Pro file as its data source. I do this all the time in windows (Excel 2003 or 2007 with Access), and it's very simple.
I bought FMP v10 and I've created my database and I've followed the rather cumbersome instructions available from FMP to install and configure the ODBC drivers. I can see and select the ODBC driver when I try to import external data into Excel, but the Query can't make a connection and the import fails.
When I try to create a Pivottable using my FMP database as the source, I can see the database file in the dialog box, but it's grayed out so I can't proceed with the PivotTable.
I've set up all the permissions and turned on sharing for the database file, so it should be working as far as I can tell. Filemaker's tech support is only marginally helpful, so I'm stuck.
Any assistance would be greatly appreciated.

Hi

Can you be more specific about what happens when you choose Data > Get
External Data > New Database Query?

You should see two tabs: User DSN and System DSN. Which did you choose?
Did you click the Add button? Did you see FileMake Pro7 in the list? Did
you select it? What happened next?

The problem seems to be that you have not configured the ODBC driver
correctly, but without more information it's hard to tell for sure.

-Jim

 

[r_poetic]

Hi Bob,

Thanks so much for the reply. My database files are all .fp7, which is what FMP v10 defaults to. Any other suggestions?

Do you have the FM database open? At least in some cases, you can't
connect odbc to FM unless it is running.

R

 

[google]

I'm trying to set up an Excel 2004 Pivottable that uses a FileMaker Pro file as its data source. I do this all the time in windows (Excel 2003 or 2007 with Access), and it's very simple.
I bought FMP v10 and I've created my database and I've followed the rather cumbersome instructions available from FMP to install and configure the ODBC drivers. I can see and select the ODBC driver when I try to import external data into Excel, but the Query can't make a connection and the import fails.

If you have your System DSN configured according to the instructions
provided by FileMaker, you should definitely be able to create a
PivotTable from your FM data. I just tried it myself (using Excel
2004 and FMP 10 and the included SequeLink driver), and Excel was able
to connect successfully and create the PivotTable.

As was mentioned, you need to make sure FMP is running on the same Mac
as Excel, the .fp7 file needs to be open with xDBC sharing enabled,
and the DSN needs to contain the following keywords:

Host=localhost
ServerDataSource=<database name without .fp7 extension>
Port=2399

Jonathan Monroe
Actual Technologies - ODBC for Mac OS X
http://www.actualtechnologies.com

 

[Theodore_MH]

Hi

Can you be more specific about what happens when you choose Data > Get
External Data > New Database Query?

You should see two tabs: User DSN and System DSN. Which did you choose?
Did you click the Add button? Did you see FileMake Pro7 in the list? Did
you select it? What happened next?

The problem seems to be that you have not configured the ODBC driver
correctly, but without more information it's hard to tell for sure.

-Jim

--
Jim Gordon
Mac MVP

<http://www.agentjim.com/MVP/welcome.htm>

Hi Jim. Thanks for replying. Here’s what happens when I choose Data > Get External Data > New Database Query in Excel 2004.

Excel launches the iODBC Data Source Chooser and displays my one entry in the System Data Sources list (I’ve also tried doing this whole setup using a User DSN, and I get the same result).

I click the Data Source to select it and then click OK.

I get a prompt for my Data Source Login username and password, which I enter. This is the same username and password I use in FMP to open the database file.

I immediately get a msg “Could not establish connection.” The only option is OK, which I click. Then I get another message: Microsoft Excel did not receive any information from Microsoft Query.” Again, there’s one option, OK, which I click.

At this point I’m back where I started with a new blank workbook.

I followed the instructions closely to configure the ODBC driver, and my data source shows up as expected in the Chooser.

Is this enough info for you to help me? Thanks.

Ted

 

[Theodore_MH]

Do you have the FM database open? At least in some cases, you can't
connect odbc to FM unless it is running.

R

I've tried the import with the FM database open and closed. It doesn't seem to affect the result either way. Any other suggestions?

Many thanks.

Ted

 

[Theodore_MH]

If you have your System DSN configured according to the instructions
provided by FileMaker, you should definitely be able to create a
PivotTable from your FM data. I just tried it myself (using Excel
2004 and FMP 10 and the included SequeLink driver), and Excel was able
to connect successfully and create the PivotTable.

As was mentioned, you need to make sure FMP is running on the same Mac
as Excel, the .fp7 file needs to be open with xDBC sharing enabled,
and the DSN needs to contain the following keywords:

Host=localhost
ServerDataSource=
Port=2399

Jonathan Monroe
Actual Technologies - ODBC for Mac OS X
<http://www.actualtechnologies.com>

Hi Jonathan, and thanks for the reply. It took me a while to find the instructions for the keywords in the FMP documentation, but yes I have the keywords set up. I was able to set up the keywords in the ODBC administrator either as part of the Driver configuration or part of the System DSN configuration. I tried each one separately and got the same result. So I put the keywords in for both the Driver and the System DSN. I get the same result regardless.
I also tried different sequencing for the keywords--does it matter which of the three keywords is at the top of the list, which is second and which is third?
I'm doing everything on one machine (Mac Pro with OS 10.5). I have definitely enabled Sharing for my database, as instucted in the FMP documentation.

Any ideas why this still doesn't work?

Many thanks.

 

[Theodore_MH]

Hi

Can you be more specific about what happens when you choose Data > Get
External Data > New Database Query?

You should see two tabs: User DSN and System DSN. Which did you choose?
Did you click the Add button? Did you see FileMake Pro7 in the list? Did
you select it? What happened next?

The problem seems to be that you have not configured the ODBC driver
correctly, but without more information it's hard to tell for sure.

-Jim

--
Jim Gordon
Mac MVP

<http://www.agentjim.com/MVP/welcome.htm>

Jim, this is my second reply to your reply. Here's some additional information. When I click Test on the iODBC chooser window after selecting my data source, I get the following message:

IM002
[iODBC][Driver Manager]Data source name not found and no
default driver specified. Driver could not be loaded.

The only option is OK, which I click, and that returns me to the Chooser. I click OK to exit back to Excel and then I get the "Could not establish connection" message followed by the "Microsoft Excel did not receive any information from Microsoft Query" message.

Then I'm back where I started with no data in my Excel file.

This is a slightly different result than when I click OK after selecting my data source in the Chooser. I thought it might possibly be relevant to the problem so I'm posting it, too.

Thanks.