I have a C# automation code which pulls some data from Microsoft Excel. When I try to run this code in a virtual machine (windows server 2012 r2) I am getting the below error
'System.Runtime.InteropServices.COMException (0x80040154): Retrieving the COM class factory for component with CLSID {00024500-0000-0000-C000-000000000046} failed due to the following error: 80040154 Class not registered (Exception from HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG)).'
I found out that Microsoft Access is not installed and tried to install the same (Microsoft Access 2013 Runtime), installation is successful. But weirdly it is not installed completely. In the C:\Program Files (x86)\Microsoft Office\Office15
I can see only a few DLL's (Microsoft.Office.Interop.Access dll is missing in my project references) compared to the DLL's in my local system. Also there is no entry in registry with the key 00024500-0000-0000-C000-000000000046
Code to get data from excel
public static Dictionary<string, string> GetExcelData(string testDatapath, string testDataSheet, string testcaseID)
{
testCaseValues.Clear();
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
Workbook book = null;
Range range = null;
bool arrayFlag = false;
int iVal = 0;
ArrayList excelValues = new ArrayList();
ArrayList header = new ArrayList();
try
{
app.Visible = false;
app.ScreenUpdating = false;
app.DisplayAlerts = false;
book = app.Workbooks.Open(testDatapath);
Console.WriteLine("fetching the excel from " + testDatapath);
foreach (Worksheet sheet in book.Worksheets)
{
if (sheet.Name.Equals(testDataSheet))
{
Console.WriteLine("Test data sheet is " + testDataSheet);
// get a range to work with
range = sheet.get_Range("A1", Missing.Value);
// get the end of values to the right (will stop at the first empty cell)
range = range.get_End(XlDirection.xlToRight);
// get the end of values toward the bottom, looking in the last column (will stop at first empty cell)
range = range.get_End(XlDirection.xlDown);
// get the address of the bottom, right cell
string downAddress = range.get_Address(false, false, XlReferenceStyle.xlA1, Type.Missing, Type.Missing);
// Get the range, then values from a1
range = sheet.get_Range("A1", downAddress);
object[,] values = (object[,])range.Value2;
//To fetech the header values
for (int headCount = 1; headCount <= values.GetLength(1); headCount++)
{
try
{
header.Add((string)values.GetValue(1, headCount));
arrayFlag = true;
}
catch (Exception ApplicationException)
{
Console.WriteLine(ApplicationException.Message);
arrayFlag = false;
break;
}
}
//To fetch test data
if (arrayFlag == true)
{
for (int i = 1; i <= values.GetLength(0); i++)
{
for (int j = 1; j <= values.GetLength(1); j++)
{
string actualTestCaseID = (string)values.GetValue(i, 1);
// Console.WriteLine("Actual Test case is " + actualTestCaseID);
if (actualTestCaseID.Equals(testcaseID))
{
Console.WriteLine("Inside if");
iVal = i;
arrayFlag = true;
break;
}
}
}
}
if (arrayFlag == true)
{
//Add Excel Values
for (int j = 1; j <= values.GetLength(1); j++)
{
excelValues.Add((string)values.GetValue(iVal, j));
}
//Create HashmapTable
int colCount = sheet.UsedRange.Columns.Count;
for (int hashVal = 0; hashVal <= colCount - 1; hashVal++)
{
if (excelValues[hashVal] != null)
{
testCaseValues.Add(header[hashVal].ToString(), excelValues[hashVal].ToString());
}
else
{
testCaseValues.Add(header[hashVal].ToString(), "");
}
}
}
break;
}
}
}
catch (Exception e)
{
throw e;
}
finally
{
range = null;
if (book != null)
book.Close(false, Missing.Value, Missing.Value);
book = null;
if (app != null)
app.Quit();
app = null;
}
return testCaseValues;
}
I expect excel to be installed successfully after I install 'Access runtime' software but since it is installed partially i am getting the same error as described above.
Installing the MS Access runtime does not install Microsoft Excel. Only installing Microsoft Office will install Excel. This means buying the package so that it is fully licensed. There is no free version.
Microsoft Access runtime is designed to allow users without Microsoft Access to run databases created using the full version of that application. The runtime will not install all dlls that the full version brings with it.
In any case, it's a bad idea to install Micrososft Office products server-side as they're end-user tools. This means that under certain circumstances they can "stall" (appear to crash) because they're waiting for user input. You should consult https://support.microsoft.com/en-za/help/257757/considerations-for-server-side-automation-of-office.
There are alternative approaches to using "the interop", but not knowing what you're trying to do it's not possible to make any suggestions. That would be a topic for another question...
User contributions licensed under CC BY-SA 3.0