我可以使用VBA中的变量更新XML节点值吗

问题描述：

我是学习XML的新手，但是可以在Excel 2010中使用VBA.

I am a complete newcomer to learning about XML but OK with VBA in Excel 2010.

在Excel VBA中，我创建了一个简单的 CustomXMLPart ，在单个根下具有5个节点，类似于以下示例:

In Excel VBA I have created a simple CustomXMLPart with 5 nodes under a single root, akin to the example below:

<
  <RefTest>
      <sRef1>SomeText</sRef1>    'text
      <sRef2>XYZ234</sRef2>      'text
      <sRef3>ABC123</sRef3>      'text
      <dRef4>25/02/1953</dRef4>  'date or text?
      <iRef5>0</iRef5>           'numeric or text?
  </RefTest>
>

这正常，我可以使用VBA读回值.

This works OK and I can read the values back-in using VBA.

我的问题是(目前)节点值是作为文字(文本和数字)输入的.

My problem is that the node values (at the moment) are entered as literals (text and digits).

我希望能够在Excel VBA中使用VBA变量的内容来更新这些节点值.

I want to be able to update these node values, from within Excel VBA, but using the contents of VBA variables.

例如，某个用户在 userform 文本框中输入一个值，在一个变量(例如 MyVar )中输入一个值，而我想用此变量的内容.一种使用MyVar更新节点iRef5".在网络上，几乎找不到关于更新XML值的参考，尤其是使用变量.

So, for example a user enters a value into a userform text box, into a variable (say MyVar), and I want to update the node value with the contents of this variable. A sort of "update node iRef5 with MyVar". I can find very little reference to Updating XML values like this, especially using variables, on the web.

可以在VBA内完成此操作吗?如果是这样，那是什么方法，XML如何处理变量，也许还需要一个确切语法的例子.

Can this be done from within VBA? If so, what is the approach, how does XML deal with variables, and perhaps an example of the exact syntax please.

非常感谢.

答

我不知道您的xml将具有什么结构.但是，如果仅是这五个节点，也许这样的事情可能会对您有所帮助:

I don't know what structure of your xml will have. But if it's only these five nodes, perhaps something like this might help you:

Sub XMLTest()
Dim myVar As String, pathToXML As String
Dim xmlDoc As Object, xmlRoot As Object
    Set xmlDoc = CreateObject("MSXML2.DOMDocument")
    pathToXML = "N:\example.xml" '<--- change the path
    Call xmlDoc.Load(pathToXML)
    Set xmlRoot = xmlDoc.getElementsByTagName("RefTest").Item(0)
    myVar = "foobar" '<--- your value
    xmlRoot.selectSingleNode("iRef5").Text = myVar
    Call xmlDoc.Save(pathToXML)
End Sub

如果这对您没有帮助，则应提供有关xml以及您实际要执行的操作的更多信息.

If this doesn't help you, you should give more information about your xml and what you actually want to do.

我可以使用VBA中的变量更新XML节点值吗

相关推荐