Excel 2007 -> Filtered Data Connection from Another Spreadsheet

R

ramz

So, I'm attempting to grab a subset of data from one master
spreadsheet based on the value of a cell in a specific row to
determine if it should be imported or not. I've managed to create the
connection which imports the entire table, but short of using
filtering in the importing sheet (which won't work for my purposes
because I need to do sum operations and such and the filtering only
seems to hide the rows I don't want but does not exclude data found
therein when range sums are used) I can't seem to figure out a way to
make the data connection do the filtering for me. Being that this
seems to use SQL language (and mind you, I'm not an SQL expert, but I
have picked up a few phrases along the way) I would think that I could
type something like this in the "Command Text" box in the Definition
tab of the Connection Properties dialog:

SELECT * FROM [Sheet1$] WHERE < range or column or whatever > = '<
VALUE >'

I *think* the problem is that I can't find documentation that would
tell me what the syntax of the "< range or column or whatever >" bit
would actually be. I've tried a zillion different things to no avail.
Could someone point to some documentation or provide some insight on
getting this working? Thanks!
 
D

Dick Kusleika

no one has any ideas on this one? hrmpf.

So, I'm attempting to grab a subset of data from one master
spreadsheet based on the value of a cell in a specific row to
determine if it should be imported or not. I've managed to create the
connection which imports the entire table, but short of using
filtering in the importing sheet (which won't work for my purposes
because I need to do sum operations and such and the filtering only
seems to hide the rows I don't want but does not exclude data found
therein when range sums are used) I can't seem to figure out a way to
make the data connection do the filtering for me. Being that this
seems to use SQL language (and mind you, I'm not an SQL expert, but I
have picked up a few phrases along the way) I would think that I could
type something like this in the "Command Text" box in the Definition
tab of the Connection Properties dialog:

SELECT * FROM [Sheet1$] WHERE < range or column or whatever > = '<
VALUE >'

I *think* the problem is that I can't find documentation that would
tell me what the syntax of the "< range or column or whatever >" bit
would actually be. I've tried a zillion different things to no avail.
Could someone point to some documentation or provide some insight on
getting this working? Thanks!

It should be whatever is in Row 1 of Sheet1. It will assume that Row 1
contains field names. Assume A1:C2 is

Name Number Amount
Ramz 1 12.00

...WHERE Number>=1

should work for a WHERE clause.
 
R

ramz

SELECT * FROM [Sheet1$] WHERE FiscalPeriod='Q1-2010'

^^^ gives an error message ("No value given for one or more required
parameters."). I assume this is because FiscalPeriod does not mean
anything to the query

SELECT * FROM [Sheet1$] WHERE 'FiscalPeriod'='Q1-2010'

^^^ no error message but does not return results

"FiscalPeriod" is the column header in row 1 of the source worksheet.

no one has any ideas on this one? hrmpf.
So, I'm attempting to grab a subset of data from one master
spreadsheet based on the value of a cell in a specific row to
determine if it should be imported or not. I've managed to create the
connection which imports the entire table, but short of using
filtering in the importing sheet (which won't work for my purposes
because I need to do sum operations and such and the filtering only
seems to hide the rows I don't want but does not exclude data found
therein when range sums are used) I can't seem to figure out a way to
make the data connection do the filtering for me. Being that this
seems to use SQL language (and mind you, I'm not an SQL expert, but I
have picked up a few phrases along the way) I would think that I could
type something like this in the "Command Text" box in the Definition
tab of the Connection Properties dialog:
SELECT * FROM [Sheet1$] WHERE < range or column or whatever > = '<
VALUE >'
I *think* the problem is that I can't find documentation that would
tell me what the syntax of the "< range or column or whatever >" bit
would actually be. I've tried a zillion different things to no avail.
Could someone point to some documentation or provide some insight on
getting this working? Thanks!

It should be whatever is in Row 1 of Sheet1.  It will assume that Row 1
contains field names.  Assume A1:C2 is

Name    Number  Amount
Ramz    1               12.00

..WHERE Number>=1

should work for a WHERE clause.
 
R

ramz

I take that back... apparently, I've run into some kind of refresh
hiccup here. The SQL query as you propose (and as I tried MANY MANY
times, ugh) works fine when I start with a fresh importing
spreadsheet. Thank you for your help.

no one has any ideas on this one? hrmpf.
So, I'm attempting to grab a subset of data from one master
spreadsheet based on the value of a cell in a specific row to
determine if it should be imported or not. I've managed to create the
connection which imports the entire table, but short of using
filtering in the importing sheet (which won't work for my purposes
because I need to do sum operations and such and the filtering only
seems to hide the rows I don't want but does not exclude data found
therein when range sums are used) I can't seem to figure out a way to
make the data connection do the filtering for me. Being that this
seems to use SQL language (and mind you, I'm not an SQL expert, but I
have picked up a few phrases along the way) I would think that I could
type something like this in the "Command Text" box in the Definition
tab of the Connection Properties dialog:
SELECT * FROM [Sheet1$] WHERE < range or column or whatever > = '<
VALUE >'
I *think* the problem is that I can't find documentation that would
tell me what the syntax of the "< range or column or whatever >" bit
would actually be. I've tried a zillion different things to no avail.
Could someone point to some documentation or provide some insight on
getting this working? Thanks!

It should be whatever is in Row 1 of Sheet1.  It will assume that Row 1
contains field names.  Assume A1:C2 is

Name    Number  Amount
Ramz    1               12.00

..WHERE Number>=1

should work for a WHERE clause.
 
D

Dick Kusleika

SELECT * FROM [Sheet1$] WHERE FiscalPeriod='Q1-2010'

^^^ gives an error message ("No value given for one or more required
parameters."). I assume this is because FiscalPeriod does not mean
anything to the query

SELECT * FROM [Sheet1$] WHERE 'FiscalPeriod'='Q1-2010'

^^^ no error message but does not return results

"FiscalPeriod" is the column header in row 1 of the source worksheet.

SELECT * FROM [Sheet1$] WHERE FiscalPeriod='Q2-2009'

This worked for me. Make sure you don't have trailing spaces on
FiscalPeriod (or other nonprinting characters). I can send make my test
workbooks available if you think it will help you track down the problem.

Here's my connection string
ODBC;DSN=Excel Files;DBQ=C:\Documents and Settings\Dick.NEBRASKA\My
Documents\ExternalData.xls;DefaultDir=C:\Documents and
Settings\Dick.NEBRASKA\My
Documents;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;
 
D

Dick Kusleika

I take that back... apparently, I've run into some kind of refresh
hiccup here. The SQL query as you propose (and as I tried MANY MANY
times, ugh) works fine when I start with a fresh importing
spreadsheet. Thank you for your help.
No problem. Querying close workbooks never felt too robust to me, so I
always hold my breath when refreshing. :)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top