Have you tried SET DATE AMERICAN before doing the export?
Having said that, this works OK for me:
oWorkBook = oExcel.WorkBooks.Add()
oExcel.DisplayAlerts = .F. && avoid dialog boxes
oSheet = oWorkBook.Worksheets.Add()
oConn = NewObject('ADODB.Connection')
oRs = NewObject('adodb.recordset')
cPath = EVL(tcPath, SYS(5)+SYS(2003))
cConnection_String = "Provider=vfpoledb.1;Data Source="+cPath
cConnection_String =cConnection_String +";Collating Sequence=general;"
oConn.ConnectionString=cConnection_String
cSQL="SELECT * FROM " + cSource
cConnection_String = "Provider=vfpoledb.1;Data Source="+cPath+';'
oQryTable = oSheet.QueryTables.Add('OLEDB;' +
cConnection_String,oSheet.Range('A1'),cSQL)
*** This next can barf if too many records or similar, so cater for that.
lError = .F.
TRY
oQryTable.REFRESH(.F.)
CATCH TO oErr
lError = .T.
ENDTRY
Alan Bourke wrote:
> I'm automating Excel with VFP9, using ADO to populate rows in a
> spreadsheet,as detailed here:
> http://fox.wikis.com/wc.dll?Wiki~ExcelAutomation~VFP
>
> Which all fine apart from the usual - bloody dates, or more specifically
> datetimes. I'm running a SELECT on a VFP table similar to this:
>
> VFP2Excel(_samples+'data\testdata.dbc','select * from
> employee',.ActiveSheet.Range('A10'))
>
> One of the VFP fields is DateTime, but it was going into Excel in US
> format, i.e. month first. Nothing I could do with cell formats, custom
> or otherwise, would change it. I can't see anything in the ADO
> documentation about regionalisation.
>
> So I gave up and decided to put the date part in one column and the time
> in another. But this *still* puts the date in with the wrong format.
>
> Has anyone who doesn't work with US-format dates ever done this ?
>
>
>
>
--
Cheers
============
Brian Abbott
============
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message:
http://leafe.com/archives/byMID/profox/[email protected]
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
