ADODB Recordset 256 Char Length Issue

Question

I am running below recordset code on 64 bit Windows machine with 32 bit excel and while data retrieval only 256 chars are getting retrieved.

This code is reading data from excel sheet "Test Plan" and it contains some column which contain data more then 256 char. Currently it is only retrieving 256 chars for those columns.

I need to retrieve whole data which any numbers of character in it.

I have tried below options for this problem:

1. I have tried by updating System Registry "TypeGuessRows" to 0, however it is not working on my machine.

2. I have changed column format to General.

   Sconnect = "Provider=MSDASQL.1;DSN=Excel Files;DBQ=" & DBPath & ";HDR=Yes';IMEX=1"
   
   conn.Open Sconnect
   
   Query = "Select * from [Test Plan$] where [Optimized Scenario No] = '" + OptimizedScenarioNo + "' and [Type] = 'Today' order by [Seq]"
           rsOptimizedScenarioCreation.Open Query, conn
   

Actual Result:

This code is reading data from excel sheet "Test Plan" and it contains some column which contain data more then 256 char. Currently it is only retrieving 256 chars for those columns.

Expected Result

I need to retrieve whole data which any numbers of character in it.

Answer 1

To be clear:
This is just a workaround, as you use Excel as data source for Excel ...

---

You may filter and sort your data source worksheet according to your SQL statement and use the remaining visible range for further purposes, e. g. to copy it to another sheet:

Private Sub CopyWithoutSQL()
    Dim ws As Worksheet
    Dim n As Long
    Dim OptimizedScenarioNo As String

    Set ws = ActiveWorkbook.Sheets("Test Plan")
    OptimizedScenarioNo = "whatever"

    ' switch all filters off
    If Not ws.AutoFilterMode Then ws.Rows(1).AutoFilter
    If ws.AutoFilter.FilterMode Then ws.ShowAllData

    ' filter according to SQL statement
    n = ws.Range("1:1").Find(What:="Optimized Scenario No", After:=Range("A1")).Column
    ws.UsedRange.AutoFilter Field:=n, Criteria1:="=" & OptimizedScenarioNo
    n = ws.Range("1:1").Find(What:="Type", After:=Range("A1")).Column
    ws.UsedRange.AutoFilter Field:=n, Criteria1:="Today"

    ' sort according to SQL statement
    n = ws.Range("1:1").Find(What:="Seq", After:=Range("A1")).Column
    With ws.Sort
        .SortFields.Clear
        .Header = xlYes
        .SortFields.Add key:=Columns(n), SortOn:=xlSortOnValues, Order:=xlAscending
        .SetRange ws.UsedRange
        .Orientation = xlTopToBottom
        .Apply
        .SortFields.Clear
    End With

    ' use the remaining visible range, e. g. copy it to somewhere
    ws.UsedRange.Copy ActiveWorkbook.Sheets(2).Range("A1")
End Sub