ADO query of date value in Excel returns string as formatted in worksheet

Question

I'm trying to pull data from an Excel sheet using an ADO query. However, date values are being returned the way they're formatted on the worksheet, rather than the actual date value. For example, the value 8/12/1929 is formatted as 8/12/29, so the query is returning the string "8/12/29". This makes it hard to determine what the correct date is based on the recordset data alone, as the year could also be 2029 in this case.

Here's the code for the ADO query:

Function WorksheetRecordset(workbookPath As String, sheetName As String) As ADODB.Recordset

Dim objconnection As New ADODB.Connection
Dim objrecordset As New ADODB.Recordset


'On Error GoTo errHandler

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H1

objconnection.CommandTimeout = 99999999

objconnection.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Data Source=" & workbookPath & ";" & _
        "Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1"";"

objrecordset.Open "Select * FROM [" & sheetName & "$]", _
    objconnection, adOpenStatic, adLockOptimistic, adCmdText

If objrecordset.EOF Then
    Set WorksheetRecordset = Nothing
    Exit Function
End If

Set WorksheetRecordset = objrecordset
On Error GoTo 0
Exit Function


errHandler:
Set WorksheetRecordset = Nothing
On Error GoTo 0

End Function

I'm fetching the value by using, e.g.

Sub getValue(rs as ADODB.Recordset)

Debug.Print rs.Fields(0).Value

End Sub

Part of the problem might be that the date values don't start until after several rows of text, so maybe when ADO detects the field type as text it only fetches the visible formatted value. Is there a way to retrieve the actual date value?

EDIT: Just realized that this is similar to this SO question that I previously asked: ADO is truncating Excel data. But I didn't get a satisfactory answer from that one, so I'll ask this one anyways.

I think the problem is your `SELECT *`. Lets say that the header (first cell) of the column is "myDate". Try using `SELECT CDATE([myDate]) FROM [" & sheetName & "$]` to see if the problem persists — kb_sou, Dec 28 '13 at 00:47
This problem occurs because you specified `IMEX=1` in you connection string, causing the data to be interpreted as a string (see [here](http://www.connectionstrings.com/ace-oledb-12-0/treating-data-as-text/)) — kb_sou, Dec 28 '13 at 00:51
@kbsou, the `cdate([myDate])` approach was successful at getting the actual date. But I have 58 fields to select in total; any ideas on how I could use this and still get the rest of the fields without having to build a monster SQL statement? In the meantime, I'll try removing the `IMEX` parameter and see what happens. — sigil, Dec 28 '13 at 00:59
@kbsou, removing IMEX is not going to work, because I need to get the contents of the text rows above the date rows (the 3rd row has the actual field names), and if I let ADO guess the datatype, it will return NULL for the rows above the date rows. I may have to do two separate queries, one for everything and one with the `cdate()`, and then merge the results later on in the code. — sigil, Dec 28 '13 at 01:23

score 0 · Answer 1 · edited May 23 '17 at 10:25

Since you have 58 fields I think that the best thing is to build a string running the following code on the workbook that contains the data:

Dim rng as Range
Dim val as Variant, txt as String
Set rng = Worksheets("sheetName").Range("A3:BF3")
For Each val In rng.Value
    txt = txt & "[" & val & "], "
Next val
Debug.Print txt

Then you copy the text from the Immediate window and paste in your code, like this:

Dim strFields as String, strSQL as String
strFields = 'Paste the text here
'Note the FROM clause modification 
strSQL = "SELECT CDATE([myDate]), " & strFields & " FROM [" & sheetName & "$A3:BF]"
'...
objrecordset.Open strSQL, _
objconnection, adOpenStatic, adLockOptimistic, adCmdText

Note that the FROM clause has the form [sheet$A3:BF]. This specify the third row as the first row that contains data. More details in this question or in this link

ADO query of date value in Excel returns string as formatted in worksheet

1 Answers1