来自 VBA 的 API 调用 [英] API call from VBA

问题描述

我对 API 了解不多，实际上几乎一无所知.我使用 Google POSTMAN 发出 POST 请求以从 Adaptive Insights 中提取一些数据.我想要做的是从 Excel VBA 执行调用，并在需要时通过按一个按钮来更新 Excel 工作表.

I don't know much about API, in fact, almost nothing. I used Google POSTMAN to make a POST request to extract some data from Adaptive Insights. What I would like to do is execute the call from Excel VBA and update an Excel sheet when I need by pressing a button.

以下是来自 POSTMAN 的片段:

Here is the snippet from POSTMAN:

     POST /api/v13 HTTP/1.1
     Host: api.adaptiveinsights.com
     Authorization: Basic ZmlubWdyX3NhbGVzQHN0ZW1jZWxsLmNvbTpBZGFwdGl2ZTE=
     Content-Type: application/xml
     cache -Control: no -cache
     Postman-Token: 650bd3ad-82e9-aa33-ae77-ee4d89ff0fd8

     <?xml version='1.0' encoding='UTF-8'?>
     <call method="exportData" callerName="Export.xlsx">
     <credentials login="******@****.com" password="*****"/>
     <version name="FY2017-November V2" isDefault="false"/>
     <format useInternalCodes="true" includeUnmappedItems="false"/>
     <filters>
     <timeSpan start="Jan-2017" end="Feb-2017"/></filters>
      <rules includeZeroRows="false" includeRollups="true" markInvalidValues="false" markBlanks="false"
     timeRollups="single">
     </rules>
     </call>

这是POSTMAN的输出

Here is the output from POSTMAN

     <?xml version="1.0" encoding="UTF-8"?>
     <response success="true">
     <output><![CDATA[Account Name,Account Code,Level Name,Rollup
      .......................................................

我做了一些谷歌搜索，这是我到目前为止的宏:

I did a fair bit of googling and this is what I have so far for my macro:

      myURL = "https://api.adaptiveinsights.com/api/v13"
      xmlHTTP.Open "POST", myURL, False
      xmlHTTP.SetCredentials "****@****.com", "*****",    HTTPREQUEST_SETCREDENTIALS_FOR_SERVER
      xmlHTTP.SetRequestHeader "Content-Type", "application/xml"
      xmlHTTP.Send

      Set xmlResult = New MSXML2.DOMDocument

      xmlResult.LoadXML xmlHTTP.ResponseText

我的问题是:

1. 我如何连接到这一特定数据集 - FY2017-November V2"，其 timeSpan start ="Jan-2017" end="Feb-2017".

1. How do I connect to this specific set of data - "FY2017-November V2" that has timeSpan start ="Jan-2017" end="Feb-2017".

如何将数据加载到 Excel 中，实质上是在 VBA 中做我在 POSTMAN 中所做的?

How do I load the data into Excel, in essence, do in VBA what I did in POSTMAN?

数据如下:

有四个字段 - 3 个文本和一个数字(帐户名称、帐户代码、级别名称、汇总):

There are four fields - 3 text and one numeric (Account Name,Account Code,Level Name,Rollup):

6020 员工福利"，6020，会计公司"，20.3252

"6020 Staff Benefit", 6020, "Accounting-Corp", 20.3252

非常感谢任何帮助.

谢谢

瓦尔格拉德

推荐答案

我认为您的身份验证在这里是错误的.将所有 XML 放入一个变量中，包括身份验证凭据.对于 Adaptive Insights，这只是 XML 文本的一部分.

I think your auth is wrong here. Put all of the XML into a single variable, including the authentication credentials. For Adaptive Insights this is just part of the XML text.

request = "<?xml version=  ... </call>"
URL = "https://api.adaptiveinsights.com/api/v13"
Set objXML = CreateObject("MSXML2.ServerXMLHTTP")
objXML.Open "POST", URL, False
objXML.setRequestHeader "Content-Type", "text/xml"
objXML.send (request)
xmlResponse = objXML.responsetext
MsgBox (xmlResponse)

应该在消息框中看到响应的第一部分.您仍然需要解析 CDATA 块，但这只是文本操作.

Should see the first part of the response in the message box. You've still got a job of work to parse out the CDATA block but that's just text manipulation.

这篇关于来自 VBA 的 API 调用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！