After refresh, trigger script, then close spreadsheet...how?

Hi everyone,

I have a spreadsheet, after the external data is refreshed then I want this VB script to be triggered that exports the data to another database, then I need to close the spreadsheet. here is the code I have. It works but I would like it to happen after the data is refreshed.

Any help would be greatly appreciated.......

Public Sub Filltable()
Dim db As Database, rs As String, r As Long
Dim tdfNew As TableDef
Dim wrkODBC As Workspace

Set wrkODBC = CreateWorkspace("", "", "", dbUseODBC)
Set db = wrkODBC.OpenDatabase("compudog", _
False, False, _
"ODBC;DATABASE=compudog;DSN=nl350;")
db.Execute "Delete from rtq where Station_no='06JC002' and dt>'Jan 1, 2006'"
' get all records in a table
r = 2 ' the start row in the worksheet
Do While Len(Range("A" & r).Formula) > 0
' repeat until first empty cell in column A
rs = "INSERT INTO rtq (Station_no,DT, X, Y) select '"
' add values to each field in the record
rs = rs + Range("A" & r).Value + "'as A, "
rs = rs + Str(Range("B" & r).Value) + " as B, "
rs = rs + Str(Range("C" & r).Value) + " as C"
rs = rs + Str(Range("D" & r).Value) + " as D"
' add more fields if necessary...
db.Execute rs ' stores the new record
r = r + 1 ' next row
Loop
db.Close
Set db = Nothing
Set wrkODBC = Nothing

End Sub

Answer this question

After refresh, trigger script, then close spreadsheet...how?

Mauricio Castillo E
If Navaho's suggestion does not work for you:

the engineer would need some clarification in the problem stated in the email. He would need to know if the problem is with the code of the subroutine “Public Sub Filltable() “ or you just want to know how to get the subroutine “Public Sub Filltable() “ invoked on the refresh of external data.

-brenda (ISV Buddy Team)
Sam_2

Thanks for that. To answer the last question, the Public Sub Filltable() works good. I just want the subroutine “Public Sub Filltable() “ invoked on the refresh of external data. Then the spreadsheet to close.
robinjam
Per the support engineer:

Ask ISV to visit the following MSDN Article: http://msdn.microsoft.com/library/default.asp url=/library/en-us/vbaxl11/html/xlhowUsingQueryTableEvents_HV05255039.asp

this is a small code sample;

I created a workbook, and then I added an External Data Source.

Then I added a new class module and declare a QueryTable object with events

Write this code in the class module

Public WithEvents qtQueryTable As QueryTable

Private Sub qtQueryTable_AfterRefresh(ByVal Success As Boolean)

MsgBox ("qtQueryTable_AfterRefresh")

End Sub

Private Sub qtQueryTable_BeforeRefresh(Cancel As Boolean)

MsgBox ("qtQueryTable_BeforeRefresh")

End Sub

Sub InitQueryEvent(QT As Object)

Set qtQueryTable = QT

End Sub

Write this code in the Module1

Dim clsQueryTable As New Class1

Sub RunInitQTEvent()

'attach an event handler

clsQueryTable.InitQueryEvent _

QT:=Selection.QueryTable

' refresh the data

Selection.QueryTable.Refresh BackgroundQuery:=False

Excel.ActiveWorkbook.Close

End Sub

After this code is written invoke the macro named “RunInitQTEvent”

-brenda (ISV Buddy Team)
Chris McLeod
There is a QueryTable_AfterRefresh event. The article below explains how to use it:

XL97: How to Use the Query Before and AfterRefresh Events

In a Class Module you would have:

Public WithEvents qt As QueryTable

Private Sub qt_AfterRefresh(ByVal Success As Boolean)
If Success Then 'Query completed successfully
Filltable
Else 'Query failed or was cancelled
MsgBox "Query failed"
End If
End Sub

In a Module you would have:

Dim X As New Class1

Sub Initialize_It()
Set X.qt = ThisWorkbook.Sheets(1).QueryTables(1)
End Sub
After refresh, trigger script, then close spreadsheet...how?

Answer this question