I'm using Visual Studio 2022. I have created a .net 6 project. I added the COM references: Microsoft Excel 16 Object Library (Microsoft.Office.Interop.Excel) and Microsoft Office 16 Object Library (Microsoft.Office.Core).
The following lines of Excel interop code threw an error at SetElement()
, could not build:
Chart c = (Chart)wbSheets.Application.Workbooks[1].Charts.Add();
chart.SetElement(Microsoft.Office.Core.MsoChartElementType.msoElementChartTitleAboveChart);
Microsoft.Office.Core... is defined in an assembly that is not referenced. You must add a reference to assembly office, Version=15.0.0.0, ..., PublicKeyToken=71e9bce111e9429c
A very strange error, that an Office 16 (Office 2016) libary needs references to Office 15 (Office 2013).
The exact same steps for COM referencing and c# code works perfectly in .NET 4.8 Framework. So the problem is specific to .NET 6 (possibly .NET 5 and all previous .NET Core versions).
Using a nuget command Install-Package MicrosoftOfficeCore -Version 15.0.0
to install the reference and manually removing the previous v16 reference, did resolve the code building error. But now the dependency had a yellow triangle warning, that the dependency is for .NET 4.6-4.8 Framework and might be incompatible with .NET 6.
Despite this, the code seemed to work and the Excel file was generated.
But I'll post a better fix in the answer.
The better fix is to go to:
Solution Explorer > project > Dependencies > COM > select Microsoft.Office.Core > properties pane.
The last property is Wrapper Tool, with a default value of tlbimp
Type in primary
for the value, click somewhere else to update the field and the problem is solved. No nuget package required.
Found this obscure solution from here (bottom most post): https://github.com/dotnet/project-system/issues/5735