fbpx
Welcome, Guest
Username: Password: Remember me
  • Page:
  • 1
  • 2

TOPIC: How to connect with Excel (via OLE)

How to connect with Excel (via OLE) 6 months 5 days ago #1

  • Intexso
  • Intexso's Avatar Topic Author
  • Offline
  • Posts: 63
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

Please Log in or Create an account to join the conversation.

How to connect with Excel (via OLE) 6 months 5 days ago #2

  • Intexso
  • Intexso's Avatar Topic Author
  • Offline
  • Posts: 63
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

Please Log in or Create an account to join the conversation.

How to connect with Excel (via OLE) 6 months 5 days ago #3

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 SDKs\Windows\v10.0A\bin\NETFX 4.6 Tools

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

Chris
XSharp Development Team
chris(at)xsharp.eu

Please Log in or Create an account to join the conversation.

How to connect with Excel (via OLE) 6 months 5 days ago #4

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 www.xsharp.info/help/example-4-ole-automation.html . It describes the process.


Robert
XSharp Development Team
The Netherlands
This email address is being protected from spambots. You need JavaScript enabled to view it.

Please Log in or Create an account to join the conversation.

How to connect with Excel (via OLE) 6 months 4 days ago #5

  • Intexso
  • Intexso's Avatar Topic Author
  • Offline
  • Posts: 63
@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 Office\Root\Office16\EXCEL.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

Please Log in or Create an account to join the conversation.

How to connect with Excel (via OLE) 6 months 4 days ago #6

  • wriedmann
  • wriedmann's Avatar
  • Offline
  • Posts: 1423
  • Karma: 6
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
Wolfgang Riedmann
Meran, South Tyrol, Italy
This email address is being protected from spambots. You need JavaScript enabled to view it.
www.riedmann.it - docs.xsharp.it

Please Log in or Create an account to join the conversation.

How to connect with Excel (via OLE) 6 months 4 days ago #7

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 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

Please Log in or Create an account to join the conversation.

How to connect with Excel (via OLE) 6 months 4 days ago #8

  • wriedmann
  • wriedmann's Avatar
  • Offline
  • Posts: 1423
  • Karma: 6
Hi,

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

github.com/JanKallman/EPPlus

It works very well.

Wolfgang
Wolfgang Riedmann
Meran, South Tyrol, Italy
This email address is being protected from spambots. You need JavaScript enabled to view it.
www.riedmann.it - docs.xsharp.it

Please Log in or Create an account to join the conversation.

How to connect with Excel (via OLE) 6 months 4 days ago #9

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:\XIDE\Assemblies\Interop, 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
XSharp Development Team
chris(at)xsharp.eu

Please Log in or Create an account to join the conversation.

How to connect with Excel (via OLE) 6 months 4 days ago #10

  • Intexso
  • Intexso's Avatar Topic Author
  • Offline
  • Posts: 63
Hello,

I need a connection to the 'real Excel'.

@Chris: In the folder C:\XIDE\Assemblies\Interop 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

Please Log in or Create an account to join the conversation.

How to connect with Excel (via OLE) 6 months 4 days ago #11

Intexso wrote: Hello,

I need a connection to the 'real Excel'.

@Chris: In the folder C:\XIDE\Assemblies\Interop 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.


Hmm, I doubt this is the problem, because I also get this warning here, but the dlls get created fine.

It must be something else, but let's not spend more time doing it this way, just do as Robert said, search for "Microsoft.Office.Interop.Excel" in your disk (most likely inside C:\Program Files (x86)) and you will most probably find the .dll already created, so just add a reference to it. In case the file still doesn't exist, please do as the help file says, use VS to generate it, then again copy the dll to a location you prefer and add a reference in your app to it.

Please let me know if one of the above works for you.

Chris
XSharp Development Team
chris(at)xsharp.eu

Please Log in or Create an account to join the conversation.

How to connect with Excel (via OLE) 6 months 4 days ago #12

  • Intexso
  • Intexso's Avatar Topic Author
  • Offline
  • Posts: 63
Hello Chris,

I agree.

I found this DLL on my drive. I have added it to the references.

In my VO application I made a prefix for alle class names. Like: ExcelApplication, ExcelWorksheet and so on. Is there some trick to use the same prefixes or do I have to remove them in Xide?

Eric

Please Log in or Create an account to join the conversation.

How to connect with Excel (via OLE) 6 months 4 days ago #13

Hi Chris,

PMFJI,

Here´s what i´ve tried and it works. The only "problem" is to uncheck the "Show only clr 4.0 assemblies" checkbox. Then on my "GAC" tabpage "Microsoft.Office.Interop.Excel (v2.0) (15.0.0.0)" becomes visible. After adding the reference something like this works:


USING Microsoft.Office.Interop.Excel

FUNCTION Start(  ) AS VOID
//VAR oExcelApp := Microsoft.Office.Interop.Excel.Application{}
VAR oExcelApp := Application{}

	oExcelApp:visible := TRUE 
	oExcelApp:Workbooks:Add()
	
