We were in need to modify about 100+ excel files which are connecting to data connections files sitting in data connection library of SharePoint 2010. The property we needed to change is “Always user connection file” in connection properties as shown below. Of course programmatically.
First of, I added the reference of “Microsoft.Office.Interop.Excel” in project and include it in code, like below
using xls = Microsoft.Office.Interop.Excel;
Then, read excel file from physical location and changed its connection properties using the code below
var application = new xls.Application();
try
{
var application = new xls.Application(); // Creating excel application object
try
{
string readLocation = @"D:\Documents\myfile_1.xlsx";
var workbook = application.Workbooks.Open(readLocation); // Open excel workbook
foreach (xls.WorkbookConnection con in workbook.Connections) // Iterating all connections
{
// Check for other properties, if you have to. For me, I am dead sure that all files wouldn’t be using connection files
con.OLEDBConnection.AlwaysUseConnectionFile = true;
}
workbook.Save(); // Saving back workbook
}
catch (Exception)
{
throw;
}
finally
{
// These clean up lines are important to run otherwise, you will end up having an orphan excel instance sitting idle.
application.Workbooks.Close();
application.Quit();
}