Excel formatting

[David Jondreau]

I'm using Save As Excel... to get data out of Filemaker. One of the fields is a text field (500 characters) and when it goes to Excel, the display is a bunch of hash marks (#####). Setting the cell formatting to "General" solves the problem.

But the client is awful picky and want the text to display correctly without doing that last extra step.

Is there a way to set the formatting of an Excel field to display the long text in a field?

[fmdevusa]

You might not want to do it cause it requires a bit of work - but once the method is established it becomes easier for other needs.

1) Create an Excel with 'placeholders' for content and format anything and everything within Excel - as much sophistication as you want.

2) in Excel "Save as XML"

3) Place the XML file's content - the complete text in a system field within a FileMaker record.

4) When you are ready to create the file - by some means - substitute the content placeholders with the live content. It is basically an excel mail merge except for cells.

5) Export the result field as a file (.xls) this is the short method since the file is still technically text it will open properly in the Excel program with all that formatting in place - OR - more lengthy method is to use AppleScript to convert it to binary .xls - this is necessary if you are using protections and want the file protected etc. If you are on windows - you could create a macro method convert on first open,

Others might have a simpler method but this is extremely powerful in that anything you can design in Excel you can populate with FM data and export it.

[bcooney]

How about setting Excel up to use a Data Source? That's how I "bring" FM data into a highly formatted worksheet.

Another approach may be to have the data on a worksheet that's not for viewing, and create a reference to the data from one cell to another.