Worksheet TableUpdate Event

Creating a data model

You can make a data model by retrieving the data from a file, web, table, database or from any other source listed in the Data > Get Data as shown in the following image:

In our example, we’ll retrieve the data from a file. Create a text file and insert some data into it. For example, I have entered the following data into a blank text file and saved it as data.txt using notepad:

a,b,c
1,5,18
10,12,16
12,15,1
1,2,10

Back to Excel, Click Data tab and select From Text/CSV from the Get & Transform Data section to import the data from the data.txt file:

Select data.txt file:

The next window shows the preview of data in a table form. Click Load To... button from the window as shown below image:

Select the Add this data to the Data Model and click ok:

Data will show on a new Worksheet and a query (data) will also made which visible on the left side of the sheet under the Queries & Connection:

If the data in the data.txt file changes, the changes will not be automatically updated on the table. To update the changes click Data tab and click Refresh All menu from the Queries & Connections section:

Worksheet_TableUpdate

The TableUpdate event occurs after a query table connected to a data model is updated. The Worksheet_TableUpdate procedures must be in the code module for that worksheet. You can quickly access that code window by right-clicking the worksheet’s tab and selecting the View Code:

In our data-model, the data imported to new sheet, Sheet2. Right-click on the Sheet2 tab, select View Code and write the following code.

Private Sub Worksheet_TableUpdate(ByVal Target As TableObject)
 MsgBox "Data from the data.txt has been updated"
End Sub

Back to Sheet2 and click Data tab and click Refresh All menu from the Queries & Connections section, it will reload (update) the data from the data.txt file and Excel fires the TableUpdate event which executes the Worksheet_TableUpdate event procedure: