Microsoft ODBC Query: parameters on OS X Office 2008

[Phantom]

Greetings, programs.

Does anyone know if it is possible to use a parameter in a Micosoft
ODBC Query on OS X Office 2008? I'd like to set a particular cell
within the spreadsheet to act as a parameter in this example query:

select * from mytable where project=$A$1

will something along these lines work? I tried it, even using a full
worksheet reference:

=Sheet1!$A$1

... but no luck. If anyone's done this, I'd love to hear how. I'm
thinking I'll be forced to use Applescript to update the SQL, which
isn't too bad:

tell application "Microsoft Excel"
set sql of query table 1 of worksheet 5 to somethingelse
end tell

still, thought there might be a solution in Office 2008; I noticed that
Microsoft seems to provide for that in Office 2010 on the Windows side.

thanks guys
P.

[Phantom]

Post by Phantom
Greetings, programs.
Does anyone know if it is possible to use a parameter in a Micosoft
ODBC Query on OS X Office 2008? I'd like to set a particular cell
select * from mytable where project=$A$1
will something along these lines work? I tried it, even using a full
=Sheet1!$A$1
... but no luck. If anyone's done this, I'd love to hear how. I'm
thinking I'll be forced to use Applescript to update the SQL, which
tell application "Microsoft Excel"
set sql of query table 1 of worksheet 5 to somethingelse
end tell
still, thought there might be a solution in Office 2008; I noticed that
Microsoft seems to provide for that in Office 2010 on the Windows side.
thanks guys
P.

hah, solved my own question, here's the info for others, hope this
helps... I couldn't find this anywhere in the documentation.

to use parameters in Microsoft Query (ODBC), replace the value with a
question mark (?), e.g.:

select * from projects where projectshortname=?

when you save the query ("Return data to Excel"), you'll be asked to
identify each ? parameter. presumably, you can enter a string of data,
but it's even cooler in that it allows you to point and shoot (or
simply enter a valid cell range), and optionally tie that parameter
indefinitely to a cell.

so, I now have a settings worksheet that contains the parameters for
all the ODBC calls in the workbook, making cloning of the document from
one client to the next a pure snap.

I'm working on writing a quick applescript that will then update all
the queries at once, something along the lines of (still working on the
looping):

tell application "Microsoft Excel"

set theQueryTable to query table 1 of worksheet 5
refresh query table theQueryTable

end tell

hope this helps!
P.

p.s. if Excel is crashing on you when you try to return the data from
the query, update your Office to the latest. mine crashed constantly
until I updated.

p.p.s. another problem Microsoft seems to have are null columns, e.g.:

select X, Y, null as Z

will seem to freak out the result set. if you need a placeholder, it's
better to use a blank value:

select X, Y, '' as Z

joins that actually require a null column are best left to internal
views back at the server (your mileage may vary).

[Phantom]

Post by Phantom

Post by Phantom
Greetings, programs.
Does anyone know if it is possible to use a parameter in a Micosoft
ODBC Query on OS X Office 2008? I'd like to set a particular cell
select * from mytable where project=$A$1
will something along these lines work? I tried it, even using a full
=Sheet1!$A$1
... but no luck. If anyone's done this, I'd love to hear how. I'm
thinking I'll be forced to use Applescript to update the SQL, which
tell application "Microsoft Excel"
set sql of query table 1 of worksheet 5 to somethingelse
end tell
still, thought there might be a solution in Office 2008; I noticed that
Microsoft seems to provide for that in Office 2010 on the Windows side.
thanks guys
P.

hah, solved my own question, here's the info for others, hope this
helps... I couldn't find this anywhere in the documentation.
to use parameters in Microsoft Query (ODBC), replace the value with a
select * from projects where projectshortname=?
when you save the query ("Return data to Excel"), you'll be asked to
identify each ? parameter. presumably, you can enter a string of data,
but it's even cooler in that it allows you to point and shoot (or
simply enter a valid cell range), and optionally tie that parameter
indefinitely to a cell.
so, I now have a settings worksheet that contains the parameters for
all the ODBC calls in the workbook, making cloning of the document from
one client to the next a pure snap.
I'm working on writing a quick applescript that will then update all
the queries at once, something along the lines of (still working on the
tell application "Microsoft Excel"
set theQueryTable to query table 1 of worksheet 5
refresh query table theQueryTable
end tell
hope this helps!
P.
p.s. if Excel is crashing on you when you try to return the data from
the query, update your Office to the latest. mine crashed constantly
until I updated.
select X, Y, null as Z
will seem to freak out the result set. if you need a placeholder, it's
select X, Y, '' as Z
joins that actually require a null column are best left to internal
views back at the server (your mileage may vary).

Unfortunately, I've discovered that Microcrap won't allow parameters in
an External Data Query that is inserted into the sheet *as a pivot
table*. So nice of Microcrap to think of integrating External Data
Queries with Pivot Tables, just a shame they never bothered to !#$%$#^&
test it!

I don't suppose there's a Microsoft employee lurking around? Perhaps
you could pass this along, your crap doesn't work... hardly news, I
guess.

The only work around I was able to figure out was to create an extra
Data sheet, access the data with a standard External Data Query, then
generating a separate Pivot Table on the actual sheet.

I'll try this nce I was able to actually try this without Microphuck
Excel **crashing**, thank you Micromorons.