MS Query variable

D

Dean

Is it possible to pass a variable to an MS query from
and excel spreadsheet.
ie. can the user enter something into a cell on sheet1
and this be called into an MSquery and the query run on
this info?

Dean
 
K

Kieran

This is certainly possible. However the MSquery results must be
returned to excel.


You need to access the query properties (right click in the data
returned area) and select the Parameters option.

This menu option is sensitive to the fact that the query is expecting
parameters so ensure that the query is constructed for this first.
(MSQuery help hsows you how)

Then it is simply a matter of selecting the appropriate method for
entry of the parameter.

Note however that when you change the parameter in excel, you will need
to requery to obtain the new result set.

It could be automated, but I think you need VBA to trap the
worksheet_change event. This seems to be a bit more than you
origianally requested, but f you need it post back. I am sure that help
will not be far away.
 
A

Arvi Laanemets

Hi

You need a code invoked after the cell entry is changed. You can use the
worksheet's Change event (the active cell address must be checked at start,
otherwise the query is refreshed whenever you change anything on worksheet),
or the user starts the procedure manually (shortcut, command button, or from
menu).

The code changes the CommandText property of query object, and then
refreshes the query. But first you have to create the query with any fixed
values as parameters.

Below is a segment of code from one of my workbooks as example:
.....
kuu = ActiveSheet.Range("D1").Value
aasta = ActiveSheet.Range("D2").Value
'
Set qtQtrResults = Worksheets("Tasud").QueryTables(1)
Sheets("Tasud").Activate
ActiveSheet.Range("C2").Select
With qtQtrResults
.CommandType = xlCmdSql
.CommandText = _
"SELECT a.tabn, a.sisestus As 'arvestus', b.tyyp, b.kuupaev,
a.tl, a.summa FROM tasud a, lahendus b WHERE b.lahendus=a.sisestus AND
a.tabn<>'' AND a.tl<'060' AND Year(b.kuupaev)=" & aasta & " AND
Month(b.kuupaev)=" & kuu & " ORDER BY a.tabn, a.sisestus"
.Refresh
End With
.....


Arvi Laanemets
 
D

Dean Southgate

Arvi,

I think I understand the ("Tasud") as being the name of
your sheets, sheet1,l sheet2 etc. but the bit where your
example says .QueryTables(1) is confusing me.
Does it meen that the table you are querying is in an
Excel spreadsheet? if so how would I change it to access
an ODBC table?

Thanks again
Dean
 
A

Arvi Laanemets

Hi


Dean Southgate said:
Arvi,

I think I understand the ("Tasud") as being the name of
your sheets, sheet1,l sheet2 etc. but the bit where your
example says .QueryTables(1) is confusing me.
Does it meen that the table you are querying is in an
Excel spreadsheet? if so how would I change it to access
an ODBC table?

Thanks again
Dean
Click to expand...


It's the sheet where I entered the parameters, and from where I invoked the
procedure



Sheet 'Tasud" is the sheet with data, returned by ODBC query (results
table), in it. And it looks like the ODBC query is defined by it's result
table properties somehow (whenever you delete the whole result table, the
query is deleted also). As I always use only one query per sheet, I don't
have to worry, which query table has which index - I'll edit the properties
of QueryTables(1) on this sheet.



I activate the sheet "Tasud" and a cell, always falling into results range
(a cell from header row), while I
have to refresh the result table after editing the CommandText property.




Arvi Laanemets
 
Top