Reputation: 21
I have created a SQL view on the AS/400 called SHIPVIEW and I then am connecting to this view from Excel using an ODBC connection.
I have also verified that if the data in the view changes and I press the "Refresh All" in Excel under the Data tab that the data in Excel is also being updated correctly.
I now need to create some VBA code that when the workbook is opened or when the "Refresh All" is selected, that it kicks off the VBA code to do some calculations on the data that was just imported.
I found the below link, but I didn't have any luck getting it to work.
I'm new to VBA, so any answers would be welcomed!
Upvotes: 2
Views: 2941
Reputation: 71247
Presumably the view is being dumped onto a worksheet; Excel creates a refreshable QueryTable
object for it, and the QueryTable
object exposes BeforeRefresh
and AfterRefresh
events you can handle.
This is done by declaring a WithEvents
object variable of the appropriate type in a class module (WithEvents
is illegal in a standard procedural module), and then selecting the WithEvents
variable in the top-left code pane dropdown, then the event we want to handle in the top-right dropdown:
Assuming the table exists in the workbook at compile-time (i.e. it's not being created from scratch by some other VBA code), you could have something like this in the ThisWorkbook
module:
Option Explicit
Private WithEvents QT As QueryTable
Private Sub QT_AfterRefresh(ByVal Success As Boolean)
If Success Then
'do stuff
End If
End Sub
Private Sub Workbook_Open()
Set QT = Sheet1.QueryTables(1) 'adjust accordingly
End Sub
If the QueryTable
is spawned at run-time by other code, then you could make QT
public, and assign it from the code that's responsible for creating it.
Note that these QueryTable events will fire regardless of how the QT was refreshed: whether it was a right-click to refresh that query, or a click on "refresh all", makes no difference.
Upvotes: 2