Access to Excel format

[garygoodguy]

Hi, I'm using the following code to pull in an Access cross-tab query t
Excel:

Option Explicit
Const TARGET_DB = "Forecast Mapping.accdb"

Sub Forecast()
'
' Forecast Macro
'
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim fld As ADODB.Field
Dim MyConn
Dim i As Long
Dim ShDest As Worksheet
Dim sSQL As String

Set ShDest = Sheets("GetData")


sSQL = "SELECT * FROM qryFTE WHERE SL ='"
Sheets("Start").Range("K41").Value & "'"

Set cnn = New ADODB.Connection
MyConn = ThisWorkbook.Path & Application.PathSeparator & TARGET_DB

With cnn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.Open MyConn
End With

Set rst = New ADODB.Recordset
rst.CursorLocation = adUseServer
rst.Open Source:=sSQL, ActiveConnection:=cnn, _
CursorType:=adOpenForwardOnly, LockType:=adLockOptimistic
_
Options:=adCmdText

'clear existing data on the sheet
ShDest.Activate
Range("A1").CurrentRegion.Clear

'create field headers
i = 0
With Range("A1")
For Each fld In rst2.Fields
.Offset(0, i).Value = fld.Name
i = i + 1
Next fld
End With

'transfer data to Excel
Range("A2").CopyFromRecordset rst

'Close the connection
rst.Close
cnn.Close
Set rst = Nothing


My problem is that the header that comes in changes the date format fro
d/mm/yyyy to m/dd/yyyy. The thing is, if I extract the qry manually fro
Access in excel it keeps the headers 'general', and thus the format i
correct.
So how can I keep the format 'gneral' in the code?

Thanks

 

[garygoodguy]

EDIT: Making it general will not help. I need to stop Access fro
switching the d and m format around. Does anyone know why this i
happening and how I can stop it? Thanks.

 

[GS]

EDIT: Making it general will not help. I need to stop Access from

switching the d and m format around. Does anyone know why this is
happening and how I can stop it? Thanks

The formatting has nothing to do with Acces, per se, but more the way
Excel handles dates. For example, dates in Win7 are by default
"dd/mm/yyyy", but in XP it's "mm/dd/yyyy". When you open a file in Win7
the dates display in that OS's default system date format. Open the
same file in XP and the dates display in that OS's default system date
format.

Have you tried formatting the date col before (or after) dumping the
data in?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion