Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mystery Formats
My workbook has two sheets - one is the actual report and
the other is where the data is kept. The report contains a grid with times listed in the column and names across the top. Formulas fill in the grid from the data on the other sheet. I use ADO to connect to a database, get the data for the time range, dump the data to the one worksheet, then the formulas automatically update the report. TADA! Works fine... EXCEPT.... When the data is copied into the first worksheet, the formats for the times in the report change. My code does nothing to change the formats on the report sheet. It just happens, I assume because the formulas refer to that other data. My solution has been to add code to put the formats BACK where I had them, but it seems pointless to have to do that. Does anyone know what I'm talking about and how to fix it? tod |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mystery Formats
I recorded a macro while importing data from Access. I was having th
same problem with the formatting. Everytime the macro put the dat from Access into the cell it changed the formatting. I looked throug the code to see what i could change. I scrolled down t .preserveformatting = false, and changed it to true. It preserves th formatting you set in Excel, not the data coming in. For example, i you are putting new data in column C, and you format column C to b currency. Setting .preserveformatting = true, will keep it currency. Hope this helps, Range("A1").Activate With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _ "ODBC;DSN=MS Access Database;DBQ=C:\Documents an Settings\mn\My Documents\Tass2.mdb;DefaultDir=C:\Documents an Settings\mn\My Docum" _ ), Array("ents;DriverId=25;FIL=M Access;MaxBufferSize=2048;PageTimeout=5;")), _ Destination:=Range("A1")) .CommandText = Array( _ "SELECT ProductPerformance.ProductReference ProductPerformance.Date, ProductPerformance.RateOfReturn ProductPerformance.NAV, ProductPerformance.EstimatedAssets" & Chr(13) "" & Chr(10) & "FROM `C:\Documents and Settings\mn\My " _ , _ "Documents\Tass2`.ProductPerformance ProductPerformance" Chr(13) & "" & Chr(10) & "WHERE (ProductPerformance.ProductReference= & Manager & ")" & Chr(13) & "" & Chr(10) & "ORDER B ProductPerformance.Date" _ ) .Name = "Query from MS Access Database_27" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = True .SaveData = False .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .Refresh BackgroundQuery:=False End Wit -- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Mystery Files | Excel Discussion (Misc queries) | |||
Macro Mystery | Excel Discussion (Misc queries) | |||
One of lifes little mystery? | New Users to Excel | |||
One of lifes little mystery? | Excel Worksheet Functions | |||
MYSTERY!! | Excel Discussion (Misc queries) |