How to run a complex SQL statement against Excel spreadsheet range?

[TCook]

Hey All,

I'm trying to run a SQL statement against an Excel range. For example, say
we have a range with the following:

Name Year Earnings
Company1 1999 $25,000
Company2 1999 $30,000
Company3 1999 $35,000
Company1 2000 $27,000
Company2 2000 $32,000
Company3 2000 $37,000
Company1 2001 $29,000
Company2 2001 $34,000
Company3 2001 $39,000

And want to do something like:

SELECT Name, Year, Earnings FROM Selection WHERE Name='Company1'

The ADO code samples that I found do not have complex SQL statements and,
more importantly, do not work.

Thanks in Advance,

TC

 

[Guest]

See if the QueryMaster file at Debra Dalgleish's website gets you pointed in
the right direction:
http://www.contextures.com/excelfiles.html#External

Use the Generic Excel List Query

Assuming you want to use the QueryMaster file as the data source, I inserted
a new worksheet (Sheet1) and copied your posted data to it.

Query Field Settings of Interest:
Data_Source: (enter the complete file path and file name )
SQL_Select: SELECT Name, Year, Earnings
SQL_From: "FROM `C:\ExcelQueries\QryMaster\QueryMaster`.`Sheet1$`"
SQL_Where: WHERE Name='Company1'

Note: The best approach is to create a named range for the data list and use
that name in the SQL_From field. But, if you refer to the sheet name followed
by a dollar sign (Sheet1$), the query will use the used range of the
referenced sheet and try to interpret it as a table.

Is that something you can work with?

Post back with any questions.

***********
Regards,
Ron

XL2002, WinXP

 

[TCook]

Hey Ron,

Thanks for the assistance. Very nice sample file.

However, the Excel file referenced in the sample isn't in the .zip. Do you
have that sample data Excel file?

In answer to your question about referencing the sheet, unfortunately, for
my use case scenario, this wouldn't work. I'm being handed a file that has
a singular spreadsheet containing all of the tables but no named ranges.
I'll probably have to create the names myself. Is there no way to work with
the 'CurrentRegion' object and pass that as the source range?

Thanks Again,

Todd

 

[Guest]

Todd
Sorry for the confusion...I didn't actually change the file at Debra's site.
I described what I did to test a same-workbook query.

Definitely

Not that I'm aware of. You'd need to create range names to be referenced by
the SQL.

I hope that helps

***********
Regards,
Ron

XL2002, WinXP

 

[TCook]

Hey Ron,

What about something like:


Dim oConn As ADODB.Connection
Dim oCmd As ADODB.Command
Dim oRS As ADODB.Recordset


' Open a connection to the Excel workbook.
Set oConn = New ADODB.Connection
oConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=Expenses.xls;" & _
"Extended Properties=""Excel 8.0;HDR=Yes;"";"
oConn.Open


' Create a command object and set its ActiveConnection
Set oCmd = New ADODB.Command
oCmd.ActiveConnection = oConn


' This SQL statement selects a cell range in a particular worksheet.
' oCmd.CommandText = "SELECT * from `Expenses$A2:C4`"


' This SQL statement selects a named cell range defined in the workbook.
oCmd.CommandText = "SELECT * from `Range1`"


' Open a recordset containing the worksheet data.
Set oRS = New ADODB.Recordset
oRS.CursorLocation = adUseServer
oRS.Open oCmd, , adOpenKeyset, adLockOptimistic


Debug.Print oRS.RecordCount


' Update last row
oRS.MoveLast
oRS(0).Value = -1
oRS.Update


' Add a new row
oRS.AddNew
oRS(0).Value = 7
oRS(1).Value = 8
oRS(2).Value = 9
oRS.Update


Debug.Print oRS.RecordCount


' Clean Up
Set oRS = Nothing
Set oCmd = Nothing
oConn.Close
Set oConn = Nothing


or like:

