Best way to create graphs?

[Jason Goldsmith]

What is the best way to automate the creation of graphs from data within filemaker? Should I learn Applescript and automate the export and generation of graphs in Excel or Appleworks? How steep is the learning curve? Would QuickKeys work for this one purpose? I appreciate any suggestions!

[DanBrill]

Hi Jason,

I'm a Windows user, but here's how I do it:

1) find and sort as usual for the data you want to graph in FM

2) export it to a tab-delimited text file

3) launch an excel file that you've created already. (In windows you'd use the 'send message' script step specifying your excel file as the application to launch. I think it is a little different in Mac.)

That's pretty much it from the FM side. The rest is done in Excel and is dependant on how you've set up your spreadsheets / graphs.

To create an excel file that graphs your data, you'll need to

1) query the tab-delimited text file that FM created: From the excel menu bar go to data --> Import external data --> Import data. Point it to your text file. I tell it to return data to $A$2. This leaves the first row open for column headers. You'll see why below.

This creates the data import. You'll need to set the data range properties for future queries. Go to Data --> Import External Data --> Data Range Properties. Make sure that 'Save Query Definition' is checked. Uncheck 'Prompt for file name on refresh'. Uncheck 'Refresh data on file open'. You could leave this checked, but I'll show you why in a minute. (It's for security). Select the 'Overwrite existing cells with new data, clear unused cells' radio button option, and the 'fill down formulas in columns adjacent to data' checkbox.

2) graph the data in excel as usual. The wizard is pretty helpful here.

3) Create Auto_Open and Auto_Close macros. Auto_Open and Auto_Close are special names in excel (in VBA in generally, actually). They tell the application to execute these on open and close.

Here are examples. At this point I've exported data from FM to a text file called plt1.tab

Sub Auto_Open()

ThisWorkbook.Activate

' sheet 2 is where I keep the data. Sheet 1 is the graph itself. So...

Sheets(2).Select

' the next line refreshes the data in the query

ActiveWorkbook.RefreshAll

' the next line erases the text file that FM created for security reasons.

' Note that the exported data needs to be in the same directory as the

' spreadsheet so that the path is the same. I know you'll have some

' platform issues here....

Kill ActiveWorkbook.Path & "plt1.tab"

' next I name the data ranges that will be graphed. This hasn't always

' been necessary, but has been for certain graphs like pie charts.

' Here I had 2 columns, A and B. This creates data ranges called

' whatever you've put in A1 and B1. I had 'Name' and 'Amount'

' (Remember how I said to return data to A2? This is why.)

Range("A1:B1").Select

Range(Selection, Selection.End(xlDown)).Select

Selection.CreateNames Top:=True, Left:=False, Bottom:=False, Right:= _

False

' Then I go to the chart itself and set the source data to my new named

' ranges.

Sheets(1).Select

Windows(1).WindowState = xlMaximized

ActiveChart.SetSourceData Source:=Sheets("Data").Range("name,amount"), _

PlotBy:=xlColumns

End Sub

Sub Auto_Close()

' Again for security reasons I don't want the data hanging around

' in the spreadsheet while it isn't being used. This clears the

' data if it is in the original workbook. If the user has saved a

' copy of the workbook under a different name, the data remains....

If ActiveWorkbook.Name = "GraphContributionsByDonor.xls" Then

Sheets(2).Select

Range("A3:c16002").Select

Selection.ClearContents

Range("a1").Select

Sheets(1).Select

ActiveWorkbook.Names("Name").Delete

ActiveWorkbook.Names("Amount").Delete

ActiveWorkbook.Save

End If

End Sub

Good luck

Dan

[Jason Goldsmith]

Wow, thanks Dan. I didn't see this response until today. I will look it over carefully this weekend and then get back with any questions I may have.

Thanks again!!

Jason

[DanBrill]

Hey Jason,

Any luck?

Dan

[jfmcel]

I like XMChart. It is a little tricky to get the data formatted into the text string (a limitation of the way FileMaker implements the plug-in), but it is very versatile and well worth the money.