View Full Version : Solved: Cancel Call to Oracle Database
abhiker
12-17-2008, 02:44 PM
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
12-18-2008, 03:24 AM
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:
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
5. Add this code to a standard code module:
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
6. Start refreshing the query by executing the sub StartRefreshing.
HTH
Jimmy
abhiker
12-18-2008, 02:24 PM
thank you for the help.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.