Edit the sharedStrings XML File to Implement Mass Updates to Text

Vertex42 The Excel Nexus

Professional Excel Templates

Get Instant Access

As mentioned before, the sharedStringsXML part holds all of the strings used in the Excel file. These strings are referenced via the shared index number by each sheet in the container to apply them to the correct cell. One nifty trick is to change a string in the sharedStrings file and watch that change take effect in your Excel file.

For example, in the SalesByPeriod.xlsx sample file, certain records are tagged with the market South America. Suppose you wanted to change all instances of South America to Latin America. All you would have to do is open the sharedStrings file, find South America, and change it to Latin America. After repackaging the Excel file, you will see that every instance of South America has been changed to Latin America. Although you could make this change manually, imagine doing this for dozens of files.

The good news is that you can automate this process using MSXML DOM and XPath. The procedure shown here demonstrates how:

Sub Change_SharedString_File() Dim oXmlDoc As DOMDocument Dim oXmlNode As IXMLDOMNode

'Run the Unzip procedure Call UnzipPackage

'Create an instance of the DOMDocument and load XML file Set oXmlDoc = New DOMDocument oXmlDoc.async = False oXmlDoc.Load ("C:\MyUnzipped\xl\sharedstrings.xml")

'Pass Xpath to find the text that needs to be changed

Set oXmlNode = oXmlDoc.SelectSingleNode("//t[text()='South America']")

'Make sure text exists

If oXmlNode Is Nothing Then

Exit Sub End If

'Change the text and save your changes oXmlNode.Text = "Latin America"

oXmlDoc.Save "C:\MyUnzipped\xl\sharedstrings.xml"

'Run the Zip procedure Call ZipPackage

'Ready message

MsgBox "Find your updated file here:" & vbCrLf & "C:\UpdatedFile.xlsx" Set oXmlNode = Nothing Set oXmlDoc = Nothing

End Sub

To understand what is going on here, evaluate this code in steps:

1. Run the UnzipPackage procedure you created previously.

2. Create an instance of the DOMDocument object and load the sharedstrings.xml file.

3. Use an XPath expression to find the t node that contains the text 'South America'.

4. Check to make sure the text exists; if it doesn't, exit the procedure.

5. Edit the text to "Latin America" and save the change back to the sharedstrings.xml file.

6. Repackage the Excel file using the ZipPackage procedure created previously.

7. Clean up and output a message.

After running the procedure, open the C:\UpdatedFile.xlsx file to see that all of the records that were tagged as South America are now tagged Latin America.

Was this article helpful?

0 0

Responses

Post a comment