If you mean something beyond the Export in FM where you can select the fields to export to Excel, then writing a field to a "particular cell" in a destination spreadsheet will take more work.
Without knowing your programming background, I can't give you specific suggestions.
One way to do it would be to use the Java/JDBC to connect to the FM database in real time, extract the fields you want, and then use an API like "jExcel" (JExcelApi ) or Apache POI (POI-HSSF and POI-XSSF - Java API To Access Microsoft Excel Format Files ) to write to the spreadsheet. That code could even be automated/scheduled. And, this really is not that difficult if you're already a SW developer (say, Java, C, or .NET).
One of the things I like best about FM is that when it can't do something I want (which is more often than I would like), FM is still open enough (with external interfaces) where I can work around those limitations with some effort.
HOPE THIS HELPS.
Oh gosh, that sounds awful. I hope that area manager is making it worth your while!
I'd highly recommend you spring for a license of this plugin and use that. It will allow you to "open" an excel file, write any data from filemaker to any specified cell in the file, and then save the file as a new file.
From this you can open a "template" excel file stored in a filemaker container (or imported from a known location), make your modifications, and then export said excel file or even email it.
One thing that is sorely lacking with native methods that your area manager will probably not be happy without is excel formatting. .mer files.
Another option would be to configure FileMaker as an ODBC data source and write some macros to pull data into the spreadsheet similar to how a mail merge is done. You can do it with .mer files but it's a very "hands on process"; something you won't want to be doing for every record in the database.
Mike's ideas are cool, but the Java approach will give you complete control.
In this case, per MIke's suggestion, I would try the plug-in before trying the Java approach. (Well, if it were me, I'd do the Java approach since I don't get the cost of FM plug-ins, but I seem to be in the minority.)
I have 14 - if you require more than one sheet, I think you're out of luck (without a third party solution).
One way I've "dealt" with Excel is to use a worker table with a bunch of text fields that can be exported as columns (e.g. text01, text02, text03, etc.). I programmatically create each temporary worker-record and populate each field, including data, headers, summaries, and blank lines. Every record is a row. You can also populate a numerical "SortOrder" to help you organize the rows.
You can use MBS Plugin with XL functions to automatically remove columns.
Or use the XL functions to load a template Excel file and insert the numbers you need to report.
Load a file, insert rows with data, fill cells with values, export as Excel file.
If interested, load our plugin and check the examples.
As you can see, from the previous posts, there are many ways to approach this task...
Here is one more method to consider: AppleScript...
MicroSoft invested in AppleScript when it dropped VBA from Excel and did a very complete job. You can learn more here:
We have used AppleScript for lot of projects where data is moved from FileMaker to custom formatted Excel documents.
The disadvantage is that AppleScript is OS X only. That said, you might only need a single (perhaps a robot) OS X machine in the mix.
The advantage to AppleScript is that it is:
* Free (if you are running OS X)
* Can talk to lots of applications, for example, the Finder, email clients, FileMaker, Excel, Word, etc, etc. meaning you can set up powerful multiple application workflows.
Hope that helps.
My programming knowledge doesn't extend much beyond being the Filemaker guy, so the references to Java, etc. are over my head. I wish I had time to remedy that.
No multiple sheets. One Filemaker record > One Excel file. (Yes, file. It has to do with the corporate interim "solution".)
Not looking for any live connectivity.
It has to be something that multiple users can do easily: Push a button to export one record from FM as an Excel file, somehow have that data be in certain cells, and save that where others can access it.
I'm starting to wonder if we could just export from Filemaker to that 50+-column Excel file, then use Excel functions to move data to certain cells in a "template" that would be easier for them to work with than the loooong spreadsheet with confusing columns. Apparently that's the major issue.
A plug-in is a possibility, including 360Works Scribe. This:
It will allow you to "open" an excel file, write any data from filemaker to any specified cell in the file, and then save the file as a new file.
...does sound like what we want to do--although I want to make sure I'm not overlooking an "easier" way to accomplish this before I recommend spending $$.
Make sure to factor in your labor in your cost estimates. The value of most plugins comes in their ability to save hours of development time in the “cheaper” alternatives.
That's a good point, Mike.
I would only add that FM plug-ins are only for FM, which might be OK for most.
But, when I create a "workaround" for missing FM functionality, using other techniques, it is generic enough, usually, where I can use it elsewhere -- that is, in other non FM contexts ( as well as in FM).
HOPE THIS HELPS.
This is almost similar to a solution for a client. They had a workbook solution (Excel) where the first tab (worksheet) was the data imported from .csv (from FileMaker). Then there were wonderful links on the other tabs to reference the imported data. They had many reports (each for a single user) that used the same template, but different data.
It worked beautifully, even if it needed a little manual intervention (to import).
The Scribe Plug-in (not available at the time) would definitely take the manual part out of the equation, once setup could be used again and again.
Before you go getting all complicated, I just want to check that you are aware that you can *choose* which fields (columns) you send to excel if you select "Export <Table Name> Records", instead of "Save/Send <Table Name> Records as ..."
That way you can leave out the unwanted and confusing fields/columns like interface fields or unwanted related fields.
And you can name the worksheet.
+1 and the context makes a difference in a related export. from parent is different than from child(-ren).
One Excel file per record? A single-row Excel file? You can isolate one record and export just its data.
You could have a found set of records and loop through them creating an Excel file per record.
If you only want 10 of the 50 fields available you can just export those 10 - eg: Field A, Field D, Field M, etc.
If you want them in a different order (Field M, Field A, Field D) then re-arrange them in the export dialogue.
If you want them to appear in the Excel sheet in Column A, [blank], [blank], Column F, etc, then you could create a second worksheet that references the exported columns and displays them in the sequence you want.
The problem with that is that you would be over-writing your Workbook-with-calculations every time you created another export. To get around that, either create the second sheet as a second single-worksheet workbook and reference from it back to the freshly exported workbook.
Create the two-sheet workbook as described, but do not export as Excel format. Export in merge format, and import the merge data into the first worksheet. You can make this happen automatically as soon as the user opens the workbook, so it appears seamless to them.
The tools available make Filemaker and Excel work really well together - various export possibilities from Filemaker, scripting them, on-timer scripting them, automatically triggering the opening of an Excel workbook, having macros auto-running on open in Excel... it is a really powerful combo. You can often make it that if the user blinks they might not even be aware they have slipped from a Filemaker table of data into a pivot chart presentation in Excel.
I come back to the first condition, though: that your solution needs to involve a single Excel file per record (and I'm qualifying that with 'single row' - because if you are trying to export what looks like a header row followed by many item-line rows then you need to heed Beverly's advice about which table you start the export from) then I think that requirement would be well worth revisiting.