pause code while Query is refreshing, then continue

D

Damian

I have a worksheet that Needs to refresh its query and then run code, BUT my
problem is while it takes few seconds to refresh it the code runs and gives
me the result before query refresh.

How can I fix that?

Thank You
 
F

FSt1

hi
i am guessing that you are allowing a background query. the code will pause
if you do not allow for the query to refresh in the back ground. not sure how
you are refreshing now but in my code i use something like this.....
sheets("sheet1").range("A1").querytable.refresh backgroundquery:=false
code will pause until the refresh is done.

Regards
FSt1
 
D

Damian

Great thank You.
"sheets("sheet1").range("A1").querytable.refresh backgroundquery:=false"

Worked Great. Thats what I needed.
My Code: "ActiveSheet.QueryTables(1).Refresh BackgroundQuery:=False"
 
F

FSt1

thanks for the feedback
regards
FSt1

Damian said:
Great thank You.
"sheets("sheet1").range("A1").querytable.refresh backgroundquery:=false"

Worked Great. Thats what I needed.
My Code: "ActiveSheet.QueryTables(1).Refresh BackgroundQuery:=False"
 
A

AFSSkier

FSt1,

I tried your suggestion & recieved a Run-time error 1004
sheets("sheet1").range("A1").querytable.refresh backgroundquery:=false

I recorded the following with the macro recorder. But when I copy it into
my code, I still get a Run-time error 1004. I'm trying to run in a
cboBox1_Change sub located on Sheet3 that runs a refresh code to ReQuery
inbound data for a ListFillRange for cboBox2, then Enables cboBox2.
Sheets("Sheet2").Select
Range("A1").Select
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False

I was able to get this code to work. However when I hide the query sheet2,
I get the Run-time error 1004. How do I refresh a single Hidden QueryTable?
I tried the following, but get a Run-time error 438.
With Sheets("Sheet2").Select
.querytable.refresh backgroundquery:=false
End With
 
A

AFSSkier

**Resolved**

Worksheets("Sheet2").Visible = xlsheetVisible 'UnHide Sheet2
Worksheets("Sheet2").Range("A1").Select 'select within Range of
QueryTable
Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False 'False
pauses code until Refresh is complete
Worksheets("Sheet2").Visible = xlsheetVeryHidden '(xlsheetHidden)
ReHides Sheet2
Worksheets("Sheet1").Select 'Returns to UnHidden sheet1
Range("A1").Select
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top