Excel Charting

Nov 2009

Overview

download sample file

This sample was stripped from a solution where the excel chart was the whole result of the database. This could also just be setup as a 'report' in a FileMaker solution.

It's meant to be used by people who have Microsoft Office on their machine and are familar with charting in Excel so they don't have to install any plugins.

Pathing is an issue when implementing this.

The sample files need to be in this location for it to work.

When you create yours, it can be anywhere.

In a networked environment, a networked drive is best as then you don't need to distribute the solution when it get's updated.

The FileMaker Database

Depending where you place your solutions, modify the paths in the chartIt script for export of the text file called exportFile.txt as well as the open URL script items.

This will export the data to the text file and then open the excel file.

 

The Excel File

From scratch.

Create a new excel spreadsheet and save it.

go to where you want your data to sit in the file and choose:

Data / Import External Data / Import Data

 

 

Select the exportFile.txt and import it.

You then need to set some properties so that the data is refreshed automatically when you open the spreadsheet.

select Data / Import External Data / Data Range Properties

In the 'External Data Range Properties' window that appears:

change the Refresh control section from:

to

and click OK

Save the file and re-open it.

It will automatically prompt you to refresh the data

click Enable automatic refresh.