I use a SQL view to output data to a csv file, then this VBA (thanks to capricorn1) to convert the CSV to Excel format:
Dim xlObj As Object, xlFile As String, csvFile
csvFile = "C:\xxx.csv"
xlFile = "C:\xxx.xls"
Set xlObj = CreateObject("excel.Application")
xlObj.activeworkbook.saveas xlFile, FileFormat:=-4143, CreateBackup:=False
xlObj.activeworkbook.Saved = True
This works fine except for dates: -
File QtyReceived LastRecDate QtyOrdered LastOrderDate
CSV 44 10/01/2011 14:14 12 11/01/2011 12:13
XLS 44 01/10/2011 14:14 12 01/11/2011 12:13
The dates look OK in CSV but get messed up in XL, does anyone have a solution?