[Microsoft[[ODBC Excel Driver]Invalid datetime format on column number 1

Question

I am attempting to pull a table from an existing excel workbook, into a new workbook.  I have created a simple excel file, with 2 columns (TimeStamp and HourlyTonnage).  I have named the range 'Tonnage' and saved the file.  I have created a data source in the control panel for the file.

In the new file, i select Data -> From Other Sources -> From Microsoft Query, choose my database, the HourlyTonnage table shows up, and I add it to the query.  When I finish the wizard, and attempt to Return Data to Microsoft Excel, it generates the above error.

Column 1 in my source table is a DATE field.  What would be causing this error?

Answer 1

Hi

 

Could you have a try with this one ?

 

Sub getData()

Const adOpenStatic = 3

Const adLockOptimistic = 3

Const adCmdText = &H1

Dim conn As Object

Dim rs As Object

Dim strCon As String

Dim strSQL As String

Dim x As Long

'change the path from ThisWorkbook.FullName to your other workbook

Set conn = CreateObject("ADODB.Connection")

Set rs = CreateObject("ADODB.Recordset")

strCon = "Provider=Microsoft.Jet.OLEDB.4.0;" & _

    "Data Source=" & ThisWorkbook.FullName & ";" & _

    "Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1;ReadOnly=False;Format=xls"" "

' Extended properties for Excel 2000/2002/2003 : Excel 8.0

' Extended properties for Excel 97 :Excel 97

' HDR header row ye sor no

' IMEX   mixed data type

conn.Open strCon

'Change the name in [  ]  if incorrect

strSQL = "SELECT [TimeStamp], [HourlyTonnage] FROM [Tonnage] "

rs.Open strSQL, conn, adOpenStatic, adLockOptimistic, adCmdText

Worksheets("notcompleted").Range("A1").CurrentRegion.Clear

For x = 0 To rs.Fields.Count - 1

    Worksheets("notcompleted").Range("A1").Offset(0, x) = rs.Fields(x).Name

Next x

Worksheets("notcompleted").Range("A2").CopyFromRecordset rs

rs.Close

conn.Close

Set rs = Nothing

Set conn = Nothing

End Sub

regards

JY

I am attempting to use ODBC, not OLEDB.  The reasons for this are that ultimately I will be pulling from a 3rd party software that only offers ODBC.  This may just be a problem with the way the excel ODBC driver works, and won't impact it in my final production environment - it was just something I came across and figured I should report it.

Answer 2

Hi

Could you have a try with this one ?

Sub getData()

Const adOpenStatic = 3

Const adLockOptimistic = 3

Const adCmdText = &H1

Dim conn As Object

Dim rs As Object

Dim strCon As String

Dim strSQL As String

Dim x As Long

'change the path from ThisWorkbook.FullName to your other workbook

Set conn = CreateObject("ADODB.Connection")

Set rs = CreateObject("ADODB.Recordset")

strCon = "Provider=Microsoft.Jet.OLEDB.4.0;" & _

    "Data Source=" & ThisWorkbook.FullName & ";" & _

    "Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1;ReadOnly=False;Format=xls"" "

' Extended properties for Excel 2000/2002/2003 : Excel 8.0

' Extended properties for Excel 97 :Excel 97

' HDR header row ye sor no

' IMEX   mixed data type

conn.Open strCon

'Change the name in [  ]  if incorrect

strSQL = "SELECT [TimeStamp], [HourlyTonnage] FROM [Tonnage] "

rs.Open strSQL, conn, adOpenStatic, adLockOptimistic, adCmdText

Worksheets("notcompleted").Range("A1").CurrentRegion.Clear

For x = 0 To rs.Fields.Count - 1

    Worksheets("notcompleted").Range("A1").Offset(0, x) = rs.Fields(x).Name

Next x

Worksheets("notcompleted").Range("A2").CopyFromRecordset rs

rs.Close

conn.Close

Set rs = Nothing

Set conn = Nothing

End Sub

regards

JY

Answer 3

Hi,

Not sure but. Try ( in a copy) to insert a new date value in all cells. See if it works.

Using a ADO query, the standard behaviour is to use the most appearing value data type an to ignore others unless you specify that a column contains mixed data type, i.e. integer and string.

Maybe there is a value which looks but is not a real date ( ...?)

HTH

Regards

JY

I've tried setting the date column to number format, and it brings it in fine.  As soon as I change the format back to a data format (of any variety), the error returns.  I just wish this error provided SOME sort of information of what entry it is talking about.

I have tried using the ODBC Tracing feature, and looked at the generated SQL.LOG, but it doesn't shed any light on the problem.

Answer 4

Hi,

Not sure but. Try ( in a copy) to insert a new date value in all cells. See if it works.

Using a ADO query, the standard behaviour is to use the most appearing value data type an to ignore others unless you specify that a column contains mixed data type, i.e. integer and string.

Maybe there is a value which looks but is not a real date ( ...?)

HTH

Regards

JY