Solved: Cancel Call to Oracle Database

[abhiker]

i have a vb subroutine in excel that calls an Oracle database. Is there any way to cancel the call to the database before it completes. Excel just sits and waits for the return. i would like to get control back of excel so that i could cancel gracefully if required. thanks in advance for any suggestions.

[JimmyTheHand]

Assuming that there is a QueryTable on Sheet1, Cell A1

1. Create a userform, set its caption to "Retrieving data..." (or something).
2. Add a button, set its caption to "Cancel" (or something).
3. Set the form's ShowModal property to False
4. Add this code to the forms code module:
[vba]Private Sub CommandButton1_Click()
Dim WS As Worksheet
Set WS = Sheets("Munka1")
WS.Range("A1").QueryTable.CancelRefresh
Application.OnTime EarliestTime:=T + 1 / 24 / 60 / 60, Procedure:="Check", Schedule:=False
MsgBox "Data retrieval cancelled."
Unload Me
End Sub

Private Sub UserForm_Initialize()
Check
End Sub
[/vba]
5. Add this code to a standard code module:
[vba]
Public T As Date
Sub StartRefreshing()
Dim WS As Worksheet
Set WS = Sheets("Sheet1")
WS.Range("A1").QueryTable.Refresh BackgroundQuery:=True
UserForm1.Show
End Sub

Sub Check()
Dim WS As Worksheet
Set WS = Sheets("Sheet1")
If Not WS.Range("A1").QueryTable.Refreshing Then
Unload UserForm1
Else
T = Now
Application.OnTime T + 1 / 24 / 60 / 60, Procedure:="Check", Schedule:=True
End If
End Sub
[/vba]
6. Start refreshing the query by executing the sub StartRefreshing.

HTH

Jimmy

[abhiker]

thank you for the help.