Mystery Formats

T

Tod

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
 
M

mnuge

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(Array( _
"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
 
Top