Home |
Search |
Today's Posts |
|
#1
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.programming
|
|||
|
|||
![]()
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 |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
QueryTables | Excel Programming | |||
QueryTables.Add and XML | Excel Programming | |||
QueryTables command | Excel Programming | |||
QueryTables Add | Excel Programming |