On 15 Mar, 12:36, Hiran wrote:
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