excel 无法通过VBA中的HTTP请求访问.cfm或XML

bfhwhh0e 于 2023-06-30 发布在其他

关注(0)|答案(1)|浏览(129)

我试图从“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

excel

来源：https://stackoverflow.com/questions/76560511/unable-to-access-cfm-or-xml-via-http-request-in-vba

1条答案

按热度按时间

ttcibm8c1#

尝试使用powershell脚本将其转换为csv。最好将输出设置为制表符分隔而不是逗号分隔。

using assembly System.Xml.Linq
$csv = 'c:\temp\test.csv'

$uri = 'https://www.higheredjobs.com/rss/articleFeed.cfm'

$doc = [System.Xml.Linq.XDocument]::Load($uri)
$atomNs = $doc.Root.GetNamespaceOfPrefix('atom')

$writer = [System.IO.StreamWriter]::new($csv)
$header = 'title,description,link,language,copyright,managingEditor,atom link'
$writer.WriteLine($header)
foreach($item in $doc.Descendants('item'))
{
   $title = '"' + $item.Element('title').Value + '"'
   $description = '"' + $item.Element('description').Value + '"'
   $link = '"' + $item.Element('link').Value + '"'
   $language = '"' + $item.Element('language').Value + '"'
   $copyright = '"' + $item.Element('copyright').Value + '"'
   $managingEditor = '"' + $item.Element('managingEditor').Value + '"'
   $atomLink = '"' + $item.Element($atomNs + 'link').Value + '"'
   $writer.Writeline([string]::Join(',',@($title,$description,$link,$language,$copyright,$managingEditor,$atomLink)))
}
$writer.Flush()
$writer.Close()

赞(0）回复(0）举报 2023-06-30

我来回答

excel 无法通过VBA中的HTTP请求访问.cfm或XML

1条答案

相关问题

热门标签

最新问答