excel - 如何使用 Excel VBA 解析 xml 文件？

Question

我想在 Excel VBA 中解析一个 XML 文件。

xml 文件如下所示：

<Dummy docType="GetToken" copyright="Copyright Dummy Inc." versionNumber="1.7" createdDate="2018-09-25T13:03:36Z" statusId="0" statusText="" responseId="e1c967ec-607c-4cd0-94f1-8eee5f506c93">
   <AuthResponse>
         <AuthToken expiry="2018-09-25T14:02:00Z">ABCDEF123</AuthToken>
         <ServerPath>site.Dummy.com/traffic/Dummy.ashx</ServerPath>
         <ServerPaths>
               <ServerPath type="API" region=“X">http://eu.api.Dummy.com/Dummy.ashx</ServerPath>
          </ServerPaths>
    </AuthResponse>

我想解析 AuthToken 值以在后续调用中使用。

当前代码。

Sub GetToken()

Dim ws As Worksheet: Set ws = Worksheets("APICallInfo")
Dim strURL As String
strURL = ws.[TokenURL]

Dim hReq As New WinHttpRequest
hReq.Open "Get", strURL & "&Vendorid=" & VendorID & "&Consumerid=" & ConsumerID
hReq.Send

Dim strResp As String
strResp = hReq.ResponseText

Dim xmlDoc As MSXML2.DOMDocument60
Set xmlDoc = New MSXML2.DOMDocument60
If Not xmlDoc.LoadXML(strResp) Then
    MsgBox "Load Error"
End If

Dim Token As IXMLDOMNode
Set Token = xmlDoc.FirstChild

Debug.Print Token.SelectSingleNode("AuthToken").Text

End Sub

我收到一条消息，提示“运行时错误 '9'：下标超出范围”。

标签： excelvbaxml-parsing