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

更新时间：2022-11-03 08:01:13

我拼凑了一些类似的东西，以便从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

上一篇 : ：FTP VS数据流下一篇 : MVC3提交返回我的复杂数据类型空

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

相关阅读

技术问答最新文章