Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 213
Default Need Help with Refreshing Querytables.

Hello all,

I have 3 querytables that connects to an external database in my Excel file.
All 3 query tables are looking up 3 values in worksheet "Data". The 3
values are in Cells D15, D16 and D17 of the Data worksheet. The way I have
the querytables to work is as followed:

1. I populate a value in Cell D15, and data populates for Querytable 1.
2. For Cell D16, I have a Vlookup formula to grab the data from Querytable
1 by looking up the value in Cell D15. The Querytable 2 then populates based
on the value in Cell D16.
3. For Cell D17, I have the same setup as Cell D16. The Querytable 3 is
populated based on the value in Cell D17.

The problem that I have is since Querytables 2 and 3 are looking up the
values in Cells D16 and D17, the Querytables 2 and 3 start refreshing before
the values in Cells D16 and D17 populate. Because of that, I receive error
messages for not able to refresh the Querytables 2 and 3.

Is there a way to control the refresh on Querytables 2 and 3 that refresh
after the values are populated in Cells D16 and D17 by Vlookup, respectively?

Thanks.
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,942
Default Need Help with Refreshing Querytables.

hi
without seeing our code, i can only guess that you are allowing background
queries. I would suggest that you add something like this.......
range("A1").querytable.refresh backgroundquery:=false
this will pause code until the refresh is completed. and since your project
is dependent on a formula, i would add the calculate command after each
refresh.

post your code and i'll see if i can rework it.

Regards
FSt1

"Accesshelp" wrote:

Hello all,

I have 3 querytables that connects to an external database in my Excel file.
All 3 query tables are looking up 3 values in worksheet "Data". The 3
values are in Cells D15, D16 and D17 of the Data worksheet. The way I have
the querytables to work is as followed:

1. I populate a value in Cell D15, and data populates for Querytable 1.
2. For Cell D16, I have a Vlookup formula to grab the data from Querytable
1 by looking up the value in Cell D15. The Querytable 2 then populates based
on the value in Cell D16.
3. For Cell D17, I have the same setup as Cell D16. The Querytable 3 is
populated based on the value in Cell D17.

The problem that I have is since Querytables 2 and 3 are looking up the
values in Cells D16 and D17, the Querytables 2 and 3 start refreshing before
the values in Cells D16 and D17 populate. Because of that, I receive error
messages for not able to refresh the Querytables 2 and 3.

Is there a way to control the refresh on Querytables 2 and 3 that refresh
after the values are populated in Cells D16 and D17 by Vlookup, respectively?

Thanks.

  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 213
Default Need Help with Refreshing Querytables.

FSt1,

Thanks for your response. I do not have any code for my querytables.

The querytables are created via DataImport External DataNew Database Query.

Thanks.

"FSt1" wrote:

hi
without seeing our code, i can only guess that you are allowing background
queries. I would suggest that you add something like this.......
range("A1").querytable.refresh backgroundquery:=false
this will pause code until the refresh is completed. and since your project
is dependent on a formula, i would add the calculate command after each
refresh.

post your code and i'll see if i can rework it.

Regards
FSt1

"Accesshelp" wrote:

Hello all,

I have 3 querytables that connects to an external database in my Excel file.
All 3 query tables are looking up 3 values in worksheet "Data". The 3
values are in Cells D15, D16 and D17 of the Data worksheet. The way I have
the querytables to work is as followed:

1. I populate a value in Cell D15, and data populates for Querytable 1.
2. For Cell D16, I have a Vlookup formula to grab the data from Querytable
1 by looking up the value in Cell D15. The Querytable 2 then populates based
on the value in Cell D16.
3. For Cell D17, I have the same setup as Cell D16. The Querytable 3 is
populated based on the value in Cell D17.

The problem that I have is since Querytables 2 and 3 are looking up the
values in Cells D16 and D17, the Querytables 2 and 3 start refreshing before
the values in Cells D16 and D17 populate. Because of that, I receive error
messages for not able to refresh the Querytables 2 and 3.

Is there a way to control the refresh on Querytables 2 and 3 that refresh
after the values are populated in Cells D16 and D17 by Vlookup, respectively?

Thanks.

  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,942
Default Need Help with Refreshing Querytables.

hi
sorry. i assumed that since you posted under programming that you had code.
how do you refresh the queries.

regards
FSt1

"Accesshelp" wrote:

FSt1,

Thanks for your response. I do not have any code for my querytables.

The querytables are created via DataImport External DataNew Database Query.

Thanks.

"FSt1" wrote:

hi
without seeing our code, i can only guess that you are allowing background
queries. I would suggest that you add something like this.......
range("A1").querytable.refresh backgroundquery:=false
this will pause code until the refresh is completed. and since your project
is dependent on a formula, i would add the calculate command after each
refresh.

post your code and i'll see if i can rework it.

Regards
FSt1

"Accesshelp" wrote:

Hello all,

I have 3 querytables that connects to an external database in my Excel file.
All 3 query tables are looking up 3 values in worksheet "Data". The 3
values are in Cells D15, D16 and D17 of the Data worksheet. The way I have
the querytables to work is as followed:

