Using SQL to extract results

[tiredofit]

I have a workbook with two worksheets, Data and Results.

Data contains data from cell J11 to K17 and I want to summarise it.

This code gets data from Data and pastes the results onto Results:

Option Explicit

Sub GetData()
 
        Dim cn As ADODB.Connection
    
        Dim rs As ADODB.Recordset
          
        Set cn = New ADODB.Connection
          
        Set rs = New ADODB.Recordset
          
        rs.CursorLocation = adUseServer
      
        Dim strcon As String
      
        strcon = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
                      "Data Source=" & ThisWorkbook.FullName & ";" & _
                      "Extended Properties=""Excel 12.0 Macro;" & _
                      "HDR=Yes;" & _
                      "IMEX=1;" & _
                      "MaxScanRows=0"";"
      
        cn.Open ConnectionString:=strcon
      
        Dim strSQL As String
      
        strSQL = "SELECT [Data$].[Name], Sum([Data$].[Num]) AS [Metric]" & _
                        "FROM [Data$]" & _
                        "GROUP BY [Data$].[Name]"
        
        rs.Open Source:=strSQL, ActiveConnection:=cn
      
        Results.Cells(1, 1).CopyFromRecordset rs
                      
        rs.Close
        cn.Close
      
        Set rs = Nothing
        Set cn = Nothing
      
        Set cn = New ADODB.Connection
        Set rs = New ADODB.Recordset
      
End Sub

What I don't understand is how the code knows where the data is?

These lines:

    "Data Source=" & ThisWorkbook.FullName & ";" & _

and

    strSQL = "SELECT [Data$].[Name], Sum([Data$].[Num]) AS [Metric]" & _
                    "FROM [Data$]" & _
                     "GROUP BY [Data$].[Name]"

specifies the data is within the workbook (as opoosed to an external database or spreadsheet) but it doesn't refer to a range or cell on the sheet Data.

I experimented by copying the data on Data to cell B21 to C27 and changed the metric values to be 10 times the original.

The code still "used" the values in cells J11.

I moved the data in cells B21 to cell B1.

The code now "used" the values in cells B1.

So it appears the code reads the data from the highest row.

Is there an explicit way to define the range?

Thanks

 

[RoryA]

Yes, you'd use [Data$B21:C27] for example. You shouldn't use ADO on open workbooks due to memory leaks though. (and it appears to be overkill for a data set that size)

 

[tiredofit]

Yes, you'd use [Data$B21:C27] for example. You shouldn't use ADO on open workbooks due to memory leaks though. (and it appears to be overkill for a data set that size)

Thanks.

It was a theoretical question re the size.

Re the open workbook probelm, I suppose it would be better to save the data in a separate workbook and refer to its location in:

"Data Source=" & "C:\mydatalocation\mydata.xlsx" & ";" & _

instead.