Excel

MatrixFrame and Excel


For avid Excel users, efficient and seamless data exchange with other systems is of great importance. One of the most effective ways to achieve this is through the MXML format. In this article, you will discover how data exchange between Excel and MXML can automate your workflow.

Data Exchange:

Using MXML, Excel users can directly import data from Excel into MatrixFrame. This means you can avoid the complex and time-consuming task of manual data entry. For example, you can export tax cases, taxes, beam lengths, supports, and other structural properties of your project to MXML and easily import them into MatrixFrame.

To write values to an MXML file, follow these steps:

1. Create a new XML DOM document using the line:

   ```vba
   Set xmlDoc = CreateObject("MSXML2.DOMDocument.6.0")
   ```

2. Load the XML source file (XMLSourcePath) or the XML used file (XMLUsedPath) into the xmlDoc object using the `load` method:

   ```vba
   xmlDoc.load(XMLSourcePath)
   ```

3. Select specific nodes or elements in the XML document using the `SelectSingleNode` or `SelectNodes` methods. This allows you to navigate through the XML structure and target specific nodes:

   ```vba
   Set xmlNode = xmlDoc.SelectSingleNode("//someNode")
   Set xmlNodeList = xmlDoc.SelectNodes("//someNode")
   ```

4. Once you have selected the desired node, you can modify the value of attributes or elements by accessing the properties or methods of the XML node object. This allows you to assign new values to attributes or elements:

   ```vba
   xmlNode.attributeName = "newValue"
   xmlNode.text = "newValue"
   ```

5. Save the updated XML document to a file using the `Save` method:

   ```vba
   xmlDoc.Save(XMLUsedPath)
   ```

Here is an example of how you can write values to an MXML file:
Make sure you have added the correct references to your VBA project (Microsoft XML, v6.0)!.

```vba
Sub WriteValuesToMXML()

    Dim xmlDoc As Object
    Dim xmlNode As Object
    Dim XMLUsedPath As String
    Dim newValue As String
    
    ' Set the path to the XML used file
    XMLUsedPath = "C:\Path\To\Your\MXMLFile.xml"
    
    ' Create a new XML DOM document
    Set xmlDoc = CreateObject("MSXML2.DOMDocument.6.0")
    
    ' Load the XML used file
    xmlDoc.load(XMLUsedPath)
    
    ' Select the desired node in the XML document
    Set xmlNode = xmlDoc.SelectSingleNode("//someNode")
    
    ' Prompt the user to enter a new value
    newValue = InputBox("Enter a new value:")
    
    ' Assign the new value to the selected node
    xmlNode.Text = newValue
    
    ' Save the updated XML document
    xmlDoc.Save XMLUsedPath
    
    ' Notify the user that the values have been updated
    MsgBox "The values have been successfully updated in the MXML file.", vbInformation

End Sub
```

With this example, you can open an existing XML file, select a specific node, and assign a new value to that node. The updated XML document is then saved to the specified path.

Run this macro and follow the instructions in the input box to add a new value to the selected node in the XML file. Then, check the XML file to see if the value has been successfully updated.

Navigating MXML from Excel


To target values in an MXML file from VBA, we can use the MSXML2.DOMDocument

 object library and XPath expressions. Here are some examples of how you can target specific values in the MXML file from VBA:

1. To target and retrieve the name of the first tax case, you can use the following code:

```vba
Dim xmlDoc As Object
Dim nodeName As String

' Create a new XML DOM document
Set xmlDoc = CreateObject("MSXML2.DOMDocument.6.0")

' Load the MXML file
xmlDoc.Load "C:\Path\To\Your\MXMLFile.xml"

' Target the node and retrieve the name
nodeName = xmlDoc.SelectSingleNode("/mxml/cases/case[@id='1']").Attributes.getNamedItem("name").Text

' Display the name in a message box
MsgBox "The name of the first tax case is: " & nodeName
```

2. To target and retrieve the value of the second load in the first tax case, you can use the following code:

```vba
Dim xmlDoc As Object
Dim loadValue As String

' Create a new XML DOM document
Set xmlDoc = CreateObject("MSXML2.DOMDocument.6.0")

' Load the MXML file
xmlDoc.Load "C:\Path\To\Your\MXMLFile.xml"

' Target the node and retrieve the value
loadValue = xmlDoc.SelectSingleNode("/mxml/cases/case[@id='1']/loads/load[2]/@value").Text

' Display the value in a message box
MsgBox "The value of the second load in the first tax case is: " & loadValue
```

