I have a pretty large source Excel file where its first column Code has a lot of numeric data with a minority of cells with text.

My issue is that IMEX=1 setting in the connection doesn’t work as expected, and I still found that all the few text cells are nullified when stored in rst as the major datatype is numeric.

So, what am I missing here to make all the records in the column Code be treated as text when stored in rst regardless of the dominant numeric datatype?

Sub RetrieveDataUsingADO()
    Dim strFile As String
    Dim strConnect As String
    Dim strSQL As String
    Dim cnn As New ADODB.Connection
    Dim rst As New ADODB.Recordset

    strFile = ThisWorkbook.Path & "/Data.xlsx"

    strConnect = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=""" & strFile & _
                """;Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1"";"
    
    cnn.Open ConnectionString:=strConnect
    
    strSQL = "SELECT [Code] FROM [Payments$]"

    rst.Open strSQL, cnn, adOpenKeyset, adLockReadOnly

    Do Until rst.EOF
        Debug.Print CStr(rst.Fields("Code").Value)
        rst.MoveNext
    Loop

    rst.Close
    cnn.Close
End Sub