running macro after refresh of extenal data

[Guest]

I have a worksheet that is populated with data from an external source. I
need to go through the data on the worksheet and extract values that will be
used in a couple of lists in the workbook after the data has been refreshed.
The refresh on open option is turned on. I added the code to the workbook
open event but that results in the code executing prior to the prompt to
refresh the external data. Is there another event where the code should be
located? Is there any other way that I can run the code after the refresh of
the external data?

 

[Tom Ogilvy]

http://support.microsoft.com/kb/213187/en-us
XL2000: How to Use the Query BeforeRefresh and AfterRefresh Events

 

[Guest]

Thanks for the link. I have attempted to replicate the code but I am getting
a subscript out of range error.
Here is the class
Public WithEvents qt As QueryTable

Private Sub qt_RebuildGeoLoc_AfterRefresh(ByVal Success As Boolean)
Dim m_iLastRow As Integer
Dim response As Integer
'*** Find last row
ActiveCell.SpecialCells(xlLastCell).Select
ActiveCell.Offset(rowoffset:=1, columnoffset:=0).Select
m_iLastRow = ActiveCell.Row
response = MsgBox(Prompt:="Active last cell is " + m_iLastRow,
Buttons:=vbInformation)
End Sub

Here is the module code
im x As New Class1
Sub RebuildGeoLoc()
Set x.qt = ThisWorkbook.Sheets("RatesByLevel").QueryTables("qRatesByLevel")
End Sub

Opened the workbook and nothing happened so I added the following to the
Open event.
Call RebuildGeoLoc

 

[Tom Ogilvy]

My guess is that your worksheet isn't named RatesbyLevel. Maybe it has a
space on either end. or you don't have a querytable named qRatesByLevel.

As per your last statement, Yes, you do need to do something to tie your
class to the querytable.