title: Change fileshare root in Excel dynamically by modifying the xml behind the Excel with Python
body: <p>We are at a point of a fileshare migration but a lot of excel files on the current share reference each other including roots (in formulas).
  I figured it's possible to change the formula references of the current root to the new root by modifying the underlying xml.</p>
  <p>I did modify one tag value of the XML, hoping that the structure of the XML would stay the same. However unfortunately the structure doesn't stay the same.</p>
  <p>Tag: <code>&lt;x15ac:absPath xmlns:x15ac=&quot;http://schemas.microsoft.com/office/spreadsheetml/2010/11/ac&quot; url=&quot;Q:\General\Results\2021\&quot;</code></p>
  <p>should be modified to <code>&lt;x15ac:absPath xmlns:x15ac=&quot;http://schemas.microsoft.com/office/spreadsheetml/2010/11/ac&quot; url=&quot;I:\Results\2021\&quot;</code></p>
  <p>As you can see the structure and tags changes and I can't come up with the reason for that.
  Appreciate your help on solving this. Please let me know if I need to clarify.</p>
  <p>My python code:</p>
  <pre><code>import xml.etree.ElementTree as ET
  
  # Change workbook.xml
  tree = ET.parse('workbook.xml')
  root = tree.getroot()
  for elem in root.iter():
      if str(elem.get('url'))[:11] == 'Q:\General':
          elem.set('url', elem.get('url').replace(r'Q:\General', 'I:\Results'))
  tree.write('workbook.xml', encoding='UTF-8', method='xml',  xml_declaration=True)
  </code></pre>
  <p>XML of initial excel file (workbook.xml):</p>
  <pre><code>&lt;?xml version=&quot;1.0&quot; encoding=&quot;UTF-8&quot; standalone=&quot;yes&quot;?&gt;
  &lt;workbook
      xmlns=&quot;http://schemas.openxmlformats.org/spreadsheetml/2006/main&quot;
      xmlns:r=&quot;http://schemas.openxmlformats.org/officeDocument/2006/relationships&quot;
      xmlns:mc=&quot;http://schemas.openxmlformats.org/markup-compatibility/2006&quot; mc:Ignorable=&quot;x15 xr xr6 xr10 xr2&quot;
      xmlns:x15=&quot;http://schemas.microsoft.com/office/spreadsheetml/2010/11/main&quot;
      xmlns:xr=&quot;http://schemas.microsoft.com/office/spreadsheetml/2014/revision&quot;
      xmlns:xr6=&quot;http://schemas.microsoft.com/office/spreadsheetml/2016/revision6&quot;
      xmlns:xr10=&quot;http://schemas.microsoft.com/office/spreadsheetml/2016/revision10&quot;
      xmlns:xr2=&quot;http://schemas.microsoft.com/office/spreadsheetml/2015/revision2&quot;&gt;
      &lt;fileVersion appName=&quot;xl&quot; lastEdited=&quot;7&quot; lowestEdited=&quot;7&quot; rupBuild=&quot;23426&quot;/&gt;
      &lt;workbookPr defaultThemeVersion=&quot;166925&quot;/&gt;
      &lt;mc:AlternateContent
          xmlns:mc=&quot;http://schemas.openxmlformats.org/markup-compatibility/2006&quot;&gt;
          &lt;mc:Choice Requires=&quot;x15&quot;&gt;
              &lt;x15ac:absPath url=&quot;Q:\General\Results\2021\&quot;
                  xmlns:x15ac=&quot;http://schemas.microsoft.com/office/spreadsheetml/2010/11/ac&quot;/&gt;
              &lt;/mc:Choice&gt;
          &lt;/mc:AlternateContent&gt;
          &lt;xr:revisionPtr revIDLastSave=&quot;0&quot; documentId=&quot;13_ncr:1_{77E8B687-2130-4B59-8784-F53BD3BD2847}&quot; xr6:coauthVersionLast=&quot;45&quot; xr6:coauthVersionMax=&quot;45&quot; xr10:uidLastSave=&quot;{00000000-0000-0000-0000-000000000000}&quot;/&gt;
          &lt;bookViews&gt;
              &lt;workbookView xWindow=&quot;28680&quot; yWindow=&quot;-120&quot; windowWidth=&quot;29040&quot; windowHeight=&quot;15990&quot; xr2:uid=&quot;{00000000-000D-0000-FFFF-FFFF00000000}&quot;/&gt;
          &lt;/bookViews&gt;
          &lt;sheets&gt;
              &lt;sheet name=&quot;Blad1&quot; sheetId=&quot;1&quot; r:id=&quot;rId1&quot;/&gt;
          &lt;/sheets&gt;
          &lt;calcPr calcId=&quot;191029&quot;/&gt;
          &lt;extLst&gt;
              &lt;ext uri=&quot;{140A7094-0E35-4892-8432-C4D2E57EDEB5}&quot;
                  xmlns:x15=&quot;http://schemas.microsoft.com/office/spreadsheetml/2010/11/main&quot;&gt;
                  &lt;x15:workbookPr chartTrackingRefBase=&quot;1&quot;/&gt;
              &lt;/ext&gt;
              &lt;ext uri=&quot;{B58B0392-4F1F-4190-BB64-5DF3571DCE5F}&quot;
                  xmlns:xcalcf=&quot;http://schemas.microsoft.com/office/spreadsheetml/2018/calcfeatures&quot;&gt;
                  &lt;xcalcf:calcFeatures&gt;
                      &lt;xcalcf:feature name=&quot;microsoft.com:RD&quot;/&gt;
                      &lt;xcalcf:feature name=&quot;microsoft.com:Single&quot;/&gt;
                      &lt;xcalcf:feature name=&quot;microsoft.com:FV&quot;/&gt;
                      &lt;xcalcf:feature name=&quot;microsoft.com:CNMTM&quot;/&gt;
                      &lt;xcalcf:feature name=&quot;microsoft.com:LET_WF&quot;/&gt;
                  &lt;/xcalcf:calcFeatures&gt;
              &lt;/ext&gt;
          &lt;/extLst&gt;
      &lt;/workbook&gt;
  </code></pre>
  <p>Output XML after python code:</p>
  <pre><code>&lt;?xml version='1.0' encoding='UTF-8'?&gt;
  &lt;ns0:workbook
      xmlns:ns0=&quot;http://schemas.openxmlformats.org/spreadsheetml/2006/main&quot;
      xmlns:ns1=&quot;http://schemas.openxmlformats.org/markup-compatibility/2006&quot;
      xmlns:ns2=&quot;http://schemas.microsoft.com/office/spreadsheetml/2010/11/ac&quot;
      xmlns:ns3=&quot;http://schemas.microsoft.com/office/spreadsheetml/2014/revision&quot;
      xmlns:ns4=&quot;http://schemas.microsoft.com/office/spreadsheetml/2016/revision6&quot;
      xmlns:ns5=&quot;http://schemas.microsoft.com/office/spreadsheetml/2016/revision10&quot;
      xmlns:ns6=&quot;http://schemas.microsoft.com/office/spreadsheetml/2015/revision2&quot;
      xmlns:ns7=&quot;http://schemas.openxmlformats.org/officeDocument/2006/relationships&quot;
      xmlns:ns8=&quot;http://schemas.microsoft.com/office/spreadsheetml/2010/11/main&quot;
      xmlns:ns9=&quot;http://schemas.microsoft.com/office/spreadsheetml/2018/calcfeatures&quot; ns1:Ignorable=&quot;x15 xr xr6 xr10 xr2&quot;&gt;
      &lt;ns0:fileVersion appName=&quot;xl&quot; lastEdited=&quot;7&quot; lowestEdited=&quot;7&quot; rupBuild=&quot;23426&quot; /&gt;
      &lt;ns0:workbookPr defaultThemeVersion=&quot;166925&quot; /&gt;
      &lt;ns1:AlternateContent&gt;
          &lt;ns1:Choice Requires=&quot;x15&quot;&gt;
              &lt;ns2:absPath url=&quot;I:\Results\2021\&quot; /&gt;
          &lt;/ns1:Choice&gt;
      &lt;/ns1:AlternateContent&gt;
      &lt;ns3:revisionPtr revIDLastSave=&quot;0&quot; documentId=&quot;13_ncr:1_{77E8B687-2130-4B59-8784-F53BD3BD2847}&quot; ns4:coauthVersionLast=&quot;45&quot; ns4:coauthVersionMax=&quot;45&quot; ns5:uidLastSave=&quot;{00000000-0000-0000-0000-000000000000}&quot; /&gt;
      &lt;ns0:bookViews&gt;
          &lt;ns0:workbookView xWindow=&quot;28680&quot; yWindow=&quot;-120&quot; windowWidth=&quot;29040&quot; windowHeight=&quot;15990&quot; ns6:uid=&quot;{00000000-000D-0000-FFFF-FFFF00000000}&quot; /&gt;
      &lt;/ns0:bookViews&gt;
      &lt;ns0:sheets&gt;
          &lt;ns0:sheet name=&quot;Blad1&quot; sheetId=&quot;1&quot; ns7:id=&quot;rId1&quot; /&gt;
      &lt;/ns0:sheets&gt;
      &lt;ns0:calcPr calcId=&quot;191029&quot; /&gt;
      &lt;ns0:extLst&gt;
          &lt;ns0:ext uri=&quot;{140A7094-0E35-4892-8432-C4D2E57EDEB5}&quot;&gt;
              &lt;ns8:workbookPr chartTrackingRefBase=&quot;1&quot; /&gt;
          &lt;/ns0:ext&gt;
          &lt;ns0:ext uri=&quot;{B58B0392-4F1F-4190-BB64-5DF3571DCE5F}&quot;&gt;
              &lt;ns9:calcFeatures&gt;
                  &lt;ns9:feature name=&quot;microsoft.com:RD&quot; /&gt;
                  &lt;ns9:feature name=&quot;microsoft.com:Single&quot; /&gt;
                  &lt;ns9:feature name=&quot;microsoft.com:FV&quot; /&gt;
                  &lt;ns9:feature name=&quot;microsoft.com:CNMTM&quot; /&gt;
                  &lt;ns9:feature name=&quot;microsoft.com:LET_WF&quot; /&gt;
              &lt;/ns9:calcFeatures&gt;
          &lt;/ns0:ext&gt;
      &lt;/ns0:extLst&gt;
  &lt;/ns0:workbook&gt;
  </code></pre>