[SQL] Excel ADODB Fieldnames returning as null when data contains decimal?

I have 2 workbooks (WB1 and WB2). I am querying WB2 from WB1 with an ADODB connection.

When I query and print out my recordset, some of the fieldnames are NULL. The fieldnames that return NULL contain numbers with decimals, if I change those numbers to whole numbers, the fieldnames resolve to the correct name and not NULL. What is the cause of this and how do I correct it?

Dim conn As New ADODB.connection
Dim query, query1 As String
Dim ThisFile As String
Dim SourceFile As String
Dim rs As ADODB.Recordset


SourceFile = "[Excel 12.0;HDR=NO;DATABASE=" & Filename & "]"
Debug.Print SourceFile

ThisFile = strOneDriveLocalFilePath()
Debug.Print ThisFile
conn.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & ThisFile & ";Extended Properties=""Excel 12.0;HDR=YES;"";"

Set rs = conn.Execute("SELECT * FROM " & SourceFile & ".[Sheet3$]")


Dim fld As Field
For Each fld In rs.Fields

Debug.Print fld.Value
Next

conn.Close


I have tried all the combinations of HDR and IMEX without any luck.

If sheet 3 in WB2 is

A B C
1 4 3
2 8 5
3 12 7

The fieldnames print out as

A
B
C


If sheet 3 in WB2 is

A B C
1 4.2 3
2 8 5
3 12 7

The fieldnames print out as

A
Null
C


It also returns a null fieldname if the data was a date/time

Continue reading...