![excel query table wait for ready excel query table wait for ready](https://wmfexcel.files.wordpress.com/2018/05/excel-tip-price-book-with-pq2-2.png)
This macro works with Excel 2010, 20, and should survive longer than the original version I posted. LTest = InStr(1, cn.OLEDBConnection.Connection, "Provider=.1") If it is a connection that is built against Power Query engine (Microsoft.Mashup), then it will refresh the connection, otherwise it will ignore it.ĭim lTest As Long, cn As WorkbookConnection The macro below is a more targeted approach that checks the source of the query.
![excel query table wait for ready excel query table wait for ready](https://i0.wp.com/uviwebstorage.blob.core.windows.net/whitepagesstore/2014/01/image8.png)
I want create a timer that will look at when excel is refreshing, wait till its done and then run the rest of. This connection can appear in your workbook as a table. Depending on my location and internet speed it sometimes takes 2 seconds to load and sometimes 8 seconds. You can connect your Excel workbook to an external data source, such as a SQL Server database, an OLAP cube, or even another Excel workbook.
Excel query table wait for ready code#
When done refreshing the rest of the code is run. It makes more sense to try and find a method that is cannot be broken by a simple name change. There is a waiting period for the refreshing. While that works, it’s still not ideal, as names can be volatile. So the code to check a RecordCount >0 should work fine. I have seen an approach where someone modified the code I provided earlier to include a variable, allowing you to easily change the prefixed text from Power Query to something else. It seems that it isnt a timing issue - waiting for the query to finish, simply the nature of the RecordCount property being either 0 or 1 until a MoveLast is done, which explainds why I thought I was finding only one record when I first encountered the problem. Sub SettingSleepWithoutSleepFunction() MsgBox Now Application. Application.Wait (Now + TimeValue('0:01:00')) Sample Code. But sleep sometimes wont work in Excel vba. (As we move closer to Excel 2016, we are now aware that these queries will NOT have names starting with Power Query.) If you want to make your script wait in vba you have to use sleep. The challenge with that specific macro is that it refers to the connection name, which may not always start with Power Query. Some time back I posted a routine to refresh Power Queries with VBA, allowing you to refresh all Power Queries in a workbook.