Excel - MS Query - How to use Cell Value in SQL Select statement

[vambam]

Hi,
Is it possible within MS Query select statement to use a cell value as part of the sql select statement where clause without using Add Criteria/parameters (which becomes disabled in commplex sql select statements) and without using VBA coding?
TIA,
Doug

 

[SkipVought]

Doug,

I believe that the only way to do what you want is via VBA. Is there a problem with that?

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[xlbo]

Can you not hit the SQL button in MS Query and manually insert your criteria there ?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

Geoff,

There are some complex SQL statements where a parameter is not permitted.

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[xlbo]

Skip - not done it myself as I just use VBA but if you hit the SQL button when in MS Query GUI, it shows you the SQL text - I was just wondering if you can manually input a parameter into that using standard SQL syntax....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[vambam]

I could certainly modify the sql code; but the person using the spreadsheet would be challenged, frustrated and very well may goof it up.
I'm in the early stages of learning some vba code; so I wanted to determine what could be done with the cell values in the sql code. I suspected that it couldn't be done.
Essentially the user would enter certain values such as: Customer #, Start Date and End Date in specific cells of the excel sheet and click refresh button to view the results.
VBA Question: Where would I insert vba coding to cause the sql query statement to be modified (i.e. querytable?) and fired when Refresh Button is clicked or must a new button be developed?
TIA,
Doug

 

[SkipVought]

I do this quite often using a dropdown for the user to make a selection.

The selected value is concatenated in the SQL statement and run on the Selection Click event.

There is little chance for the user to screw thing up using this kind of approch.

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[xlbo]

Seconded - it is actually quite straightforward - something like

Dim Var1, Var 2, strSQL

Var1 = Range("Range1").value
Var2 = Range("Range2").value

strSQL = "SELECT Somethinig FROM Somewhere WHERE x=y AND criteria1 = '" & Var1 & "' AND criteria2 = '" & Var2 & "'"

With Sheets("Sheetname").Querytables(1)
   .commandtext = mSQL
   .refresh (false)
end with

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[vambam]

I'm on my way now with the vba tips and your assitance is greatly appreciated!
Thanks to all who responded,
Doug

 

[vambam]

Hi,
I've added a button to allow refresh using the criteria and it works well, except it prints on the report. What I'd prefer to do is insert the code currently behind that button into the Refresh Data button click, but I don't know where that is? Any would be great!
TIA,
Doug

 

[SkipVought]

Right click the button and UNCHECK the Print Object in the Format Controls - Properties tab

Skip,

_{[red]Be Advised![/red]
The band of elderly oriental musicians, known as Ground Cover, is, in reality...
Asian Jasmine!}

 

[vambam]

Skip,
Awesome!
Thanks
Doug

 

[vambam]

Hi,
I have a question related to Geoff's example for refreshing of an excel sheet which worked great.
Similar question re: Pivot Table usage with the same concept.

Following code for refreshing an excel list shown below:
With Sheets("Sheetname").Querytables(1)
.commandtext = mSQL
.refresh (false)
end with

Could anyone provide the equivalent for refreshing a pivot table Sheetname. Am I correct in assuming the sql code would not change (per previous posting example)?

TIA,
Doug

 

[xlbo]

Please look in the help files before posting

The method to refresh a pivottable is listed there under the methods of a Pivottable object

Sheets("Sheetname").Pivottables(1).Refreshtable

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question