Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

query table in excel not refreshing

Posted on 2014-11-17
5
Medium Priority
?
177 Views
Last Modified: 2014-12-06
I have an excel workbook that is connected to an access query that imports the access data into one of my worksheets as a table. I have a vba script that auto opens this worbook, and tells it to refresh all contents including the query table and the other worksheets that have formulas dependent in the query table. When i run the speeadsheet manually, the query imports perfectly. But when the spreadsheet is auto run from the task manager, the query table doesnt update fast enough before the rest of the vba code proceeds and closes the worbook.  I tried disabling background refresh in the excel data connection options, and tried adding some code in vba sucha as DoEvents, application.refreshall, etc but still doesnt help.

Can someone provide some vba code that calls the query object and then validate that the refresh is completed for that specific query? Lets say my workbook is called worbook1.xlsm, the query table is called query1table, and query1table is located on sheet2.  I see this is a common problem, but i havent found a solution yet.
0
Comment
Question by:jtencha
  • 3
  • 2
5 Comments
 
LVL 23

Expert Comment

by:Michael Fowler
ID: 40448976
Try

Application.CalculateUntilAsyncQueriesDone

after calling the refresh command

http://msdn.microsoft.com/en-us/library/office/ff821008(v=office.15).aspx
0
 

Author Comment

by:jtencha
ID: 40451222
Thanks. I tried that also, didnt help. Is there querytable property that can be verified that refresh is complete, and continhe reading the rest of my code? Btw, the code is auto launched from a private sub from  'this worbook' module.
0
 
LVL 23

Accepted Solution

by:
Michael Fowler earned 1500 total points
ID: 40451272
Is this what you are after

QueryTable.Refreshing Property
http://msdn.microsoft.com/en-us/library/office/ff834459(v=office.15).aspx
0
 

Author Comment

by:jtencha
ID: 40453022
ok, will try that, will let you know how it goes
0
 

Author Closing Comment

by:jtencha
ID: 40485049
thanks
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

971 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question