Upload Excel formats to database

[Hiran]

Hi clever people, I need some outside-the-box thinking here!

This bit I can do: upload the values or formulas in a cell range to a
database, by cell reference, so it can be downloaded to any workbook.
Normally I have the formats/conditional formats for the destination
range already in place. I successfully use this techniqe a lot in a
variety of common scenarios and is quick.

Now here is the next level I want to take it to - to upload the
formats also, so that the formats also can be served centrally. The
way-forward that comes to mind is to upload the various formatting
properties, as I do the values. But there are so many - eg. interior
color, font name, size, borders x 4, conditional formatting. ie,
that's some 10-20 times the volume of the values/formulas. In short,
bulky.

Is there a better, more elegant, way?

Targeting Excel 2003 mainly.

Thanks,
Hiran

 

[Hiran]

A possible direction I'd like to research is ... XML Maps.

First here's my ideal workflow.

1. User formats a cell range, say like a financial statement, with
lovely formats/conditional formats.
2. Clicks button. Runs code to read the formats and upload to database
(I do this already with the values/formulas. see above)
3. On destination sheet (maybe waaaay across the world!) another user
clicks a button and, Hey Presto!, the same formats appear in the same
range.

Question: is XMLMaps useful here? Is there a small demo somewhere on
this?
Note: I'm primarily interested in a cell range, but if formatting the
whole sheet is easier (with XMLMaps or some other) that will do.

TIA,
Hiran

 

[Jacob Skaria]

Maybe you can build an Excel-AddIn which creates a separate menu (while
opening) and listing down all the new functionalities mentioned here.

 

[westtowers]

Hi clever people, I need some outside-the-box thinking here!

This bit I can do: upload the values or formulas in a cell range to a
database, by cell reference, so it can be downloaded to any workbook.
Normally I have the formats/conditional formats for the destination
range already in place. I successfully use this techniqe a lot in a
variety of common scenarios and is quick.

Now here is the next level I want to take it to - to upload the
formats also, so that the formats also can be served centrally. The
way-forward that comes to mind is to upload the various formatting
properties, as I do the values. But there are so many - eg. interior
color, font name, size, borders x 4, conditional formatting. ie,
that's some 10-20 times the volume of the values/formulas. In short,
bulky.

Is there a better, more elegant, way?

Targeting Excel 2003 mainly.

Thanks,
Hiran

Phillip London UK

This works for me
First you create some styles for the numeric and text data
you want in an empty workbook.

In my example I created 2 styles Num1 and String1
Styles allow you to create a collection of formats
saved under a single name.
They are found under Format Styles in the Excel menu
I saved my styles in an empty workbook ImportStyles.xls

Next I created an Access file with 4 fields
as follows

Numdata Numeric Double Type
holds the numeric data i.e 12.5

NumStyle Text type
holds the Style namefor numeric data i.e Num1

Textdata Text Type
holds the text data i.e Fred

TextStyle Text Type
holds the Style name for text data i.e String1

The code allows the user to select a workbook
you want to add the data and formats to from the
Access file

This code below is added to the Personal.xls workbook
in a Standard Module.
Check out Help for Personal.xls if you have not used
Personal.xls before.
Using Personal.xls means that the code is always
available whenever you open Excel


You will need to change the folders path i.e c:\Data
and also the worksheet cells i.e Cells(1,3) as you require

Sub GetDBInfo()
'set reference to DAO 3.6 library in Tools Reference
Dim m_db As DAO.Database
Dim recData As DAO.Recordset
Dim StrSql As String
Dim FileToOpen As String

Workbooks.Open "c:\data\ImportStyles.xls"

'user selects workbook to enter data and formats
FileToOpen = Application.GetOpenFilename()
Workbooks.Open FileToOpen

ActiveWorkbook.Styles.Merge Workbook:= Workbooks("ImportStyles.xls")

'get the data and formats
Set m_db = DAO.OpenDatabase("C:\data\Mydata.mdb")
StrSql = "SELECT * FROM TblAlldata"
Set recData = m_db.OpenRecordset(StrSql, dbOpenDynaset)
If recData.EOF And recData.bof Then
recData.Close
m_db.Close
Set m_db = Nothing
Exit Sub
End If
recData.MoveFirst
Cells(1, 3).Value = recData!Numdata
Cells(1, 3).Style = recData!NumStyle
Cells(1, 4).Value = recData!TextData
Cells(1, 4).Style = recData!TextStyle
recData.Close
m_db.Close
Set m_db = Nothing

Workbooks("ImportStyles.xls").Close False
End Sub

 

[Ran_dst]

That's great Phillip. I hadn't thought of the Styles feature. That
would save me having to upload detailed style settings, but instead
upload Style names.

I'd still be interested in researchig a solution with XML styles.
viz. Read an entire formatted grid into a style sheet > upload to db >
download to format entire grid in one whack?

Hiran
also London