我试图从“www.example.com”访问RSS提要https://www.higheredjobs.com/rss/。有几个“.cfm”链接。当我试图访问链接“https://www.higheredjobs.com/rss/articleFeed.cfm”.它给我错误“系统无法定位指定的资源”.然而,它可以在包含具有XML的HTML的浏览器中打开。我需要从XML到Excel的数据。但首先,我不能使用VBA通过HTTP请求访问.cfm或XML。
有人能帮助我理解为什么我无法使用VBA中的HTTP请求访问.cfm或XML文件吗?是否有从该源检索XML数据的替代方法?
Sub ParseHTMLtoXML2()
Dim url As String
Dim xmlHTTP As Object
Dim htmlDoc As Object
Dim rssElement As Object
Dim channelElement As Object
Dim itemElements As Object
url = "https://www.higheredjobs.com/rss/articleFeed.cfm"
Set xmlHTTP = CreateObject("MSXML2.XMLHTTP")
xmlHTTP.Open "GET", url, False
xmlHTTP.send
'//*[@id="webkit-xml-viewer-source-xml"]/rss/channel/item[1]
Set htmlDoc = CreateObject("HTMLFile")
htmlDoc.body.innerHTML = xmlHTTP.responseText
Set rssElement = htmlDoc.getElementsByTagName("rss")(0)
Set channelElement = rssElement.getElementsByTagName("channel")(0)
Set itemElements = channelElement.getElementsByTagName("item")
Dim item As Object
For Each item In itemElements
MsgBox item.getElementsByTagName("title")(0).innerText
Next item
Set xmlHTTP = Nothing
Set htmlDoc = Nothing
End Sub
1条答案
按热度按时间ttcibm8c1#
尝试使用powershell脚本将其转换为csv。最好将输出设置为制表符分隔而不是逗号分隔。