running macro after refresh of extenal data
 

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

--
Regards,
Tom Ogilvy


"djd" wrote in message
...
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?

running macro after refresh of extenal data
 

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("q RatesByLevel")
End Sub

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


"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy


"djd" wrote in message
...
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?

running macro after refresh of extenal data
 

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.

--
Regards,
Tom Ogilvy

"djd" wrote in message
...
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("q RatesByLevel")
End Sub

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


"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy


"djd" wrote in message
...
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?