将XML从URL导入Excel作为表

Question

我想使用VBA来自动化从外部URL到我的Excel工作簿的XML数据轮询过程。这是网址的格式(我掩盖了敏感信息)。

首先，我想要一个VBA脚本来请求daterange (从-到)，并将其放入URL中。

我使用浏览器打开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>

​

在这种情况下，希望创建一个顶部有过滤器的表，但只创建字段：

ClearingDateTime，Transaction ID=“掩码”，ClearingResponseCode，TerminalID，TerminalModel，TransactionType，Amount，CardGroup，CardProduct和CurrencyCode。

有人能给我指明正确的方向吗？

Answer 1

我把类似的东西拼凑在一起，从Zillow的api中获取数据，URL请求在那里返回一些XML。下面是几段代码，这些代码应该可以让您运行。这是相当多的削减，但我认为它得到了所有相关的部分。您可以在这个站点：http://www.zillow.com/howto/api/GetDeepComps.htm上看到Zillow的数据格式。希望这能有所帮助！

        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

Answer 2

实际上我用很少的脚本就把它整理好了。我从来没有用VBA编程过，所以我可能会做一些“愚蠢”的事情。

我创建了一个UserFrom，在这里我添加了一个昏昏欲睡的列表，我填充这些列表来选择日期。由此，我将日期格式构建为字符串。

然后，我构建了这样的查询：

ActiveWorkbook.XmlImport URL:="https://www.MASKED.com/export-data/xml/?merchantid=MASKED&hash=MASKED&type=clearing&fromdate=" & FraDato & "&todate=" & TilDato, ImportMap:=Nothing, Overwrite:=True, Destination:=Range("Data!A1")

它轻轻地将所有数据作为一个表填充到一个巨大的Excel电子表格中。

在那里，我只是使用VBA来删除不需要的行:)

谢谢你的协助:)