A
aa
How to get data from worksheet using SQL in the activeworksheet
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
A
Arvi Laanemets
Hi
Define the source table (Header row included!) as named range, and save the
workbook.
Create a query using ODBC-driver for Excel, where the source workbook (can
be any available workbook) is source, and named range in source workbook is
source table.
A hint: All fields you used in query (WHERE, ORDER etc, clauses included)
MUST be presented in output table - need you them there or not! And your
source table must contain all data needed for filtering or ordering - you
can use only 4 aggregate functions in query from Excel tables.
Define the source table (Header row included!) as named range, and save the
workbook.
Create a query using ODBC-driver for Excel, where the source workbook (can
be any available workbook) is source, and named range in source workbook is
source table.
A hint: All fields you used in query (WHERE, ORDER etc, clauses included)
MUST be presented in output table - need you them there or not! And your
source table must contain all data needed for filtering or ordering - you
can use only 4 aggregate functions in query from Excel tables.
B
Bob Phillips
You can use ADO. Here is a simple example
Public Sub QueryWorksheet()
Dim oRS As Object 'ADODB.Recordset
Dim sConnParams As String
Dim sSQL As String
sConnParams = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\myTest\volker1.xls;" & _
"Extended Properties=Excel 8.0;"
sSQL = "SELECT * FROM [Sheet1$];"
Set oRS = CreateObject("ADODB.Recordset")
oRS.Open sSQL, sConnParams, 0, 1, 1
'adOpenForwardOnly, adLockReadOnly , adCmdText
If Not oRS.EOF Then
Worksheets("Sheet1").Range("A1").CopyFromRecordset oRS
Else
MsgBox "No records found.", vbCritical
End If
oRS.Close
Set oRS = Nothing
End Sub
--
HTH
Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
Public Sub QueryWorksheet()
Dim oRS As Object 'ADODB.Recordset
Dim sConnParams As String
Dim sSQL As String
sConnParams = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\myTest\volker1.xls;" & _
"Extended Properties=Excel 8.0;"
sSQL = "SELECT * FROM [Sheet1$];"
Set oRS = CreateObject("ADODB.Recordset")
oRS.Open sSQL, sConnParams, 0, 1, 1
'adOpenForwardOnly, adLockReadOnly , adCmdText
If Not oRS.EOF Then
Worksheets("Sheet1").Range("A1").CopyFromRecordset oRS
Else
MsgBox "No records found.", vbCritical
End If
oRS.Close
Set oRS = Nothing
End Sub
--
HTH
Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
O
onedaywhen
It is not recommended to query an open workbook. Save the worksheet as
a new xls file. You can then query the new workbook using the data
access technology of your choice. The popular choices for Excel are
ADO (if you want to write VBA code and have greater control) and MS
Query (if you like a GUI interface and less control).
a new xls file. You can then query the new workbook using the data
access technology of your choice. The popular choices for Excel are
ADO (if you want to write VBA code and have greater control) and MS
Query (if you like a GUI interface and less control).