Excel formatting saved in a database?
 

Hi
Our CEO has come up with an 'idea'
He wants to save the formatting of a sheet and export it to a database.
Then, later - retrieve this formatting data and read in data from our 4GL
application so it is formatted in the desired manner (Excel already reads in
the data from 4GL via an addin).
This is so the users only have to push 'one button' to save their formatting
and 'one button' to get it back.

Now this sounded fantastically complicated as who knows what formatting they
will have applied to cells/ranges/sheets and the interrogation would be
excessive. So we thought we would just save a *.xls or *.xlt of the sheet
with formatting and then read the file back in and save it as a binary
object in the database (SQL Server or Oracle). However, I don't see that
there is a way for Excel to be able to read in an XLS/XLT file and write it
to a database. It just deals in TextStreams?

Is this proposal just not possible with Excel and its VBA?

Thanks

Paul

Excel formatting saved in a database?
 

You would have to query the DB (using ADO is one way) to retrieve the blob.
Reconstruct it on disk as a file. Then XL could open it.
VBA can accomplish that.

NickHK

"Paul C" bl...
Hi
Our CEO has come up with an 'idea'
He wants to save the formatting of a sheet and export it to a database.
Then, later - retrieve this formatting data and read in data from our 4GL
application so it is formatted in the desired manner (Excel already reads
in the data from 4GL via an addin).
This is so the users only have to push 'one button' to save their
formatting and 'one button' to get it back.

Now this sounded fantastically complicated as who knows what formatting
they will have applied to cells/ranges/sheets and the interrogation would
be excessive. So we thought we would just save a *.xls or *.xlt of the
sheet with formatting and then read the file back in and save it as a
binary object in the database (SQL Server or Oracle). However, I don't see
that there is a way for Excel to be able to read in an XLS/XLT file and
write it to a database. It just deals in TextStreams?

Is this proposal just not possible with Excel and its VBA?

Thanks

Paul

Excel formatting saved in a database?
 

If you're already reading data from the 4GL then why don't you have an
Excel Template (and empty Workbook) and keep the formatting in that?

The way I see it the Template would have VBA macros that would analyze
the data and add formatting, that way the number of rows returned from
the 4GL can vary.

As to whether it's possible. If it's a matter of saving column widths,
title fonts and simple stuff, why not?

Regarding the excessive iteration, you can get around the slowness by
doing the formatting 50 rows at a time. Keep in mind the selection does
not have to be contigous.

BTW, it is absolutely possible to write the data from a Worksheet to an
xml file - so later it can be transformed into XSLT.

In fact, you can save the data as xml and use Excel (2003 or later) to
transform and format the data. That is, Excel has some built in
transformation/formatting stuff built in.