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

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

How to run a complex SQL statement against Excel spreadsheet range
 

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_Whe 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" wrote:

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

How to run a complex SQL statement against Excel spreadsheet range
 

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


"Ron Coderre" wrote in message
...
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_Whe 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" wrote:

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

How to run a complex SQL statement against Excel spreadsheet r
 

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


"TCook" wrote:

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


"Ron Coderre" wrote in message
...
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_Whe 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" wrote:

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

How to run a complex SQL statement against Excel spreadsheet r
 

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$A1:D999]
IN 'C:\test.xls' 'EXCEL 8.0;'

Regards,

Todd


"Ron Coderre" wrote in message
...
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


"TCook" wrote:

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


"Ron Coderre" wrote in message
...
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_Whe 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" wrote:

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

How to run a complex SQL statement against Excel spreadsheet r
 

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


"TCook" wrote:

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$A1:D999]
IN 'C:\test.xls' 'EXCEL 8.0;'

Regards,

Todd


"Ron Coderre" wrote in message
...
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


"TCook" wrote:

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


"Ron Coderre" wrote in message
...
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_Whe 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" wrote:

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

How to run a complex SQL statement against Excel spreadsheet r
 

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;PageTime out=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



"Ron Coderre" wrote in message
...
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


"TCook" wrote:

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$A1:D999]
IN 'C:\test.xls' 'EXCEL 8.0;'

Regards,

Todd


"Ron Coderre" wrote in message
...
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


"TCook" wrote:

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


"Ron Coderre" wrote in message
...
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_Whe 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" wrote:

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