-
Detect Timeout?
I have an Excel sheet with a simple macro that uploads the information on the sheet to a local Access DB.
Code:
Dim cn As Object
Dim rs1 As String, scn As String, dbWb As String, dsh As String
Dim ssDelete As String, ssql As String
Dim rng As Range
Set rng = rInput.CurrentRegion
Set cn = CreateObject("ADODB.Connection")
dbWb = Application.ActiveWorkbook.FullName
scn = "Provider=" & strPROVIDER & ";Data Source=" & ActiveWorkbook.Path & "\" & strDB
ssql = "INSERT INTO " & strTable & " "
dsh = "[" & rng.Parent.Name & "$" & rng.Address(False, False) & "]"
ssql = ssql & "SELECT * FROM [" & ExcelDriver & ";HDR=YES;DATABASE=" & dbWb & "]." & dsh
ssDelete = "delete * from [" & strTable & "];"
With cn
.Open scn
If flg = True Then .Execute ssDelete
.Execute ssql
.Close
End With
Set cn = Nothing
The Access DB is set up such that it automatically updates a SharePoint list. If there is a connection issue to SharePoint (e.g. expired credentials), the Excel file just sits in a hung state and never completes its task. I'd like to know if there is some way I can time the request to update the Access DB and abort the command if a pre-determined timeout threshold is exceeded. Right now, the user has to kill the Excel application from the Task Manager which isn't very elegant.
-
Re: Detect Timeout?
have you tried using a timer to detect how long the macro has been running and if need be end or close excel
i doubt there is any less extreme method to terminate the code, though a stop statement may allow you to break all code in the IDE
i believe you may be able to change the timeout value for ADO, but if it is just hanging that may not make any difference