Excel VBA - RecordSet To Array

[Harry Flashman]

I have an SQL Query which returns a subset of data from one worksheet to another worksheet. I was hoping that instead of returning the data to a worksheet I might be able to return the data to an array instead.

Here is my query:

Dim sel As New cls_Selected

Sub Data_Ext_From_Excel()

'Application.ScreenUpdating = False
Dim ws As Worksheet
Dim MyConnect As String
Dim MyRecordSet As ADODB.Recordset
Dim MySQL As String
Dim fName As String, fPath As String, dSource As String

fName = ThisWorkbook.Name
fPath = ThisWorkbook.Path
dSource = fPath & "\" & fName

MyConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
            "Data Source=" & dSource & ";" & _
            "Extended Properties=Excel 12.0"
            
Dim strCat As String
Dim strGroup As String

strCat = sel.Category ' Selected member from ListBox
strGroup = sel.Group ' Selected member from ListBox

MySQL = "SELECT [Category], [Group], [Item], [Letter] FROM [Data$] " & _
"WHERE [Category] = " & "'" & strCat & "'" & " AND [Group] = " & "'" & strGroup & "'"
Debug.Print MySQL
Set MyRecordSet = New ADODB.Recordset

    MyRecordSet.Open MySQL, MyConnect, adOpenForwardOnly, adLockReadOnly
   
    'clear existing data on the sheet
    Set ws = Sheets("Data_Fetch")
    ws.Cells.Clear
  
    'create field headers
    i = 0
    With ws.Range("A1")
       For Each fld In MyRecordSet.Fields
           .Offset(0, i).Value = fld.Name
           i = i + 1
       Next fld
    End With
     
    'transfer data to Excel
    Dim arr

    ws.Range("A2").CopyFromRecordset MyRecordSet
    ' The next line returns an error message
    ' Error = Either BOF or EOF is true, or the current record has been deleted.
    ' arr = MyRecordSet.GetRows


   
    ' Close the connection
    MyRecordSet.Close

    Set MyRecordSet = Nothing

    
End Sub

I have done some googling and I found code that uses the .GetRows property, but this throws up an error message in my code.

Can anyone see where I going wrong? Is there a way that I can pass the recordset straight to an array rather than outputting the data to a worksheet.

 

[Harry Flashman]

Okay I figured it out

Dim arr
 MyRecordSet.MoveFirst
 arr = MyRecordSet.GetRows

 

[coocooworld2]

Thank you for posting solution to your question. It helps me greatly!