none
Odd behaviour when using ADO to query worksheet from within that workbook RRS feed

  • Question

  • I have a table of data in a worksheet. It's over 20,000 records. One of the columns has a mix of numeric and non-numeric values, like this:

    Material

    123

    3212

    T32

    453

    U-VW3221

    From within that same workbook I am using ADODB to connect to the worksheet and return records from the Material column. Problem is, it only returns the numeric values, as if the non-numeric ones are not even there. 

    My connectionstring is like this:

    Dim cn As New ADODB.Connection
        Dim rs As New ADODB.Recordset
        Dim strSQL As String
        
        strSQL = "SELECT Material from [Data$]"
        cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\AL - MaterialMaster r(3).xlsm;Extended Properties=Excel 12.0 Macro;"
        rs.Open strSQL, cn
        

    When I iterate this recordset I see only the numeric values.  

    Any help?

    tod

    Friday, November 14, 2014 10:56 PM

Answers

  • Hi Tod & Andreas,

    To use multiple properties in Extended Properties section, we need to use double quotation marks. Here is the code working for me:

    Sub Test()
      Dim cn As New ADODB.Connection
      Dim rs As New ADODB.Recordset
      Dim strSQL As String
      Dim Data
    
      strSQL = "SELECT * from [sheet1$]"
      cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0 Macro;IMEX=1;"""
      rs.Open strSQL, cn
      
      Worksheets("Sheet2").Range("A1").CopyFromRecordset rs
        
    End Sub

    Also here is article about using ADO in VBA:

    How To Use ADO with Excel Data from Visual Basic or VBA

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Monday, November 17, 2014 5:48 AM
    Moderator

All replies

  • That can happen with mixed data. Seems more likely to be a problem if the first value in the field is a number followed by any text than if text in the first followed by any numbers.

    Try adding IMEX= 1 to your extended properties. 

    Saturday, November 15, 2014 11:41 AM
    Moderator
  • Try adding IMEX= 1 to your extended properties. 

    I can reproduce the behavior with this code:

    Sub Test()
      Dim cn As New ADODB.Connection
      Dim rs As New ADODB.Recordset
      Dim strSQL As String
      Dim Data
    
      strSQL = "SELECT Material from [Data$]"
      cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=" & ThisWorkbook.Path & "\MaterialMaster r(3).xlsm;" & _
        "Extended Properties=Excel 12.0 Macro"
      rs.Open strSQL, cn
      Data = rs.GetRows
      rs.Close
      cn.Close
      
      Data = Transpose(Data)
      Range("A1").Resize(UBound(Data)+1) = Data
    End Sub
    
    Private Function Transpose(Arr As Variant) As Variant
      'Return the transposed matrix of Arr
      Dim Result()
      Dim X As Long, y As Long
      ReDim Result(LBound(Arr, 2) To UBound(Arr, 2), LBound(Arr) To UBound(Arr))
      For X = LBound(Arr, 2) To UBound(Arr, 2)
        For y = LBound(Arr) To UBound(Arr)
          Result(X, y) = Arr(y, X)
        Next
      Next
      Transpose = Result
    End Function

    But when I change cn.Open to

      cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=" & ThisWorkbook.Path & "\MaterialMaster r(3).xlsm;" & _
        "Extended Properties=Excel 12.0 Macro;IMEX=1"
    I get a RTE -2147467259 "Installable ISAM not found." Did I made a mistake?

    Andreas.


    Sunday, November 16, 2014 12:46 PM
  • Hi Tod & Andreas,

    To use multiple properties in Extended Properties section, we need to use double quotation marks. Here is the code working for me:

    Sub Test()
      Dim cn As New ADODB.Connection
      Dim rs As New ADODB.Recordset
      Dim strSQL As String
      Dim Data
    
      strSQL = "SELECT * from [sheet1$]"
      cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & ";Extended Properties=""Excel 12.0 Macro;IMEX=1;"""
      rs.Open strSQL, cn
      
      Worksheets("Sheet2").Range("A1").CopyFromRecordset rs
        
    End Sub

    Also here is article about using ADO in VBA:

    How To Use ADO with Excel Data from Visual Basic or VBA

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Monday, November 17, 2014 5:48 AM
    Moderator
  • To use multiple properties in Extended Properties section, we need to use double quotation marks. Here is the code working for me:

    Works for me too.

    An interesting part is that CopyFromRecordset writes all values as strings into the sheet, but when I use rs.GetRows I get doubles and strings mixed as in the original data... weird.

    Andreas.

    Monday, November 17, 2014 8:15 AM
  • Thanx. This is very good information, and I learned a few more things in the process.
    Thursday, November 20, 2014 4:02 AM