Execute VBA before an external data TABLE is updated

I'm linking to an external data file and pulling the results into a table.   The table is the special table type that was introduced in Excel 2007.

I need a way to run some vba code right before the data is refreshed i.e. updated.

Is there there an event I can use to trigger vba?

Who is Participating?
Rory ArchibaldConnect With a Mentor Commented:
The refresh on open will happen before any code runs so you'll need to turn that off and then use the Workbook_Open event to refresh it. Then in the sheet containing the table, you will need code like:
Private WithEvents qt As QueryTable

Private Sub qt_BeforeRefresh(Cancel As Boolean)
   MsgBox "about to refresh"
End Sub

Private Sub Worksheet_Activate()
End Sub
Public Sub hookqt()
   Set qt = Me.ListObjects(1).QueryTable
End Sub

Open in new window

and in the Workbook_Open event:
Private sub workbook_open()
with sheets("Sheet1")
End With
End Sub

Open in new window

Rory ArchibaldCommented:
Is the table being refreshed manually?
lee_jdAuthor Commented:
No, it refreshes when the file is open and then automatically every 30 minutes.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.