我一直在尝试从网站上提取一些XML数据,但似乎无法使我的循环在每个应计日期之间循环,并且最后一个元素应该是总费率,而不是单日费率。下面是代码,请谨慎,因为我也正在学习VBA和XML。感谢您提供的任何帮助。
Sub getXMLFRN()
Dim xmldoc As MSXML2.DOMDocument60
Dim xnodesls As MSXML2.IXMLDOMNodeList
Dim xnode As MSXML2.IXMLDOMNode
Dim strcusip As String
Dim straccrualStart As String
Dim straccrualEnd As String
Dim strdailyAccruedInterestPer100 As String
Dim strinterestPaymentPeriodAccruedInterestPer100 As String
Set xmldoc = New MSXML2.DOMDocument60
xmldoc.async = False
xmldoc.Load ("http://www.treasurydirect.gov/TA_WS/FRN/912828D31?format=xml")
For i = 1 To 10
Application.Workbooks("xml test.xlsm").Worksheets("Sheet1").Range("A1:e1").Formula = Array("CUSIP", "Accrual Start", "Accrual End", "Daily Rate", "Total Rate")
strcusip = xmldoc.getElementsByTagName("cusip").Item(0).Text
straccrualStart = xmldoc.getElementsByTagName("accrualStart").Item(0).Text
straccrualEnd = xmldoc.getElementsByTagName("accrualEnd").Item(0).Text
strdailyAccruedInterestPer100 = xmldoc.getElementsByTagName("dailyAccruedInterestPer100").Item(0).Text
strinterestPaymentPeriodAccruedInterestPer100 = xmldoc.getElementsByTagName("interestPaymentPeriodAccruedInterestPer100").Item(0).Text
Sheets(1).Cells(i + 1, 1).Value = strcusip
Sheets(1).Cells(i + 1, 2).Value = straccrualStart
Sheets(1).Cells(i + 1, 3).Value = straccrualEnd
Sheets(1).Cells(i + 1, 4).Value = strdailyAccruedInterestPer100
Sheets(1).Cells(i + 1, 5).Value = interestPaymentPeriodAccruedInterestPer100
Next i
MsgBox ("Done")
End Sub
我无法使您的代码正常工作(以前我在DOMDocument60中遇到过此问题),但是如果我改为DOMDocument类型,则可以运行它。
尽管DomDocument60支持某些其他功能,但我对这些代码的工作感到惊讶。对于DOMDocument,该.Load
方法采用字符串(xml),而不是URL。当我运行您的代码时,在那一行上会出现错误。
无论如何,我都使用XMLHttp从该URL获取响应文本,并将该响应文本加载到xmldoc
问题在于,尽管有一个循环For i = 1 to 10
,但您并没有增加该getElementsByTagName
方法的索引位置-每次执行该方法时,您都会得到Item(0)
。将其更改为Item(i-1)
,这应该可以工作(假设至少有10个匹配元素(否则,可能会出现错误)。
Sub getXMLFRN()
Dim xmlRequest As MSXML2.XMLHTTP
Dim xmldoc As MSXML2.DOMDocument
Dim xnodesls As MSXML2.IXMLDOMNodeList
Dim xnode As MSXML2.IXMLDOMNode
Dim strcusip As String
Dim straccrualStart As String
Dim straccrualEnd As String
Dim strdailyAccruedInterestPer100 As String
Dim strinterestPaymentPeriodAccruedInterestPer100 As String
Set xmlRequest = New MSXML2.XMLHTTP
xmlRequest.Open "Get", "http://www.treasurydirect.gov/TA_WS/FRN/912828D31?format=xml", False
xmlRequest.Send
Set xmldoc = New MSXML2.DOMDocument
xmldoc.LoadXML xmlRequest.responseText
For i = 1 To 10
Application.Workbooks("xml test.xlsm").Worksheets("Sheet1").Range("A1:e1").Formula = Array("CUSIP", "Accrual Start", "Accrual End", "Daily Rate", "Total Rate")
strcusip = xmldoc.getElementsByTagName("cusip").Item(i-1).Text
straccrualStart = xmldoc.getElementsByTagName("accrualStart").Item(i-1).Text
straccrualEnd = xmldoc.getElementsByTagName("accrualEnd").Item(i-1).Text
strdailyAccruedInterestPer100 = xmldoc.getElementsByTagName("dailyAccruedInterestPer100").Item(i-1).Text
strinterestPaymentPeriodAccruedInterestPer100 = xmldoc.getElementsByTagName("interestPaymentPeriodAccruedInterestPer100").Item(i-1).Text
Sheets(1).Cells(i + 1, 1).Value = strcusip
Sheets(1).Cells(i + 1, 2).Value = straccrualStart
Sheets(1).Cells(i + 1, 3).Value = straccrualEnd
Sheets(1).Cells(i + 1, 4).Value = strdailyAccruedInterestPer100
Sheets(1).Cells(i + 1, 5).Value = interestPaymentPeriodAccruedInterestPer100
Next i
MsgBox ("Done")
End Sub
更新
要处理数量未知/不同的项目,请使用循环结构,而不是使用硬编码的“ 10”或“ 100”等,而是使用文档的相应最后索引。当然,这假定每个标记名称(“ cusip”,“ accrualstart”等)的编号相同,并且它们都“相关”(即“ cusip”。Item(0)与所有另一个.Item(0)
似乎是正确的)。
For i = 1 To xmldoc.getElementsByTagName("cusip").length
'''
'''
'''
Next i
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句