tiredofit
Well-known Member
- Joined
- Apr 11, 2013
- Messages
- 1,900
- Office Version
- 365
- 2019
- Platform
- Windows
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:
What I don't understand is how the code knows where the data is?
These lines:
and
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
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:
Code:
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:
Code:
"Data Source=" & ThisWorkbook.FullName & ";" & _
and
Code:
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