MySQL to Excel (Cell as reference)

[Robert King]

I'm using:
Windows 7 x64, MS Excel 07, MySQL 5.1 (localhost), ODBC Connector 5.16

Current status
I finally managed to use get data from MySQL into Excel Sheet. That works fine.

Question
Can I use a cell as reference in the MySQL query?

1 - SELECT * FROM users WHERE (id > 10) // works
1 - SELECT * FROM users WHERE (id > ?) // prompts Textinput, ends in error after input
2 - SELECT * FROM users WHERE (id > $d$1 ) // throws error, invalid

Is a VBA Script needed? If so please describe fool-proof


*For me it looks like it's an easy and a common task.. I searched this forum and Google for a proper solution for hours now. I couldn't find a working solution :/

 

[Gary McMaster]

Hi Robert,

In MSQuery the operation you want to perform is called a "Parameter Query". Maybe that is something you could Google.

It is rather simple in MSQuery. Basically, the record selection criteria in MSQuery is enclosed in square brackets

[]

. This forces it to prompt the user for the record selection criteria.

In Excel there is a dialog box (Query properties I believe) that allows you to designate a cell from which the prompt should automatically accept its data.

HTH,

Gary

 

[Fazza]

for example, Robert. http://www.dailydoseofexcel.com/archives/2004/12/13/ Regards, Fazza

 

[Robert King]

SELECT * 
FROM users
WHERE (users_0.visits Between ? And ?)

For any reason this works!
Thank you guys very much!

 

[Blade Hunter]

I'm using:
Windows 7 x64, MS Excel 07, MySQL 5.1 (localhost), ODBC Connector 5.16

Current status
I finally managed to use get data from MySQL into Excel Sheet. That works fine.

Question
Can I use a cell as reference in the MySQL query?

1 - SELECT * FROM users WHERE (id > 10) // works
1 - SELECT * FROM users WHERE (id > ?) // prompts Textinput, ends in error after input
2 - SELECT * FROM users WHERE (id > $d$1 ) // throws error, invalid

Is a VBA Script needed? If so please describe fool-proof


*For me it looks like it's an easy and a common task.. I searched this forum and Google for a proper solution for hours now. I couldn't find a working solution :/

I would have thought it would be something like:

"SELECT * FROM users WHERE (id > " & Range("d1").Value & ")"

 

[Robert King]

Ha! even better...

SELECT * 
FROM users
WHERE (users_0.visits [$g$1])

=======

The params button was first disabled. I assumed it was not available for MySQL. After going through MSQuery it editable. I'll investigate the workflow tomorrow a bit closer. Maybe I'm just a bit too tired