在VBA中解析XML响应并仅提取最后一个数据 [英] Parsing XML Response in VBA and extracting only last data

问题描述

我正在尝试通过excel vba对内部链接之一(在我们公司中)进行XML请求.当我使用下面的代码发送请求并接收响应时，得到以下作为响应文本:

I'm trying to do a XML request through excel vba for one of the internal links (in our company). when i send request and receive response using the code below, i get the following as response text:

[{"CPN":"700-42887-01","ExtractDt":"2018-04-02 
00:00:00","Demand":"8645"},"CPN":"700-42887-01","ExtractDt":"2018-04-09 
00:00:00","Demand":"8985"},{"CPN":"700-42887-01","ExtractDt":"2018-04-16 
00:00:00","Demand":"9341"},{"CPN":"700-42887-01","ExtractDt":"2018-04-23 
00:00:00","Demand":"9589"},{"CPN":"700-42887-01","ExtractDt":"2018-04-30 
00:00:00","Demand":"9210"},{"CPN":"700-42887-01","ExtractDt":"2018-05-07 
00:00:00","Demand":"9698"},{"CPN":"700-42887-01","ExtractDt":"2018-05-14 
00:00:00","Demand":"9542"},{"CPN":"700-42887-01","ExtractDt":"2018-05-21 
00:00:00","Demand":"9692"},{"CPN":"700-42887-01","ExtractDt":"2018-05-28 
00:00:00","Demand":"10416"},{"CPN":"700-42887-01","ExtractDt":"2018-06-04 
00:00:00","Demand":"6777"},{"CPN":"700-42887-01","ExtractDt":"2018-06-11 
00:00:00","Demand":"12774"},{"CPN":"700-42887-01","ExtractDt":"2018-06-18 
00:00:00","Demand":"12912"},{"CPN":"700-42887-01","ExtractDt":"2018-06-25 
00:00:00","Demand":"12693"},{"CPN":"700-42887-01","ExtractDt":"2018-07-02 
00:00:00","Demand":"12895"},{"CPN":"700-42887-01","ExtractDt":"2018-07-09 
00:00:00","Demand":"13366"},{"CPN":"700-42887-01","ExtractDt":"2018-07-16 
00:00:00","Demand":"13550"},{"CPN":"700-42887-01","ExtractDt":"2018-07-23 
00:00:00","Demand":"7971"},{"CPN":"700-42887-01","ExtractDt":"2018-07-30 
00:00:00","Demand":"12442"},{"CPN":"700-42887-01","ExtractDt":"2018-08-06 
00:00:00","Demand":"12960"},{"CPN":"700-42887-01","ExtractDt":"2018-08-13 
00:00:00","Demand":"14106"},{"CPN":"700-42887-01","ExtractDt":"2018-08-20 
00:00:00","Demand":"13543"},{"CPN":"700-42887-01","ExtractDt":"2018-08-27 
00:00:00","Demand":"13570"},{"CPN":"700-42887-01","ExtractDt":"2018-09-03 
00:00:00","Demand":"13506"},{"CPN":"700-42887-01","ExtractDt":"2018-09-10 
00:00:00","Demand":"13914"},{"CPN":"700-42887-01","ExtractDt":"2018-09-17 
00:00:00","Demand":"13241"},{"CPN":"700-42887-01","ExtractDt":"2018-09-24 
00:00:00","Demand":"13449"}]

我只想提取最后一个值-即13449.要实现此目的，我需要编写什么代码.

I want to extract only the last Value - Namely 13449. What is the code that i need to write to accomplish this.

预先感谢！`

使用的代码

 Sub xmlparsing()
 Dim jstring As String

 With CreateObject("MSXML2.XMLHTTP")
  .Open "GET", "**INTERNAL COMPANY LINK HERE**", False
    .send
  If .Status <> 200 Then Exit Sub
  jstring = .responseText
   Debug.Print jstring
   End With`
 End Sub

推荐答案

您可以使用InStrRev

You could use InStrRev

Mid$(responseText, InStrRev(responseText, ":") + 2, (InStrRev(responseText, "}") - 1) - (InStrRev(responseText, ":") + 2))

InStrRev 将字符串从右向左移动.我们知道您想在字符串的末尾使用该值，因此此方向很有用.我们指定要查找的字符作为参数.整个字符串为 responseText .

InStrRev walks the string from right to left. We know you want the value at the end of the string so this direction is useful. We specify as an argument the character to find. The overall string is the responseText.

要找到的第一个字符是从右到左的:" .这将是您在:"13449"}] 的位置.从此+ 2偏移以获取所需值的实际开始，本例中为 13449 中的 1 .

The first character to find is ":", from right to left. This will be where you have :"13449"}]. Offset from this + 2 to get the actual start of the value you want, in this case the 1 in 13449.

确定字符串终点的逻辑相同.我使用}" 作为终点，然后进行调整以前进至数字. Mid 允许您指定字符串，起点和字符数.我传递参数以将所需的字符串提取到 Mid .在处理字符串时，我使用类型化的函数(最后以 $ 结尾)更为有效.

Same logic to determine end point of string. I use "}" as end point then make an adjustment to move forward to the numbers. Mid allows you to specify a string, start point and number of characters. I pass the arguments to extract the required string to Mid. I used typed functions (with the $ at the end) as more efficient when working with strings.

这篇关于在VBA中解析XML响应并仅提取最后一个数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！