SELECT
Name, City
FROM [Sheet1$A1999]
IN 'C:\test.xls' 'EXCEL 8.0;'

Regards,

Todd

 

[Tim Williams]

Something like this has worked for me in the past.
GetRecords takes a Range object and a SQL statement (with "@" as a
placeholder for the temporary range name) and returns an ADO recordset with
the results.

Tim


'******************************
Sub Tester()

Dim rs As ADODB.Recordset
Dim iRow As Integer
Dim sSQL As String

sSQL = "select col2, count(col2) as v from @ group by col2"
Set rs = GetRecords(Selection, sSQL)



If Not rs Is Nothing Then
If Not rs.EOF And Not rs.BOF Then
ActiveSheet.Range("A20").CopyFromRecordset rs
Else
MsgBox "No records found"
End If
End If

End Sub



Function GetRecords(rng As Range, sSQL As String) As ADODB.Recordset
Const S_TEMP_TABLENAME As String = "SQLtempTable"
Dim oConn As New ADODB.Connection
Dim oRS As New ADODB.Recordset
Dim sPath

'name the selected range
On Error Resume Next
ActiveWorkbook.Names.Item(S_TEMP_TABLENAME).Delete
If Err.Number <> 0 Then Err.Clear

On Error GoTo haveError
ActiveWorkbook.Names.Add Name:=S_TEMP_TABLENAME, RefersToLocal:=rng

sPath = ThisWorkbook.Path & "\" & ThisWorkbook.Name

oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sPath & _
";Extended Properties=""Excel 8.0;HDR=Yes"""


oRS.Open Replace(sSQL, "@", S_TEMP_TABLENAME), oConn
Set GetRecords = oRS
Exit Function

haveError:
MsgBox Err.Description
Set GetRecords = Nothing

End Function
'************************************

 

[Guest]

Within the context of the QueryMaster.xls...there was very limited success.

For instance, referencing only a range (eg H21:J30) works if the range is on
the first sheet in the workbook. I couldn't get it to use a range reference
on a sheet that wasn't the first sheet. Named ranges worked as expected, no
matter where they were located. Referencing entire sheets (eg MySheet$) also
worked, but not with an associated range reference.

Of course, in specialized code, range references or ranges passed as
variables can be implemented, but the QueryMaster, being generic, is
designed to read text from the input cells and, consequenetly, is limited in
its ability to handle the other situations. I directed you to that file so
you could use it as a sandbox to play in.

***********
Regards,
Ron

XL2002, WinXP

Hey Ron,

What about something like:


Dim oConn As ADODB.Connection
Dim oCmd As ADODB.Command
Dim oRS As ADODB.Recordset


' Open a connection to the Excel workbook.
Set oConn = New ADODB.Connection
oConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=Expenses.xls;" & _
"Extended Properties=""Excel 8.0;HDR=Yes;"";"
oConn.Open


' Create a command object and set its ActiveConnection
Set oCmd = New ADODB.Command
oCmd.ActiveConnection = oConn


' This SQL statement selects a cell range in a particular worksheet.
' oCmd.CommandText = "SELECT * from `Expenses$A2:C4`"


' This SQL statement selects a named cell range defined in the workbook.
oCmd.CommandText = "SELECT * from `Range1`"


' Open a recordset containing the worksheet data.
Set oRS = New ADODB.Recordset
oRS.CursorLocation = adUseServer
oRS.Open oCmd, , adOpenKeyset, adLockOptimistic


Debug.Print oRS.RecordCount


' Update last row
oRS.MoveLast
oRS(0).Value = -1
oRS.Update


' Add a new row
oRS.AddNew
oRS(0).Value = 7
oRS(1).Value = 8
oRS(2).Value = 9
oRS.Update


Debug.Print oRS.RecordCount


' Clean Up
Set oRS = Nothing
Set oCmd = Nothing
oConn.Close
Set oConn = Nothing


