Excel to FrameMaker Table Converter

[Bernard Harrison <bharrison@xxxxxxxxxxxxxxxxxxxxx>]

I have searched online resources looking for the
questions and solutions to this problem. My understanding
is that the solutions generally seem to be of the
following types:

Save Excel document as delimited text. Then import as
text and convert to table. This does not preserve any
Excel formatting. If any subsequent formatting is
required then this must be done manually in FrameMaker.
If Excel data is updated then the process must be
done again.

Convert Excel to a PDF or maybe an Image file. Preserves
original Excel formatting but without any relationship to
formatting used in FrameMaker. Excel content exists in
FrameMaker within an anchored frame without the useful
features that a native FrameMaker table provides. Again
if Excel data is updated the conversion must be repeated.

Use FrameMaker's OLE ability to link to Excel as an
object. Has the advantage of preserving Excel formatting.
Though this is not helpful if you want formatting to be
seamless with your FrameMaker document's table and
paragraph styles. Excel content exists inside an anchored
frame not a table like the previous method. This method
does maintain a dynamic link to Excel, so subsequent
Excel updates can be refreshed in FrameMaker easily.
I don't think this method is useful on Unix platforms.

-------------------------------------------------------

Not so long ago I was faced with an Excel conversion
problem on behalf of a client. This client was preparing
the Financial statements for a large company's Annual
Report in Excel. There were around 50 to 60 tables with
up to a 100 figures per table. These figures were
subject to constant revision right up to the publication
deadline. Much of the Excel content was derived from a
Financial Management system with dynamic links to the
system from Excel. Excel could not be taken out of the
process. However they still wanted to use FrameMaker to
prepare the report for print.

The financial data was in one Excel Workbook with the
"tables" distributed across many worksheets. The
equivalent of one to two or three distinct tables
existing on each worksheet. They used Excel's formatting
features extensively. Font weight, Point size, alignment,
font color and cell shading. Cell borders were also used.
They practically laid out each "table" in Excel in the
fashion they wanted it to appear in print.

I was given the task of working out a way to extract the
Excel content as distinct tables, preserve the formatting
the client desired and have the content converted to
native FrameMaker tables. The method I arrived at
involves the use of Excel's style sheet system. The
client and I agreed on a style system to effectively
mark up the tables in Excel. We have styles to indicate
the start of a table, its heading and body rows etc. I
have created an Excel Add-In that scans the whole work
book looking for the styled cells that constitute a
table. The Add-In then creates a series of MIF documents
with a table for each Excel table found. The MIF document
results are based on a template file containing table
and paragraph formats of the clients choice.

The Add-In also has some extra features for controlling
column widths in the result for a consistent look. It is
also selective about how and which Excel formatting
characteristics are used. Style names are mapped to
Paragraph Format Tags so the template document gives
great control over the formatting in the result. Each
generated table is a native FrameMaker table with all
the advantages that go with that.

With one generated document per table the client then
imports each document into a master document by
reference. The auto generated tables are intermingled
with  a large amount of static content in the main
document.

Each table document is imported by reference which means
updates are easy. Every time the figures are revised,
the Excel Add-in is activated. This rebuilds all of the
table documents. Then "Update References" is used in
FrameMaker to refresh the document. None of the
sensitive figures have to be touched in FrameMaker.

The solution I have created is very specific for this
clients needs. I would like to ask users
whether a more general form of this tool would be
useful to FrameMaker users?

kind regards,
Bernard Harrison


** To unsubscribe, send a message to majordomo@omsys.com **
** with "unsubscribe framers" (no quotes) in the body.   **