Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Upload Excel formats to database
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Upload Excel formats to database
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Upload Excel formats to database
Maybe you can build an Excel-AddIn which creates a separate menu (while
opening) and listing down all the new functionalities mentioned here. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Upload Excel formats to database
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Upload Excel formats to database
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
database formats | Excel Programming | |||
database query to upload a dbf file | Excel Programming | |||
how-to use Excel to upload records to a MySQL database? | Excel Programming | |||
how to upload csv to excel? | Excel Programming | |||
Upload Excel data into online database | Excel Programming |