Programatically changing MS-Query properties within Excel.

[Greg Glynn]

Hi Everyone,

I have a workbook that contains an ODBC link to a database and using
MS-Query it fills a range in my workbook with some filtered data from
my source database.

My query is set up at the moment, more or less hard-coded, to get the
last 7 days of data. Because of the hard-coding, I have to go in to
the query manually and specify a new date parameter whenever the query
needs to be updated.

I'd like to be able to change the MS-Query data from Excel so that I
can change the filtering and sorting without the need to manually
modify the query every time.

Is this possible?

Regards


Greg

[NickHK]

Greg,
Check out "parameter_query" in Excel Help.

NickHK

"Greg Glynn" <[email protected]> wrote in message
news:[email protected]...
> Hi Everyone,
>
> I have a workbook that contains an ODBC link to a database and using
> MS-Query it fills a range in my workbook with some filtered data from
> my source database.
>
> My query is set up at the moment, more or less hard-coded, to get the
> last 7 days of data. Because of the hard-coding, I have to go in to
> the query manually and specify a new date parameter whenever the query
> needs to be updated.
>
> I'd like to be able to change the MS-Query data from Excel so that I
> can change the filtering and sorting without the need to manually
> modify the query every time.
>
> Is this possible?
>
> Regards
>
>
> Greg
>

[Tom Ogilvy]

http://nickhodge.co.uk/gui/datamenu/...taexamples.htm

http://www.*****-clicks.com/excel/Ex...htm#Parameters

--
Regards,
Tom Ogilvy


"Greg Glynn" wrote:

> Hi Everyone,
>
> I have a workbook that contains an ODBC link to a database and using
> MS-Query it fills a range in my workbook with some filtered data from
> my source database.
>
> My query is set up at the moment, more or less hard-coded, to get the
> last 7 days of data. Because of the hard-coding, I have to go in to
> the query manually and specify a new date parameter whenever the query
> needs to be updated.
>
> I'd like to be able to change the MS-Query data from Excel so that I
> can change the filtering and sorting without the need to manually
> modify the query every time.
>
> Is this possible?
>
> Regards
>
>
> Greg
>
>

[RB Smissaert]

It might be better to move away from MS Query and code in VBA with ADO.
It is quite simple and will give you much more control.
Plenty of code samples around.

RBS


"Greg Glynn" <[email protected]> wrote in message
news:[email protected]...
> Hi Everyone,
>
> I have a workbook that contains an ODBC link to a database and using
> MS-Query it fills a range in my workbook with some filtered data from
> my source database.
>
> My query is set up at the moment, more or less hard-coded, to get the
> last 7 days of data. Because of the hard-coding, I have to go in to
> the query manually and specify a new date parameter whenever the query
> needs to be updated.
>
> I'd like to be able to change the MS-Query data from Excel so that I
> can change the filtering and sorting without the need to manually
> modify the query every time.
>
> Is this possible?
>
> Regards
>
>
> Greg
>

[Greg Glynn]

Thank you Gentlemen. Excellent advice. All working (and I'm
investigating ADO).

Regards

Greg

[Jim Palmer]

I am faced with a very similar challenge.

I have set up a parameter query and I have cells that store minimum vendor number, maximum vendor number, minimum department number and maximum department number.

The query works great for those particular variables.

I have not been able to figure out how to format cells for the buyer number field where the selction criter is In('02,'05'). I keep getting the message

"a buffer passed to a system call is too small to hold return data".

Any suggestions would be appreciated.

Sincerely

Jim Palmer