ADODB only bringing down numeric values

[lynxbci]

Hi,
Have a simple code to bring down all the records from a separate spreadsheet.
One of the columna contains Numeric and Alphanumeric records, but it will not bring down the alphanumeric items, just get blank field.

To call the routine

GetData ThisWorkbook.Path & "\Stock Data.xlsx", "stock", "*", "", Sheets("Stock").Range("b2"), True, True

The routine

Public Sub GetData(SourceFile As Variant, SourceSheet As String, SourceFields As String, SourceRule As String, TargetRange As Range, Header As Boolean, UseHeaderRow As Boolean)

    Dim rsCon As Object
    Dim rsData As Object
    Dim szConnect As String
    Dim szSQL As String
    Dim lCount As Long
      
     ' Create the connection string.
    If Header = False Then
                    szConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                        "Data Source=" & SourceFile & ";" & _
                        "Extended Properties=""Excel 12.0;HDR=No"";"
        
    Else
                    szConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                        "Data Source=" & SourceFile & ";" & _
                        "Extended Properties=""Excel 12.0;HDR=Yes"";"
    End If
    

    ' worksheet level name or range
    szSQL = "SELECT " & SourceFields & " FROM [" & SourceSheet$ & "$]" & SourceRule & ";"
   

    On Error GoTo SomethingWrong

    Set rsCon = CreateObject("ADODB.Connection")
    Set rsData = CreateObject("ADODB.Recordset")

    rsCon.Open szConnect
    rsData.Open szSQL, rsCon, 0, 1, 1

    ' Check to make sure we received data and copy the data
    If Not rsData.EOF Then
        TargetRange.Cells(1, 1).CopyFromRecordset rsData
    Else
        MsgBox "No records returned from : " & SourceFile, vbCritical
    End If

    ' Clean up our Recordset object.
    rsData.Close
    Set rsData = Nothing
    rsCon.Close
    Set rsCon = Nothing
    
    
    
    
        
    Exit Sub

SomethingWrong:
    MsgBox "The file name, Sheet name or Range is invalid of : " & SourceFile, _
           vbExclamation, "Error"
    On Error GoTo 0

End Sub

Thank you for your help in advance

 

[RoryA]

Adding IMEX=1 to the extended properties may help as long as there's a mix of data types in the first few records but ADO isn't good with mixed types (since that's not possible in a proper database).