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"
'Run the Zip procedure Call ZipPackage
MsgBox "Find your updated file here:" & vbCrLf & "C:\UpdatedFile.xlsx" Set oXmlNode = Nothing Set oXmlDoc = Nothing
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.
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?