Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 432
  • Last Modified:

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?

Lee
0
lee_jd
Asked:
lee_jd
  • 2
1 Solution
 
Rory ArchibaldCommented:
Is the table being refreshed manually?
0
 
lee_jdAuthor Commented:
No, it refreshes when the file is open and then automatically every 30 minutes.
0
 
Rory ArchibaldCommented:
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()
   hookqt
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")
   .hookqt
   .Listobjects(1).Refresh
End With
End Sub

Open in new window

0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now