Classes: Other Events

I’ve been posting about using class modules to get at some events that may not be available otherwise. One last one that you mind find useful is for External Data. You can create a class for QueryTables and access the BeforeRefresh and AfterRefresh events.

You set it up the same as the others; create a variable in a class module using WithEvents, create a global variable in a standard module to hold the class instance, assign the class variable to the actual object, code the events. As always, here’s an example:

In a Standard Module

Option Explicit

Public gQTEvents As Class1

Sub AssignClass()

    Set gQTEvents = New Class1
    Set gQTEvents.gcQueryTable = Sheet1.QueryTables(1)
End Sub

In a Class Module

Option Explicit

Public WithEvents gcQueryTable As QueryTable
Dim mStartTime As Date

Private Sub gcQueryTable_AfterRefresh(ByVal Success As Boolean)

    MsgBox “This refresh took:” & vbNewLine & vbNewLine & _
        Format((Now – mStartTime), “hh:mm:ss”)
End Sub

Private Sub gcQueryTable_BeforeRefresh(Cancel As Boolean)

    mStartTime = Now
End Sub

Posted in Uncategorized

7 thoughts on “Classes: Other Events

  1. Is it possible to do a web refresh to less than 1 minute by changing the class variable in a similar fashion?

  2. Ryan: You said “1 minute”, I read “1 second” – I don’t know why. You can use the OnTime method to continually call a sub that refreshes. You pretty much won’t be able to do anything else while it’s running though.

    Sub RefreshWQ()

        Sheet1.QueryTables(1).Refresh False
        ‘Stop refreshing at 10:46 AM
        If Time < TimeSerial(10, 46, 0) Then
            Application.OnTime Now + TimeSerial(0, 0, 1), “RefreshWQ”
        End If
    End Sub

  3. Hi, having found this topic I would like to ask a question.
    Is there any possibility to change/edit existing in Excel database query from VBA?
    Data in my excel table can be updated by changing the date ( in the query (SQL) however I would not like to open Microsoft Query each time I want to get new data.
    Please kindly let me know. Thanks and regards. Bartek

  4. Dick,
    Thanks for your prompt reply. This is exactly what I was looking for HOWEVER.
    MS Query (in my case) says that query can not be represented graphically and that parameters are not available.
    I tried to include my SQL in a VB however it always return error at oQt.Refresh
    Thanks Bartek

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.