I'm doing a program in C # (Visual Studio 2015) from which I have to update an excel that has a series of formulas, etc.
I have decided to use Interop instead of other libraries like open xml or epplus because I need to update certain fields and then read the values of cells that contain formulas that reference the modified cells. Using openxml I do not get the updated values. In addition the excel has VBA code that is executed in the change of a cell, which I suppose does not run if I use openxml.
Using Interop does allow me to update a field and read the updated value of the formula fields but I have the following problem: The excel has defined about 1200 names to identify the fields and when trying to access from c # some of the names recognizes them but others do not. The name exists in the list of names but when trying to access it gives an error.
I use the following code:
excelApplication = new Microsoft.Office.Interop.Excel.Application();
excelApplication.ScreenUpdating = true;
excelApplication.Visible = true;
excelApplication.DisplayAlerts = false;
excelWorkbook = excelApplication.Workbooks.Open(txtFicheroEntrada.Text);
wsDatos = excelWorkbook.Worksheets[1];
wsDatos.Select();
foreach(Microsoft.Office.Interop.Excel.Name v in excelWorkbook.Names)
{
string NombreVar = v.Name;
//aqui encuentra el nombre definido
if (NombreVar == "BobinadoAT" ){ Console.WriteLine(NombreVar); }
}
if (wsDatos.Range["BobinadoAT"] != null) //aqui casca al intentar acceder
{
string valorcelda = wsDatos.Range["BobinadoAT"].Value.ToString();
}
It's funny because some of the names do recognize them. From the Excel if I select the name in the list does not take me to the corresponding cell.
Has anyone had problems with the names?