SQL - query on excel cells rather than write the query text?

Dear all,

I'm using one Excel macro to do some SQL queries on a lot of excel files that I have. This file works perfectly, but I want to know if it is possible to do references to cells in the SQL query section of this file.

For example, on this query:

Please Login or Register  to view this content.

Is it possible to select the date numbers from Excel cells itself, rather than write them on the code? For example something like this:

Please Login or Register  to view this content.

Please if this is possible let me know how, because it will help me a lot!

Thank you

Yes but you can't put the cell references into the sql directly. Something like:

sql = "select DATE, PAYMENT_FORM,VALUE from [Venda$] where DATE BETWEEN '" & worksheets("sheetname").Range("H1").Value & "' AND '" & worksheets("sheetname").Range("I1").Value & "' AND CARTEIRA_CLIENTE = 'DIAMANTE'"

I also don't know if you are querying a database or a worksheet, but I typically use # instead of ' in my queries around dates. You also don't need to actually reference the sheet if it is for sure that those ranges will always be on the active sheet.

Originally Posted by Arkadi

Yes but you can't put the cell references into the sql directly. Something like:

sql = "select DATE, PAYMENT_FORM,VALUE from [Venda$] where DATE BETWEEN '" & worksheets("sheetname").Range("H1").Value & "' AND '" & worksheets("sheetname").Range("I1").Value & "' AND CARTEIRA_CLIENTE = 'DIAMANTE'"

I also don't know if you are querying a database or a worksheet, but I typically use # instead of ' in my queries around dates. You also don't need to actually reference the sheet if it is for sure that those ranges will always be on the active sheet.

Dear Arkadi,

Thank you for your reply, but unfortunatly I'm still not able to do what I want. I'm querying a worksheet, and using your query string I'm getting this error: "Data type mismatch in criteria expression."
Do you know what might be?

Thanks again

Any chance you can provide a file with the relevant code and some sample data? It would make it easier to figure out the cell formatting for the dates... I assume H1 and I1 are cells formatted as date as well as the column "DATE"?

When retrieving date value from cell and using it in SQL code. I'd recommend using Value2 to obtain value from cell, rather than using .Text or .Value.
Value will return inconsistent result depending on formatting applied to cell.

If cell holds datetime vlaue (i.e. Now()) and .Value is used...
If cell is formatted for date - It will return both date and time formatted to system setting.
If cell is formatted for time - It will return double value representing datetime.
etc.

I'd recommend something like below, if only using date.

Please Login or Register  to view this content.

"yyyy-Mm-Dd" format is universally accepted by SQL dbs.

I did not realize that you are building the query in a cell as well... that doesn't really work out so well when trying to put in cell references. Instead you should use a formula that builds the actual sql statement. So in G5 you would do something like this: (note the # symbol around dates)

Formula:

Please Login or Register  to view this content.

That will then result in:

select * from [Venda$] where PRODUTO > 2 AND DATE BETWEEN #2015-08-16# AND #2015-08-20# (in my case because of the dates i put in H1 and I1 to test)

My personal preference is to use "Microsoft.ACE.OLEDB.12.0" rather than Excel Driver.

Ex:
Assuming that you have Arkadi's formula in G1.

Code like below.

Please Login or Register  to view this content.

Glad we could help you solve the issue
Thanks for the feedback, rep, and marking the thread as solved!

You are welcome