1. I populate a value in Cell D15, and data populates for Querytable 1.
2. For Cell D16, I have a Vlookup formula to grab the data from Querytable
1 by looking up the value in Cell D15. The Querytable 2 then populates based
on the value in Cell D16.
3. For Cell D17, I have the same setup as Cell D16. The Querytable 3 is
populated based on the value in Cell D17.

The problem that I have is since Querytables 2 and 3 are looking up the
values in Cells D16 and D17, the Querytables 2 and 3 start refreshing before
the values in Cells D16 and D17 populate. Because of that, I receive error
messages for not able to refresh the Querytables 2 and 3.

Is there a way to control the refresh on Querytables 2 and 3 that refresh
after the values are populated in Cells D16 and D17 by Vlookup, respectively?

Thanks.

  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 213
Default Need Help with Refreshing Querytables.

The queries are refreshed automatically after each value changes. The
problem is when refresh is done, all the queries are refreshed before my
vlookup formula to do the calculation.

Thanks.

"FSt1" wrote:

hi
sorry. i assumed that since you posted under programming that you had code.
how do you refresh the queries.

regards
FSt1

"Accesshelp" wrote:

FSt1,

Thanks for your response. I do not have any code for my querytables.

The querytables are created via DataImport External DataNew Database Query.

Thanks.

"FSt1" wrote:

hi
without seeing our code, i can only guess that you are allowing background
queries. I would suggest that you add something like this.......
range("A1").querytable.refresh backgroundquery:=false
this will pause code until the refresh is completed. and since your project
is dependent on a formula, i would add the calculate command after each
refresh.

post your code and i'll see if i can rework it.

Regards
FSt1

"Accesshelp" wrote:

Hello all,

I have 3 querytables that connects to an external database in my Excel file.
All 3 query tables are looking up 3 values in worksheet "Data". The 3
values are in Cells D15, D16 and D17 of the Data worksheet. The way I have
the querytables to work is as followed:

1. I populate a value in Cell D15, and data populates for Querytable 1.
2. For Cell D16, I have a Vlookup formula to grab the data from Querytable
1 by looking up the value in Cell D15. The Querytable 2 then populates based
on the value in Cell D16.
3. For Cell D17, I have the same setup as Cell D16. The Querytable 3 is
populated based on the value in Cell D17.

The problem that I have is since Querytables 2 and 3 are looking up the
values in Cells D16 and D17, the Querytables 2 and 3 start refreshing before
the values in Cells D16 and D17 populate. Because of that, I receive error
messages for not able to refresh the Querytables 2 and 3.

Is there a way to control the refresh on Querytables 2 and 3 that refresh
after the values are populated in Cells D16 and D17 by Vlookup, respectively?

Thanks.



  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,942
Default Need Help with Refreshing Querytables.

hi
sorry to take so long. got hung up.
how have you got this set up to refresh automaticly. there has to be a
trigger.
i still think you problem is allowing a background query. you may have to
get away from the automatic thing and opt for a 1 refresh at a time.

Regards
FSt1

"Accesshelp" wrote:

The queries are refreshed automatically after each value changes. The
problem is when refresh is done, all the queries are refreshed before my
vlookup formula to do the calculation.

Thanks.

"FSt1" wrote:

hi
sorry. i assumed that since you posted under programming that you had code.
how do you refresh the queries.

regards
FSt1

"Accesshelp" wrote:

FSt1,

Thanks for your response. I do not have any code for my querytables.

The querytables are created via DataImport External DataNew Database Query.

Thanks.

"FSt1" wrote:

hi
without seeing our code, i can only guess that you are allowing background
queries. I would suggest that you add something like this.......
range("A1").querytable.refresh backgroundquery:=false
this will pause code until the refresh is completed. and since your project
is dependent on a formula, i would add the calculate command after each
refresh.

post your code and i'll see if i can rework it.

Regards
FSt1

"Accesshelp" wrote:

Hello all,

I have 3 querytables that connects to an external database in my Excel file.
All 3 query tables are looking up 3 values in worksheet "Data". The 3
values are in Cells D15, D16 and D17 of the Data worksheet. The way I have
the querytables to work is as followed:

1. I populate a value in Cell D15, and data populates for Querytable 1.
2. For Cell D16, I have a Vlookup formula to grab the data from Querytable
1 by looking up the value in Cell D15. The Querytable 2 then populates based
on the value in Cell D16.
3. For Cell D17, I have the same setup as Cell D16. The Querytable 3 is
populated based on the value in Cell D17.

The problem that I have is since Querytables 2 and 3 are looking up the
values in Cells D16 and D17, the Querytables 2 and 3 start refreshing before
the values in Cells D16 and D17 populate. Because of that, I receive error
messages for not able to refresh the Querytables 2 and 3.

Is there a way to control the refresh on Querytables 2 and 3 that refresh
after the values are populated in Cells D16 and D17 by Vlookup, respectively?

Thanks.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
QueryTables Tomo Excel Programming 1 June 3rd 08 04:33 AM
QueryTables.Add and XML Sönke Schreiber Excel Programming 0 September 20th 06 11:43 AM
QueryTables command Sharlene England Excel Programming 0 January 4th 06 11:43 PM
QueryTables Add Marta[_3_] Excel Programming 0 January 19th 05 05:32 PM


All times are GMT +1. The time now is 11:55 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"