Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Execute VBA before an external data TABLE is updated

Posted on 2011-02-18
3
Medium Priority
?
411 Views
Last Modified: 2012-05-11
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
Comment
Question by:lee_jd
  • 2
3 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 34924626
Is the table being refreshed manually?
0
 
LVL 2

Author Comment

by:lee_jd
ID: 34924659
No, it refreshes when the file is open and then automatically every 30 minutes.
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 2000 total points
ID: 34924673
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

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question