如何在不导入新文件的情况下异步发出请求

Question

我正在尝试执行 SOAP 调用，但有时响应是数千条记录。我不希望 Excel 在处理响应时冻结它，但我看到的每个示例都需要导入一个带有异步回调的单独文件。有没有办法在没有额外文件的情况下做到这一点？

Public Function SendPost(currentTableAltIden As String, altIdentifiers() As String, Optional aSync As Boolean = True)
Dim t As XMLHTTP60
Dim r As MSXML2.DOMDocument60
Dim nodeList As IXMLDOMNodeList
Dim i As Integer
Dim listLengthControl As Integer
Dim listCounter As Integer
Dim xmlHelper As AsyncHelper
i = 0
Set t = Transport
Set xmlHelper = New AsyncHelper
xmlHelper.init t
t.Open "POST", EndPointUrl, aSync


t.send Text

Set r = New MSXML2.DOMDocument60
r.aSync = False
r.validateOnParse = False
r.SetProperty "SelectionNamespaces", " xmlns:soap='http://schemas.xmlsoap.org/soap/envelope/'"
r.LoadXML t.responseText

Set nodeList = r.SelectNodes("//result//" & currentTableAltIden)
listLengthControl = nodeList.Length

While listCounter <> listLengthControl
For i = LBound(altIdentifiers) To UBound(altIdentifiers)
Debug.Print r.SelectNodes("//result//" & currentTableAltIden & "//" & altIdentifiers(i))(listCounter).Text
WorkWebServiceTemp.Cells(TableDataRowNum + listCounter, i + 1).value = r.SelectNodes("//result//" & currentTableAltIden & "//" & altIdentifiers(i))(listCounter).Text
Next i
listCounter = listCounter + 1
Wend

End Function

Answer 1

只是为了好玩...

Sub AsyncTester()
Static col As Collection
Set col = New Collection
col.Add AsyncFetcher("https://stackoverflow.com/", New clsTest)
col.Add AsyncFetcher("https://google.com/", New clsTest)
col.Add AsyncFetcher("https://news.ycombinator.com/", New clsTest)
Debug.Print "finished setup"
End Sub

Function AsyncFetcher(url As String, callBackObject As Object)
Dim sc As Object
Set sc = CreateObject("MSScriptControl.ScriptControl")
sc.Language = "JScript"
sc.AddCode "var objXML, theUrl, objCallBack;"
sc.AddCode " function callBack(){objCallBack.Report(objXML.readystate + ' ' + theUrl);}  " & vbLf
sc.AddCode " function fetch(url, obj){                                    " & vbLf & _
"   objCallBack = obj; theUrl = url;                           " & vbLf & _
"   objXML = new ActiveXObject('Msxml2.XMLHttp.6.0');          " & vbLf & _
"   objXML.onreadystatechange = callBack;                      " & _
"   objXML.open(""GET"", url, true);                           " & vbLf & _
"   objXML.send();                                             " & vbLf & _
"}                                                             "
sc.Run "fetch", url, callBackObject
Set AsyncFetcher = sc
End Function

测试：

Public Sub Report(s)
Debug.Print s
End Sub