Solved

Execute VBA before an external data TABLE is updated

Posted on 2011-02-18
3
379 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 500 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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How do I get SLA using Excel Raw Data 12 32
Excel 2010 - Column Heading Formating 6 14
Excel Conditional Median Problem 1 15
Data in Rows to be converted into single row 9 39
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
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.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

785 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