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
Is it possible to do a web refresh to less than 1 minute by changing the class variable in a similar fashion?
Ryan: I believe the best you can do is one second.
Dick: How do I get Excel to do a Web Refresh in 1 second?
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
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 (dd.mm.yyyy) 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
Bartek
Have a look at parameters
http://www.dicks-clicks.com/excel/ExternalData6.htm#Parameters
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