How to connect with Excel (via OLE)

[Intexso]

Hello,

In my (VO) application I made an OLE library with the automation server.

I read in this forum that there is no need to convert Excel ole classes. You can simply add a reference to the Excel to 'interop assemblies' and choose "embed interop types" for these references.

Where can In find these assemblies? Are they in the GAC? And which do I have to choose? Or do I have to install something form microsoft?

In my VO-app I added a prefix to that classes? Is it likely that I will have to change the names of the classes in x#?

Thanks for your answer!

Eric

[Intexso]

Ahh.

I think i see. You have create an Assembly with the COM-tab.

Next thing is that the Type Library Importer (Tlbimp.exe) can not be found.

(I am using XIDE). Where can the path for Tlbimp.exe be set?

Eric

[Chris]

Hi Eric,

Please go to Tools/Preferences, Folders page, the last option is "SDK Tools Dir". If this is empty, please set it to point to where the .Net SDK tools (including tlbimp.exe) are located. In my PC, this is at

C:Program Files (x86)Microsoft SDKsWindowsv10.0AbinNETFX 4.6 Tools

Should be similar in yours, might need small adjustments to the path.

Chris

[robert]

Eric,
There is no need to generate the typelib. There should be a typelib already on your machine. Look for "Microsoft Excel Library" or for Microsoft.Office.Interop.Excel.

Also look at https://www.xsharp.eu/help/example-4-ol ... ation.html. It describes the process.


Robert

[Intexso]

@Chris. I set the preference and this worked.

@Robert. I studied the example. There is a small difference. On the COM-tab on my computer there is no Typelib shown. So I have to push the button 'Create COM Interop Assemblies'. There I choose 'Microsoft Excel 16.0 Object Library'. For Namespace I fill in the text Excel. After that push 'Create Assemblies'. It seems to me the only way or is there another way finding and adding the reference to this typelib?

If I push the button 'Create Assemblies', I get the folowing output from tlbimp:
TlbImp : warning TI3001 : Primary interop assembly 'Microsoft.Office.Interop.Excel, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71E9BCE111E9429C' is already registered for type library 'C:Program Files (x86)Microsoft OfficeRootOffice16EXCEL.EXE'.
TlbImp : error TI1033 : Cannot find type 'Microsoft.Office.Core.MsoGraphicStyleIndex' in 'Office, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c'. There could be a mismatch between the referenced assembly and the type library.

On my machine I have Office 2016 installed. It looks like there is a problem with the previously installed version of Excel. (Recently I created a library in VO with automation server and there was no problem).

After that I see the typelib, but can not add it to my project. Does someone know what could cause (and solve) the problem here?

Thank in advance.
Eric

[wriedmann]

Hi Eric,

I had a similar issue and Chris gave me these indications:

You can go to Tools/Create COM Interop assemblies and pick the OLE
object or ActiveX control from the list. Depending on if you are using a
class (like Excel), you must use the "Type library" option, for controls
you must use the "ActiveX control" option.

Wolfgang

[MathiasHakansson]

Hi,

do you need to use the "real excel", or do you just need to read/write excel files?

If you only need to read/write excel files you can use the npoi library. See info at https://archive.codeplex.com/?p=npoi

The npoi package is free and does not depend on having excel installed. Npoi is available as a nuget package.

/Mathias

[wriedmann]

Hi,

to read and write Excel files I use EPPlus, a german open source library:

https://github.com/JanKallman/EPPlus

It works very well.

Wolfgang

[Chris]

Hi Eric,

The help file mentions how to do it in VS, not in XIDE. Actually you can do it in VS if you want, let it create the dlls and then just use those dlls from XIDE as references to your app.

But this should work they way you did it from XIDE as well, I just did and the dlls where created fine. When you say you cannot add the dll to your project, what do you mean, do you get any error message? For the warning, you can safely ignore this, after creating the COM assembly, all you need to do is go to the References page of app options, COM page and double click on the entry you created before for Excel, this should now show a new dll added in the list on the bottom of the window.

If that didn't work, please go to the folder C:XIDEAssembliesInterop, do you see a folder named after the name you used for Excel when creating the COM assemblies? Does this folder contain a .dll file? If yes, you could also add a direct reference to that dll (through the Browse page) in your app.

If those folders/file did not exist, please try generating the COM assemblies again, specify "ExcelLib" for name, "Excel" for namespace and make sure the "Type Lib Importer" radio button is selected, then press "Create Assmblies", does that now create the folder/files?

Chris

[Intexso]

Hello,

I need a connection to the 'real Excel'.

@Chris: In the folder C:XIDEAssembliesInterop is created a subfolder for the ExcelLib. But in this subfolder there is no DLL. I think it is because of the error (error TI1033 : Cannot find type 'Microsoft.Office.Core.MsoGraphicStyleIndex' in 'Office, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c'. There could be a mismatch between the referenced assembly and the type library) which is generated.

I think this error has something to do with the warning which says that it is already registered. But I don't understand what to do about this.

Eric