Excel VBA - RecordSet To Array

Harry Flashman

Active Member
Joined
May 1, 2011
Messages
342
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:
Code:
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.
 
Last edited:

Forum statistics

Threads
1,082,316
Messages
5,364,505
Members
400,803
Latest member
Niyetkhan

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top