Solved

Execute VBA before an external data TABLE is updated

Posted on 2011-02-18
3
391 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

628 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