将URL中的XML作为表格导入到Excel中

有人可以指向我正确的方向吗?解决方案我拼凑了一些类似的东西，以便从Zillow的api中获取数据，其中URL请求返回一些XML.以下是该代码的一些片段，应该可以帮助您开始工作.这样可以节省很多时间，但是我认为它可以带给您所有相关的部分.您可以在以下站点上查看Zillow数据的格式: http://www.zillow.com/howto/api/GetDeepComps.htm .希望这会有所帮助！ 子ZillowDeepCompVf()'必须从VBA编辑器中启用Miscrosoft XML v6.0'要启用，请转到工具">参考"，然后选中"Miscrosoft XML v6.0"旁边的框'设置网址URL ="http://www.zillow.com/webservice/GetDeepComps.htm?zws-id="&ZWSID和& zpid ="&rowZpid&& count ="&计数与& rentzestimate = true"'打开XML页面设置xmldoc = New MSXML2.DOMDocument60xmldoc.async = False'检查XML文档是否已加载如果xmldoc.Load(URL)然后设置xmlMessage = xmldoc.SelectSingleNode("//message/text")设置xmlMessageCode = xmldoc.SelectSingleNode("//message/code")'检查错误信息如果xmlMessageCode.Text<>0然后'返回错误信息DC.Range(ErrorMessage& i +标头)= xmlMessage.Text别的'从Zillow获取XML数据设置xmlComparables = xmldoc.SelectSingleNode("//response/properties/comparables")设置xmlCompList = xmlComparables.SelectNodes("comp")设置xmlPrincipal = xmldoc.SelectSingleNode("//response/properties/principal")环比同类产品我= 1标头=标头+ 1对于i = 1到xmlCompList.Length'家庭详细信息设置xmlComp = xmlCompList(i-1)设置xmlHomeDetails = xmlComp.SelectSingleNode("links/homedetails")如果xmlHomeDetails什么都没有，那么DC.Range(HomeDetails& i +标头)=没有可用的家庭详细信息"别的DC.Range(HomeDetails& i +标头).Formula ="= HYPERLINK(""& xmlHomeDetails.Text&"，""Zillow Details")"万一'检索街道地址，邮政编码，城市，zPID设置xmlStreetAddress = xmlComp.SelectSingleNode("address/street")设置xmlZipCode = xmlComp.SelectSingleNode(地址/邮政编码")设置xmlCity = xmlComp.SelectSingleNode("address/city")设置xmlState = xmlComp.SelectSingleNode("address/state")设置xmlZPID = xmlComp.SelectSingleNode("zpid")'将数据推送到电子表格如果xmlStreetAddress什么都没有，那么DC.Range(地址& i +标头)="N/A"别的DC.Range(地址& i +标头)= xmlStreetAddress.Text万一如果xmlCity什么都没有，那么DC.Range(City& i +标头)="N/A"别的DC.Range(City& i +标头)= xmlCity.Text万一如果xmlState什么都没有，那么DC.Range(状态& i +标头)="N/A"别的DC.Range(状态& i +标头)= xmlState.Text万一如果xmlZipCode什么都没有，那么DC.Range(ZipCode& i +标头)="N/A"别的DC.Range(ZipCode& i +标头)= xmlZipCode.Text万一如果xmlZPID无效DC.Range(pzpid& i +标头)="N/A"别的DC.Range(pzpid& i +标头)= xmlZPID.Text万一取回&推送LastSold日期设置xmllastSoldDate = xmlComp.SelectSingleNode("lastSoldDate")如果xmllastSoldDate什么都没有，那么DC.Range(LastSold& i +标头)="NA"别的DC.Range(LastSold& i +标头)= xmllastSoldDate.Text万一...接下来，我将通过组合进行迭代'排序数据设置数据= DC.Range(单元格(标题+ 1，1)，单元格(i +标题，LastColumn))Data.Sort Key1:= DC.Range("H"& i +标头)，order1:= xlDescending I want to use VBA to automate an process of polling XML data from an external URL to my Excel Workbook. This is the format of the URL (I have masked sensitive information).First, I want an VBA script to ask for the daterange (from-to), and put this into the URL.I get the following back using the browser to open the XML:<Report Type="Clearing" Generated="2015-10-22 16:59:58" MerchantID="MASKED" MerchantName="MASKED" FromDate="2015-10-21" ToDate="2015-10-23"><Batches> <Batch ID="MASKED"> <Host>EDB</Host> <Name>MASKED</Name> <ClearingDateTime>2015-10-22 04:15:00</ClearingDateTime> <Transactions> <Transaction ID="MASKED"> <ClearingResponseCode>00</ClearingResponseCode> <TerminalID>MASKED</TerminalID> <TerminalModel>Ingenico IWL250 GPRS</TerminalModel> <TransactionType>Sale</TransactionType> <SettleTransactionID>MASKED</SettleTransactionID> <Online>1</Online> <TransactionDateTime>2015-10-21 11:01:23</TransactionDateTime> <Amount>24000</Amount> <ReceiptNo>000465</ReceiptNo> <TerminalRRN>MASKED</TerminalRRN> <CardGroup>MasterCard</CardGroup> <ApprovalCode>MASKED</ApprovalCode> <CardIssuer>Foreign MasterCard/Eurocard</CardIssuer> <CardProduct>Foreign MC/EC</CardProduct ><CurrencyCode>NOK</CurrencyCode> <TerminalAppVersion>MASKED</TerminalAppVersion> <TerminalDateTime>2015-10-21 11:00:54</TerminalDateTime> <ExtraData/> </Transaction> <Transaction ID="MASKED"> <ClearingResponseCode>00</ClearingResponseCode> <TerminalID>MASKED</TerminalID> <TerminalModel>Ingenico IWL250 GPRS</TerminalModel> <TransactionType>Sale</TransactionType> <SettleTransactionID>3MASKED</SettleTransactionID> <Online>1</Online> <TransactionDateTime>2015-10-21 11:11:11</TransactionDateTime> <Amount>6000</Amount> <ReceiptNo>000466</ReceiptNo> <TerminalRRN>MASKED</TerminalRRN> <CardGroup>Visa</CardGroup> <ApprovalCode>MASKED</ApprovalCode> <CardIssuer>Foreign Visa</CardIssuer> <CardProduct>Foreign Visa</CardProduct> <CurrencyCode>NOK</CurrencyCode> <TerminalAppVersion>MASKED</TerminalAppVersion> <TerminalDateTime>2015-10-21 11:10:53</TerminalDateTime> <ExtraData/> </Transaction> </Transactions> </Batch> <Batch ID="MASKED"> <Host>NETS Norway</Host> <Name>MASKED</Name> <ClearingDateTime>2015-10-21 04:19:05</ClearingDateTime> <Transactions> <Transaction ID="MASKED"> <ClearingResponseCode>00</ClearingResponseCode> <TerminalID>MASKED</TerminalID> <TerminalModel>Ingenico IWL250 GPRS</TerminalModel> <TransactionType>Sale</TransactionType> <SettleTransactionID>MASKED</SettleTransactionID> <Online>1</Online> <TransactionDateTime>2015-10-20 13:07:21</TransactionDateTime> <Amount>100</Amount> <ReceiptNo>000028</ReceiptNo> <TerminalRRN>MASKED</TerminalRRN> <CardGroup>BankAxept</CardGroup> <ApprovalCode>MASKED</ApprovalCode> <CardIssuer>BBS</CardIssuer> <CardProduct>BAX Smartkort</CardProduct> <CurrencyCode>NOK</CurrencyCode> <TerminalAppVersion>MASKED</TerminalAppVersion> <TerminalDateTime>2015-10-20 13:07:09</TerminalDateTime> <ExtraData/> </Transaction> </Transactions> </Batch></Batches>With this a want to create a table with a filter on top, but only with the fields:ClearingDateTime, Transaction ID="MASKED",ClearingResponseCode, TerminalID, TerminalModel, TransactionType, Amount, CardGroup, CardProduct and CurrencyCode.Might somebody point me in the right direction? 解决方案 I pieced something similar together to get data from Zillow's api where URL request returns some XML. Here are some pieces of that code that should get you going. This cuts out quite a bit, but I think it gets you all the pertinent parts. You can see the format of Zillow's data at this site: http://www.zillow.com/howto/api/GetDeepComps.htm. Hope this helps! Sub ZillowDeepCompVf() ' Miscrosoft XML v6.0 must be enabled from the VBA editor ' To enable, go to Tools>References and check the box next to "Miscrosoft XML v6.0" 'Set URL URL = "http://www.zillow.com/webservice/GetDeepComps.htm?zws-id=" & ZWSID & "&zpid=" & rowZpid & "&count=" & Count & "&rentzestimate=true" 'Open XML page Set xmldoc = New MSXML2.DOMDocument60 xmldoc.async = False ' Check XML document is loaded If xmldoc.Load(URL) Then Set xmlMessage = xmldoc.SelectSingleNode("//message/text") Set xmlMessageCode = xmldoc.SelectSingleNode("//message/code") ' Check for an error message If xmlMessageCode.Text <> 0 Then ' Return error message DC.Range(ErrorMessage & i + headers) = xmlMessage.Text Else ' Get XML data from Zillow Set xmlComparables = xmldoc.SelectSingleNode("//response/properties/comparables") Set xmlCompList = xmlComparables.SelectNodes("comp") Set xmlPrincipal = xmldoc.SelectSingleNode("//response/properties/principal") ' Loop through comparables i = 1 headers = headers + 1 For i = 1 To xmlCompList.Length 'Home Details Set xmlComp = xmlCompList(i - 1) Set xmlHomeDetails = xmlComp.SelectSingleNode("links/homedetails") If xmlHomeDetails Is Nothing Then DC.Range(HomeDetails & i + headers) = "No home details available" Else DC.Range(HomeDetails & i + headers).Formula = "=HYPERLINK(""" & xmlHomeDetails.Text & """,""Zillow Details"")" End If ' Retrieve Street address, ZIP code ,City, zPID Set xmlStreetAddress = xmlComp.SelectSingleNode("address/street") Set xmlZipCode = xmlComp.SelectSingleNode("address/zipcode") Set xmlCity = xmlComp.SelectSingleNode("address/city") Set xmlState = xmlComp.SelectSingleNode("address/state") Set xmlZPID = xmlComp.SelectSingleNode("zpid") ' Push data to spreadsheet If xmlStreetAddress Is Nothing Then DC.Range(Address & i + headers) = "N/A" Else DC.Range(Address & i + headers) = xmlStreetAddress.Text End If If xmlCity Is Nothing Then DC.Range(City & i + headers) = "N/A" Else DC.Range(City & i + headers) = xmlCity.Text End If If xmlState Is Nothing Then DC.Range(State & i + headers) = "N/A" Else DC.Range(State & i + headers) = xmlState.Text End If If xmlZipCode Is Nothing Then DC.Range(ZipCode & i + headers) = "N/A" Else DC.Range(ZipCode & i + headers) = xmlZipCode.Text End If If xmlZPID Is Nothing Then DC.Range(pzpid & i + headers) = "N/A" Else DC.Range(pzpid & i + headers) = xmlZPID.Text End If 'Retrieve & push LastSold Date Set xmllastSoldDate = xmlComp.SelectSingleNode("lastSoldDate") If xmllastSoldDate Is Nothing Then DC.Range(LastSold & i + headers) = "NA" Else DC.Range(LastSold & i + headers) = xmllastSoldDate.Text End If... Next i 'end of iterate through the comps 'Sort the data Set Data = DC.Range(Cells(headers + 1, 1), Cells(i + headers, LastColumn)) Data.Sort Key1:=DC.Range("H" & i + headers), order1:=xlDescending 这篇关于将URL中的XML作为表格导入到Excel中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！