Testing Add-In methods with ExcelDNA Testing

[schebotar]

Hello.

All examples from Testing samples works well.
I have some internal add-in methods with Excel functionality such as worksheet validations or Ribbon buttons functions. With no [ExcelFunction] attribute. So they are not accessible by cell formulas.

Adding add-in project as reference causing an error:

Error CS0433 The type 'Workbook' exists in both 'Interop.Microsoft.Office.Interop.Excel, Version=1.9.0.0, Culture=neutral, PublicKeyToken=null' and 'Microsoft.Office.Interop.Excel, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c'

And this conflict i can't resolve by just

using Workbook = Interop.Microsoft.Office.Interop.Excel.Workbook

How to test methods like these?

[govert]

The error you show has to do with references or packages you use to get the COM interop assemblies.
You should be able to use the NuGet package "ExcelDna.Interop" v15.0.1 in both your add-in project and the test project, and then at least this error should go away.

[schebotar]

Added ExcelDna.Interop to Test Project with no effect

PS D:\Source\RhSolutions.Excel> dotnet list package
Project 'RhSolutions.AddIn' has the following package references
   [net472]:
   Top-level Package            Requested   Resolved
   > ExcelDna.AddIn             1.6.0       1.6.0
   > ExcelDna.Integration       1.6.0       1.6.0
   > ExcelDna.IntelliSense      1.6.0       1.6.0
   > ExcelDna.Interop           15.0.1      15.0.1
   > Microsoft.CSharp           4.7.0       4.7.0
   > Newtonsoft.Json            13.0.3      13.0.3
   > RhSolutions.Sku            0.1.1       0.1.1
   > System.Net.Http            4.3.4       4.3.4

   [net6.0-windows7.0]:
   Top-level Package            Requested   Resolved
   > ExcelDna.AddIn             1.6.0       1.6.0
   > ExcelDna.Integration       1.6.0       1.6.0
   > ExcelDna.IntelliSense      1.6.0       1.6.0
   > ExcelDna.Interop           15.0.1      15.0.1
   > Microsoft.CSharp           4.7.0       4.7.0
   > Newtonsoft.Json            13.0.3      13.0.3
   > RhSolutions.Sku            0.1.1       0.1.1
   > System.Net.Http            4.3.4       4.3.4

Project 'RhSolutions.Tests' has the following package references
   [net6.0-windows7.0]:
   Top-level Package       Requested   Resolved
   > ExcelDna.Interop      15.0.1      15.0.1
   > ExcelDna.Testing      1.6.0       1.6.0

Still get an error if I add AddIn project as reference to Test project

Error CS0433 The type 'Workbook' exists in both 'Interop.Microsoft.Office.Interop.Excel, Version=1.9.0.0, Culture=neutral, PublicKeyToken=null' and 'Microsoft.Office.Interop.Excel, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c'

[govert]

Do you perhaps have explicit COM references to the Excel object model in the project file?
You can remove these.

[schebotar]

I got it
ExcelDna.Testing.props:

	<ItemGroup Condition=" '$(TargetFramework)' == 'net6.0-windows' ">
		<COMReference Include="Microsoft.Office.Interop.Excel">
			<WrapperTool>tlbimp</WrapperTool>
			<VersionMinor>9</VersionMinor>
			<VersionMajor>1</VersionMajor>
			<Guid>00020813-0000-0000-c000-000000000046</Guid>
			<Lcid>0</Lcid>
			<Isolated>false</Isolated>
			<EmbedInteropTypes>true</EmbedInteropTypes>
		</COMReference>
		<COMReference Include="Microsoft.Office.Core">
			<WrapperTool>tlbimp</WrapperTool>
			<VersionMinor>8</VersionMinor>
			<VersionMajor>2</VersionMajor>
			<Guid>2df8d04c-5bfa-101b-bde5-00aa0044de52</Guid>
			<Lcid>0</Lcid>
			<Isolated>false</Isolated>
			<EmbedInteropTypes>true</EmbedInteropTypes>
		</COMReference>
	</ItemGroup>

COM References was added to Test project by ExcelDNA.Testing package because my target framework was 'net6.0-windows'
I had to change target to net472. And it worked.

[govert]

@Sergey-Vlasov Can we update the ExcelDna.Testing so that the net6.0-windows target also depends on ExcelDna.Interop, instead of the explicit COMReference?