时间戳

首页 > 解决方案 > Requests from XMLDom to retrieve XML from API are blocked

xml - Requests from XMLDom to retrieve XML from API are blocked

问题描述

I've written a macro in VBA for Excel 2010 to retrieve data from an API that returns XML. I'm doing this voluntarily to help my wife and her colleagues from doing a lot of manual work. So, the macro is used in an enterprise setting, where security settings are out of my hands and having never worked in an enterprise setting, I also have no idea what the possibilities are.

However, the IT guys have stated they are willing to help, but have no idea how to fix it. So I wonder if anyone here can point in the right direction on how to get this to work on all PCs.

The code is as below, and it works on the home PCs of all my wife's colleagues and my own too. It also works on some work PCs, but on some not. The code fails on the objDoc.Load part, it shows my error message: 

MsgBox ("There was an error when connecting to the API)

I wonder if there are enterprise settings, or individual Excel settings that block access to external URLs in macros. I wouldn't be surprised, as it would be good security, but I guess there should also be a way to whitelist the url of the API?

Does anyone have an idea how this could be fixed? Either in the code, in Excel 2010 settings or any Office enterprise settings I'm not aware of?

Dim strUrl as String
Dim objDoc As Object
Set objDoc = CreateObject("MSXML2.DOMDocument")
objDoc.async = False

strUrl = {api URL}

    If objDoc.Load(strUrl) Then
        'it worked!
    Else
        MsgBox ("There was an error when connecting to the API)
        Exit Function
    End If

标签： xmlexcelexcel-2010vba

解决方案

缩小误差范围的方法

显然，您使用后期绑定的代码已执行，但会显示错误消息。XML 代码通常包含不可读的字符实体（例如 & 符号&）。Else您可以通过将代码中的完整块替换为以下语句来缩小错误字段：

  Dim xPE        As Object    ' Set xPE = CreateObject("MSXML2.IXMLDOMParseError")
  Dim strErrText As String
  Set xPE = objDoc.parseError
  With xPE
     strErrText = "Load error " & .ErrorCode & " xml file " & vbCrLf & _
      Replace(.URL, "file:///", "") & vbCrLf & vbCrLf & _
      xPE.reason & _
      "Source Text: " & .srcText & vbCrLf & vbCrLf & _
      "Line No.:    " & .Line & vbCrLf & _
      "Line Pos.: " & .linepos & vbCrLf & _
      "File Pos.:  " & .filepos & vbCrLf & vbCrLf
  End With
  MsgBox strErrText, vbExclamation
  Set xPE = Nothing
  Exit Function

附加提示

如果您将objDoc对象设置为内存，Set objDoc = CreateObject("MSXML2.DOMDocument")通常您将获得旧版本（3.0），因此在大多数情况下，最好显式使用Set objDoc = CreateObject("MSXML2.DOMDocument.6.0")以获得最新的 6.0 版本。

推荐阅读