or like:

SELECT
Name, City
FROM [Sheet1$A1999]
IN 'C:\test.xls' 'EXCEL 8.0;'

Regards,

Todd

Todd

Sorry for the confusion...I didn't actually change the file at Debra's
site.
I described what I did to test a same-workbook query.

Not that I'm aware of. You'd need to create range names to be referenced
by
the SQL.

I hope that helps

***********
Regards,
Ron

XL2002, WinXP

 

[TCook]

Hey Guys,

Thanks for all of the help!

Tim, I did get your sample working.

One final question guys:

Is there any advantage to using straight Excel such as:

With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=Excel
Files;DBQ=G:\Emptoris\QueryExcel.xls;DefaultDir=G:\Emptoris;DriverId=790;MaxBufferSize=2048;PageTimeout=5;"
_
, Destination:=Range("A1"))
.CommandText = Array( _
"SELECT MyTable.Earned, MyTable.Name, MyTable.Year" & Chr(13) & "" &
Chr(10) & "FROM `G:\Emptoris\QueryExcel`.MyTable MyTable" & Chr(13) & "" &
Chr(10) & "WHERE (MyTable.Name='Sean')" & Chr(13) & "" & Chr(10) & "ORDER BY
MyTable.Year" _
)
.Name = "Query from Excel Files"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With



It appears that the above Excel method bypasses the opening of an ADO
connection which sometimes requires credentials. Assuming that users will
already have the Excel file in question open and that the source data will
be an Excel workbook, would it be advisable to use straight Excel?

Regards,

Todd

 

[TCook]

Hey Guys,

Thanks for all of the help!

Tim, I did get your sample working.

One final question guys:

Is there any advantage to using straight Excel such as:

With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=Excel
Files;DBQ=G:\Emptoris\QueryExcel.xls;DefaultDir=G:\Emptoris;DriverId=790;MaxBufferSize=2048;PageTimeout=5;"
_
, Destination:=Range("A1"))
.CommandText = Array( _
"SELECT MyTable.Earned, MyTable.Name, MyTable.Year" & Chr(13) & "" &
Chr(10) & "FROM `G:\Emptoris\QueryExcel`.MyTable MyTable" & Chr(13) & "" &
Chr(10) & "WHERE (MyTable.Name='Sean')" & Chr(13) & "" & Chr(10) & "ORDER BY
MyTable.Year" _
)
.Name = "Query from Excel Files"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With



It appears that the above Excel method bypasses the opening of an ADO
connection which sometimes requires credentials. Assuming that users will
already have the Excel file in question open and that the source data will
be an Excel workbook, would it be advisable to use straight Excel?

Regards,

Todd

Within the context of the QueryMaster.xls...there was very limited
success.

For instance, referencing only a range (eg H21:J30) works if the range is
on
the first sheet in the workbook. I couldn't get it to use a range
reference
on a sheet that wasn't the first sheet. Named ranges worked as expected,
no
matter where they were located. Referencing entire sheets (eg MySheet$)
also
worked, but not with an associated range reference.

Of course, in specialized code, range references or ranges passed as
variables can be implemented, but the QueryMaster, being generic, is
designed to read text from the input cells and, consequenetly, is limited
in
its ability to handle the other situations. I directed you to that file
so
you could use it as a sandbox to play in.

***********
Regards,
Ron

XL2002, WinXP

Hey Ron,

What about something like:


Dim oConn As ADODB.Connection
Dim oCmd As ADODB.Command
Dim oRS As ADODB.Recordset


' Open a connection to the Excel workbook.
Set oConn = New ADODB.Connection
oConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=Expenses.xls;" & _
"Extended Properties=""Excel
8.0;HDR=Yes;"";"
oConn.Open


' Create a command object and set its ActiveConnection
Set oCmd = New ADODB.Command
oCmd.ActiveConnection = oConn