VAR oWorkSheet := ( Worksheet) oExcelApp:ActiveSheet
	
	oWorkSheet:Cells[1, "A"] := "One"
	oWorkSheet:Cells[1, "B"] := "Two"

          
     wait 
     
     oExcelApp:Quit()  
	
RETURN 

regards
Karl-Heinz

Please Log in or Create an account to join the conversation.

How to connect with Excel (via OLE) 6 months 3 days ago #14

  • Intexso
  • Intexso's Avatar Topic Author
  • Offline
  • Posts: 63
Hi Karl,

Thanks. I tried to add via GAC as you said. But I got a warning, because it is a CLR2 and my application is CLR4.

Does that matter?

Eric

Please Log in or Create an account to join the conversation.

How to connect with Excel (via OLE) 6 months 3 days ago #15

Guys,

Hmm, yes it is possible to use old < .Net 4.0 assemblies from >= .Net 4.0 applications, but generally that's not a good idea, now I think you will have both runtimes loaded in memory at the same time. So best way IMO is to create and use a .Net 4 dll and use this one. It will work with a CLR 2 version, too, though.

About the prefixes, not sure what you mean, to be honest I never dealt with OLE in VO. Is there an option in the settings about OLE in VO to do something like that? How about inheriting the real classes with new ones, that uses the names you want, like CLASS ExcelApplication INHERIT Microsoft.Office.Interop.Excel.Application?

Chris
XSharp Development Team
chris(at)xsharp.eu

Please Log in or Create an account to join the conversation.

How to connect with Excel (via OLE) 6 months 3 days ago #16

Hi Chris,

yes the pia reference is .net 2.0. Honestly spoken, i don´t know if there are e.g. 4.0 pias available, and how the different office versions behave..

a quick search for "primary interop assemblies redistributable office" gives no clear answers.

personally i´m using a ms-office independet lib to read/write excel files, so i do not have to care about which ms-office versions are installed.

regards
Karl-Heinz

Please Log in or Create an account to join the conversation.

How to connect with Excel (via OLE) 6 months 3 days ago #17

  • Intexso
  • Intexso's Avatar Topic Author
  • Offline
  • Posts: 63
Hi Chris,

Prefixes: Yes, this was possible in VO. I had to use that in VO, because otherwise it conflicted with other class names. But I found out that the find/replace function in XIDE works :-)

About Microsoft.Office.Interop.Excel.Application CLR 4: Do you mean by creating a .Net 4.0 dll to use the 'Create COM Interop Assemblies' in XIDE from 'Microsoft Excel 16.0 Object Library' ? In that case I have my problem that it is not generated.

In a search on internet I don't see discussions about this difference.

In think 'the world' is using the standard pia, so it seems to me best to use the CLR2 version.

Eric

Please Log in or Create an account to join the conversation.

How to connect with Excel (via OLE) 6 months 3 days ago #18

Hi Eric,

Yeah, it's hard to say why it doesn't work when you try to create the lib. Do you have any version of VS? You can try to create it there (does not even need to be a X# project, you can do it also in c#), let it create the .dll and you can use it then as is in XIDE.

But, ok, if the CLR 2.0 version works fine for you and you have no issues, yeah, I guess you can use this one fine as well.

Chris
XSharp Development Team
chris(at)xsharp.eu

Please Log in or Create an account to join the conversation.

How to connect with Excel (via OLE) 6 months 2 days ago #19

Chris wrote:
If that didn't work, please go to the folder C:\XIDE\Assemblies\Interop, 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?


Hi Chris,

Following your advice i succesfully created such a excel.dll, which is placed in the \interop subdir. I removed the (2.0) pia reference and added the excel.dll instead. conclusion: My posted sample still works - without any code changes :-)

But when i examine via ilspy the created excel.dll i see that still .net 2.0 is referenced. It seems that it makes no difference to reference the (2.0) pia or the created excel.dll.

// C:\XIDE\Assemblies\Interop\ExcelLib\excel.dll
// excel, Version=1.8.0.0, Culture=neutral, PublicKeyToken=null
// Global type: <Module>
// Architecture: AnyCPU (64-bit preferred)
// Runtime: .NET 2.0

using System.Reflection
using System.Runtime.InteropServices

[assembly. ImportedFromTypeLib("Excel")];
[assembly. TypeLibVersion(1, 8)];
[assembly. Guid("00020813-0000-0000-c000-000000000046")];
[assembly. AssemblyVersion("1.8.0.0")];


Regards
Karl-Heinz

Please Log in or Create an account to join the conversation.

How to connect with Excel (via OLE) 6 months 2 days ago #20

Karl-Heinz ,
According to the docs: if you use the .Net framework 4 version of tlbimp then you should get a CLR4 type lib.
Where was your tblimp.exe located. Or what does tlbimp show as version number of the first line if you invoke it.
Mine shows:
Microsoft (R) .NET Framework Type Library to Assembly Converter 4.6.1055.0
Copyright (C) Microsoft Corporation. All rights reserved.



Robert
XSharp Development Team
The Netherlands
This email address is being protected from spambots. You need JavaScript enabled to view it.

Please Log in or Create an account to join the conversation.

  • Page:
  • 1
  • 2