See this post for a really good answer on alternatives to this Excel VBA - QueryTable AfterRefresh function not being called after Refresh completes by KazJaw.
#Excel vba on event code
However, this is not the best approach but will work if you don't mind waiting on the query before any other code in the Sub Module runs. To get around this, you can run it synchronously by setting qt.Refresh False The reason for this is the reference to the query table will disappear when the module finishes executing, which is likely to finish before the query finishes executing. The one caveat with this approach is that the AfterRefresh will not be called if this is run asynchronously and left as is. Qt.Refresh False ' See link at bottom of post for alternatives to this Qt.CommandText = "Select * From someTable" Set qtDict = UtilFunctions.CollectAllQueryTablesToDict Set classQtEvents = New CQtEvents ' Instantiate the Class 'Dependencies: Microsoft Scripting Runtime (Tools->References) for Dictionary (HashTable) object The key thing to note above is the WithEvents keyword and the declarations/definitions for BeforeRefresh and AfterRefresh.īelow is what the code might look like to leverage the Class Module defined above Option Explicit
'Insert logic you want to run after a refresh
Private Sub mQryTble_AfterRefresh(ByVal Success As Boolean) 'Insert logic you want to run before a refresh Private Sub mQryTble_BeforeRefresh(ByVal Cancel as Boolean) Public Property Get QryTble() As QueryTable: Set QryTble = mQryTble: Public Property Set QryTble(ByVal QryTable As QueryTable): Set mQryTble = QryTable: ' Add variables you may want to cache here such at the query or connection settings Private WithEvents mQryTble As Excel.QueryTable Here is what the class module might look like in a class module called CQtEvents Option Explicit It becomes especially useful when your Excel Workbook has multiple QueryTables. This allows for more modular, reusable code. One pattern you can utilize is keeping the QueryTable callback events in a separate Class Module instead of embedded within a worksheet. I realize this thread is old, but there is a decent alternative to the other solution posted. You can do the followings with Excel VBA Application.OnTime Method schedule a procedure to be run at a specified time in the future schedule a procedure to be run after a specific. Excel VBA Application.OnTime Method to trigger event at specified time.
#Excel vba on event windows
I ran across this same issue recently and it was very difficult to find a good answer. Auto Open Excel with Windows Task Scheduler.
0 kommentar(er)
