Update Excel file connections to CRM

In scope of our CRM Dynamics 2013 On premise to CRM Online migration, we had users who created dynamic Excel workbook based on CRM views.

When switching environment, URL changed (to become xxxx.crm4.dynamics.com) which prevented existing workbooks to work.

We found out no other way than edit directly in the file content the connection.

Starting with Office 2007 (docx/xlsx/...), documents started to be a zip container. For Excel file, looking into xl/connection.xml list all connections associated.

<connections xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">  
    <connection id="1" name="Connection" type="4" refreshedVersion="6" background="1" refreshOnLoad="1">
        <webPr sourceData="1" parsePre="1" consecutive="1" xl2000="1" url="https://crm.xxxxxx.com/_grid/print/print_data.aspx?tweener=1" post="XML_definition" htmlFormat="all"/>
    </connection>
</connections>  

updating, saving, re-compressing (be careful, it's easy to corrupt the zip file) is possible ... for an IT background.

Rather than traumatised our users, we ends up by automating this a little bit with a simple winform application based on Ionic.Zip nuget package.

string filename = "inputfile.xlsl";  
string connection_ref_filename = "xl/connections.xml";  
string old_crm = "https://oldcrm.com";  
string new_crm = "https://newcrm.com";

using (ZipFile zip = new ZipFile(filename))  
{
    var connections_file = zip.Entries.FirstOrDefault(x => x.FileName == connection_ref_filename);
    if (connections_file != null)
    {
        MemoryStream connections_content = new MemoryStream();
        connections_file.Extract(connections_content);

        connections_content.Position = 0;
        string content = new StreamReader(connections_content).ReadToEnd(); ;
        content = content.Replace(old_crm, new_crm);

        zip.UpdateEntry(connection_ref_filename, content);

        string fixed_excel_file = filename.Replace(".xlsx", "-fixed.xlsx");

        zip.Save(fixed_excel_file);
        MessageBox.Show("Excel file connection to CRM has been upaded. Fixed Excel file has been saved with '-fixed' in the filename", "Fixed Excel", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
        Process.Start(fixed_excel_file);
    }
}