Janis S. Janis S. - 3 months ago 29
C# Question

Why setting OLEDBConnection.Connection throws Exception (HRESULT: 0x800A03EC)?

I am working with Excel Interop namespace to modify connection string in the Excel Workbook connections.

enter image description here

Why when I am trying to set the connection property (MSDN OLEDBConnection.Connection) throws an error on the line of the assignment?


Exception from HRESULT: 0x800A03EC


application = new Application();
Workbook wb = application.Workbooks.Open(file.FullName);
Sheets wbs = wb.Worksheets;
IEnumerable<Workbook> workbooks = application.Workbooks.Cast<Workbook>();

foreach (var connection in wb.Connections.Cast<WorkbookConnection>()
.Where(c => c.Type == XlConnectionType.xlConnectionTypeOLEDB))
{
connection.OLEDBConnection.Connection = "Test Connection String";
}

application.Quit();


However, calling Replace method as shown below is working. I have found this as workaround, not being sure why Replace works in this case.

application = new Application();
Workbook wb = application.Workbooks.Open(file.FullName);
Sheets wbs = wb.Worksheets;
IEnumerable<Workbook> workbooks = application.Workbooks.Cast<Workbook>();

foreach (var connection in wb.Connections.Cast<WorkbookConnection>()
.Where(c => c.Type == XlConnectionType.xlConnectionTypeOLEDB))
{
var conString = connection.OLEDBConnection.Connection.ToString();
connection.OLEDBConnection.Connection =
conString.Replace("Test Connection String", "New Test Connection String");
}

application.Quit();


This in fact is the only way I could get the connection string changed, therefore asking what is the reason behind why set could be throwing the error.

Answer

I found a way to use the OpenXML instead, seems that changing connection string via interop was not the only way.

using (SpreadsheetDocument excelDoc = SpreadsheetDocument.Open(file.FullName, true))
{
    WorkbookPart workbookpart = excelDoc.WorkbookPart;
    string spreadsheetmlNamespace = @"http://schemas.openxmlformats.org/spreadsheetml/2006/main";
    NameTable nt = new NameTable();
    XmlNamespaceManager nsManager = new XmlNamespaceManager(nt);
    nsManager.AddNamespace("sh", spreadsheetmlNamespace);
    XmlDocument xdoc = new XmlDocument(nt);
    xdoc.Load(workbookpart.ConnectionsPart.GetStream());
    XmlNode oxmlNode = xdoc.SelectSingleNode("/sh:connections/sh:connection/sh:dbPr/@connection", nsManager);
    oxmlNode.Value.Replace(oxmlNode.Value, ReplaceInitialCatalog(oxmlNode.Value, repConfig.DbName));
    xdoc.Save(workbookpart.ConnectionsPart.GetStream());
}
Comments