Excel VBA产生奇怪的XML映射运行时错误 [英] Strange XML map run-time error with Excel VBA

问题描述

Excel VBA中的XML映射遇到麻烦，一旦将值输入到特定的单元格中，该XML映射会自动查询API并检索其数据。该映射可以正常工作，刷新XML映射并将查询数据检索到另一个所需的单元格中，但是尽管成功，它仍然会在VBA中强制执行运行时错误。

Having some trouble with an XML map in Excel VBA that automatically queries an API and retrieves its data once a value is entered into a specific cell. The map works correctly, refreshing the XML map and retrieving the query data into another desired cell, but despite this success it still forces a Run-time Error within VBA.

给出的运行时错误为'-2147217376（80041020）'

The run-time error given is '-2147217376 (80041020)'

'指定资源的下载失败。'

'The download of the specified resource has failed.'

这是我在VBA中的代码。我已经更改了网站的示例，但是查询的格式是相同的。

Here's my code in VBA. I've changed the example of the website but the format for the query is the same.

If Target.Column = 6 And (Target.Row >= 1 And Target.Row <= 10000) Then
Dim CellVariable As String: CellVariable = Target.Value

End If

Dim Map As XmlMap
Set Map = ActiveWorkbook.XmlMaps(1)
Map.DataBinding.LoadSettings "http://example.website.com/api/question?name=" & CellVariable & "&api_key=xxxxxxxxx&format=xml"
On Error Resume Next
Map.DataBinding.Refresh
On Error Resume Next

是否有某种方式可以覆盖此错误？ 关于错误继续下一步似乎没有解决办法。

Is there a way to override this error somehow? On Error Resume Next does not seem to be doing the trick. Any suggestions are appreciated!

推荐答案

尝试更改

Map.DataBinding.LoadSettings "http://example.website.com/api/question?name=" & CellVariable & "&api_key=xxxxxxxxx&format=xml"

为此（添加括号）：

Map.DataBinding.LoadSettings("http://example.website.com/api/question?name=" & CellVariable & "&api_key=xxxxxxxxx&format=xml")

或者对此（不同方法）：

Or to this (different method):

urlstr = "http://example.website.com/api/question?name=" & CellVariable & "&api_key=xxxxxxxxx&format=xml"
ThisWorkbook.XmlMaps(Map).Import urlStr

这篇关于Excel VBA产生奇怪的XML映射运行时错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！