With these examples, you can target specific values in an MXML file from VBA and use them in your own code for further processing or display.

Modifying and Adding Values

To modify a value in an MXML file, you need to target the specific node where the value is located and assign the value to the appropriate attribute or element. Here are some steps to modify a value in an MXML file:

1. Navigate to the desired node in the MXML file using XPath notation. You can use the `SelectSingleNode` method to select the node. For example:

   ```vba
   Set xmlNode = xmlDoc.SelectSingleNode("//node[@attribute='value']")
   ```

   This selects the node with a specific attribute and value.

2. Assign a new value to the selected node. Depending on the type of node (attribute or element) and the desired value, you can modify the `Text` property or a specific attribute of the node. For example:

   ```vba
   xmlNode.Text = "new value" ' To modify the text value of an element
   
   xmlNode.Attributes.getNamedItem("attribute").Text = "new value" ' To modify an attribute value
   ```

3. Save the updated MXML file to a file. Use the `Save` method to save the XML document with the updated values. For example:

   ```vba
   xmlDoc.Save "C:\Path\To\Your\MXMLFile.mxml"
   ```

With these steps, you can successfully modify values in an MXML file from VBA.

Adding new values to an MXML file:
To add new values to an MXML file, you need to create a new node and add this node to the appropriate position in the hierarchy of the MXML file. Here are some steps to add new values to an MXML file:

1. Create a new node using the `CreateElement` method of the XML DOM document. For example:

   ```vba
   Set newElement = xmlDoc.CreateElement("newNode")
   ```

2. Assign values to the attributes or text values of the new node. Use the properties or methods of the new node object

 to set the desired values. For example:

   ```vba
   newElement.SetAttribute "attribute", "value" ' To assign an attribute value
   newElement.Text = "text value" ' To assign a text value to an element
   ```

3. Add the new node to the desired parent node. Use the `appendChild` method of the parent node to add the new node. For example:

   ```vba
   Set parentNode = xmlDoc.SelectSingleNode("//parentNode")
   parentNode.appendChild newElement
   ```

4. Save the updated MXML file to a file. Use the `Save` method to save the XML document with the added node. For example:

   ```vba
   xmlDoc.Save "C:\Path\To\Your\MXMLFile.xml"
   ```

With these steps, you can successfully add new values to an MXML file from VBA.

Remember that the structure of an MXML file may vary depending on the specific usage and specifications of the application. Make sure to identify the correct nodes and attributes and make changes according to the required structure of the MXML file.

Here is an example of how you can modify values and add new values to an MXML file from VBA:

```vba
Sub ModifyMXMLValues()
    Dim xmlDoc As Object
    Dim xmlNode As Object
    Dim XMLFilePath As String
    
    ' Set the path to the MXML file
    XMLFilePath = "C:\Path\To\Your\MXMLFile.mxml"
    
    ' Create a new XML DOM document
    Set xmlDoc = CreateObject("MSXML2.DOMDocument.6.0")
    
    ' Load the MXML file
    xmlDoc.Load XMLFilePath
    
    ' Modify an existing value in the MXML file
    Set xmlNode = xmlDoc.SelectSingleNode("//node[@attribute='value']")
    xmlNode.Text = "new value"
    
    ' Add a new node to the MXML file
    Dim newElement As Object
    Set newElement = xmlDoc.CreateElement("newNode")
    newElement.SetAttribute "attribute", "value"
    newElement.Text = "text value"
    
    Dim parentNode As Object
    Set parentNode = xmlDoc.SelectSingleNode("//parentNode")
    parentNode.appendChild newElement
    
    ' Save the updated MXML file
    xmlDoc.Save XMLFilePath
    
    ' Notify the user that the values have been modified and the new node has been added to the MXML file
    MsgBox "The values have been successfully modified and the new node has been added to the MXML file.", vbInformation
End Sub
```

Make sure to adjust the path to your own MXML file (XMLFilePath) in the code. Run this macro, and the values in the MXML file will be modified, and the new node will be added according to the specified instructions.