How to connect with Excel (via OLE)

[Chris]

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.

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

[Intexso]

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

[Karl-Heinz]

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:

Code: Select all

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

[Intexso]

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

[Chris]

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

[Karl-Heinz]

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

[Intexso]

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

[Chris]

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

[Karl-Heinz]

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?

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.

Code: Select all

// C:XIDEAssembliesInteropExcelLibexcel.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

[robert]

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