' This SQL statement selects a cell range in a particular worksheet.
' oCmd.CommandText = "SELECT * from `Expenses$A2:C4`"


' This SQL statement selects a named cell range defined in the
workbook.
oCmd.CommandText = "SELECT * from `Range1`"


' Open a recordset containing the worksheet data.
Set oRS = New ADODB.Recordset
oRS.CursorLocation = adUseServer
oRS.Open oCmd, , adOpenKeyset, adLockOptimistic


Debug.Print oRS.RecordCount


' Update last row
oRS.MoveLast
oRS(0).Value = -1
oRS.Update


' Add a new row
oRS.AddNew
oRS(0).Value = 7
oRS(1).Value = 8
oRS(2).Value = 9
oRS.Update


Debug.Print oRS.RecordCount


' Clean Up
Set oRS = Nothing
Set oCmd = Nothing
oConn.Close
Set oConn = Nothing


or like:

SELECT
Name, City
FROM [Sheet1$A1999]
IN 'C:\test.xls' 'EXCEL 8.0;'

Regards,

Todd

Todd

the Excel file referenced in the sample isn't in the .zip
Sorry for the confusion...I didn't actually change the file at Debra's
site.
I described what I did to test a same-workbook query.

I'll probably have to create the names myself
Definitely

Is there no way to work with the 'CurrentRegion' object and pass that
as
the source range?
Not that I'm aware of. You'd need to create range names to be
referenced
by
the SQL.

I hope that helps

***********
Regards,
Ron

XL2002, WinXP


:

Hey Ron,

Thanks for the assistance. Very nice sample file.

However, the Excel file referenced in the sample isn't in the .zip.
Do
you
have that sample data Excel file?

In answer to your question about referencing the sheet, unfortunately,
for
my use case scenario, this wouldn't work. I'm being handed a file
that
has
a singular spreadsheet containing all of the tables but no named
ranges.
I'll probably have to create the names myself. Is there no way to
work
with
the 'CurrentRegion' object and pass that as the source range?

Thanks Again,

Todd


See if the QueryMaster file at Debra Dalgleish's website gets you
pointed
in
the right direction:
http://www.contextures.com/excelfiles.html#External

Use the Generic Excel List Query

Assuming you want to use the QueryMaster file as the data source, I
inserted
a new worksheet (Sheet1) and copied your posted data to it.

Query Field Settings of Interest:
Data_Source: (enter the complete file path and file name )
SQL_Select: SELECT Name, Year, Earnings
SQL_From: "FROM `C:\ExcelQueries\QryMaster\QueryMaster`.`Sheet1$`"
SQL_Where: WHERE Name='Company1'

Note: The best approach is to create a named range for the data list
and
use
that name in the SQL_From field. But, if you refer to the sheet name
followed
by a dollar sign (Sheet1$), the query will use the used range of the
referenced sheet and try to interpret it as a table.

Is that something you can work with?

Post back with any questions.

***********
Regards,
Ron

XL2002, WinXP


:

Hey All,

I'm trying to run a SQL statement against an Excel range. For
example,
say
we have a range with the following:

Name Year Earnings
Company1 1999 $25,000
Company2 1999 $30,000
Company3 1999 $35,000
Company1 2000 $27,000
Company2 2000 $32,000
Company3 2000 $37,000
Company1 2001 $29,000
Company2 2001 $34,000
Company3 2001 $39,000

And want to do something like:

SELECT Name, Year, Earnings FROM Selection WHERE Name='Company1'

The ADO code samples that I found do not have complex SQL
statements
and,
more importantly, do not work.

Thanks in Advance,

TC

 

[Guest]

For me it's a matter of personal preference. Most of my query work is
against Oracle databases, so I use DSN-less connections to avoid any concerns
about whether the user has a specifc DSN defined or not. For querying Excel,
it might not make much difference.

***********
Regards,
Ron

XL2002, WinXP