我可以使用VBA中的变量更新XML节点值吗 [英] Can I update an XML node value using a variable in VBA

问题描述

我是学习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节点值吗的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！