query table in excel not refreshing
Posted on 2014-11-17
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.