Refresh Data Import Q

[John]

I have a piece of code that uses a variable to pull info from a MS Access
database (finally through the help of this newsgroup to get it to work), all
works well, except that when I change the variable and then just 'Refresh'
the data it returns is using the previous variable that I have entered. What
I have to do is delete the old query and run a new one.

I thought it would just make sense to just 'refresh' using the new variable
as I don't want my file to balloon in size, or am I doing something wrong?

Thanks

 

[Stephen Bullen]

Hi John,

I have a piece of code that uses a variable to pull info from a MS Access
database (finally through the help of this newsgroup to get it to work), all
works well, except that when I change the variable and then just 'Refresh'
the data it returns is using the previous variable that I have entered. What
I have to do is delete the old query and run a new one.

I thought it would just make sense to just 'refresh' using the new variable
as I don't want my file to balloon in size, or am I doing something wrong?

How have you declared the variable? The only way it will happen automatically
is if you've used a QueryTable with a parameter linked to a cell - it'll use
the value in the cell with each refresh. If you're building the query in code,
there's no link between the QueryTable and a variable. For details about
using parameters in Query tables, see :

http://www.dicks-blog.com/archives/2004/12/13/parameters-in-excel-external-dat
a-queries/

Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk

 

[John]

Thanks for your reply Stephen, that's a great site, the examples really help
a novice. I have only one problem when it states 'click on parameters' mine
is greyed out - I can't take the option, why is that?

 

[Stephen Bullen]

Hi John,

Thanks for your reply Stephen, that's a great site, the examples really help
a novice. I have only one problem when it states 'click on parameters' mine
is greyed out - I can't take the option, why is that?

That will be greyed out if you don't have any parameters in your query. If you
go through the steps Nick suggests, you'll see how it all works, then
(hopefully!) be able to adapt it to your needs.

Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk

 

[John]

Stephen, kinda worked out how to do it, I'm assuming that the Query Wizard
won't work setting parameters. Problem is that after entering the date
parameter and after trying to put in a second parameter of product numbers
(presumably I can do that) I receive an Invalid Datetime Format Null error
appearing

 

[John]

Stephen, forget half of my last post - got it working. One final question
which you maybe able to steer me on. I want another parameter which would
select a range of product numbers, I've tried the "IN [Enter a Range]". How
can I add an additional parameter?

 

[Stephen Bullen]

Hi John,

I want another parameter which would
select a range of product numbers, I've tried the "IN [Enter a Range]". How
can I add an additional parameter?

I'm not sure you can do that with linked cell parameters, I'm afraid

Regards

Stephen Bullen
Microsoft MVP - Excel
www.oaltd.co.uk