CODEWITHSUNDEEP
sqlexcelvbaado
brutus17
brutus17

Reputation: 11

ADODB Recordset return Null values for hidden columns in Excel

I'm trying to retrieve data from an Excel worksheet using ADODB.connection and .recordset objects. Generally, the whole procedure works without any errors, however, when a column/field in the source excel is hidden, the recordset query produce only "Null" values for that specific hidden column.

The hidden column is apparently visible by the connection/recordset because there is no SQL error due to unrecognized field and I can even get rs.Field(i).Name value of this hidden column. But somehow ADO is not able to get the values of hidden columns.

The Null values are returned when using rs.copyfromrecordset method, looping through all records for each field, or checking in the immediate window.

I do not see any properties of the connection/recordset that would handle this situation. Is that a limitation/bug of the ADO?

Edit.

For the ConnectionString, I use standard ACE Provider with Read mode and Properties Excel XML or Excel Macro.

cn.ConnectionString = _
    "Provider=Microsoft.ACE.OLEDB.12.0;" & _
    "Mode='Read';" & _
    "Data Source=" & fullFilePath & ";" & _
    "Extended Properties='Excel 12.0 Xml;HDR=YES';"
cn.Open

For the recordset, I use following setting.

With rs
    .ActiveConnection = cn
    .CursonType = adOpenStatic
    .Source = SQL_Query
    .Open
End with

Would appreciate any help on that. Thanks!

Upvotes: 1

Views: 149

Answers (1)

brutus17
brutus17

Reputation: 11

The problem was the values in the affected columns, as suggested by Tim. For some reason, the Microsoft.ACE.OLEDB provider interpreted the column as "Date" type and all the values which were not formatted in the "date" format were erased (in my understanding). The column did not contain any date values in any of the cells so it's still unclear why the column type was interpretted that way. The solution for me was to insert " ' " in the first row cell so that the column was recognized as Text type.

Upvotes: 0

Related Questions