Excel COM object

  • Thread starter Thread starter Dent2
  • Start date Start date
D

Dent2

I wrote a nice C# excel routine to automate the formatting of some raw CSV
data. I wrote the
routine on a WinXP computer with Visual Studio .NET 2003 and Office XP
installed. It compiled and ran on my XP machine without problems. Then, I
tried to run the program on a Windows 2000 computer with Office 2000
installed. I got an error:

System.IO.FileNotFoundException: File or assembly name
"Microsoft.Office.Interop.Excel, or one of its dependencies, was not found."

I think that this problem is occurring because the program is compiled on my
XP/Office
10.0 COM-Object system and so it doesn't include all the previous version
objects that are needed to make use of earlier versions of Excel.

My current code employs early-binding of the excel object. I tried to use
late-binding in hopes that this would solve my problem, but I have been
unable to make this work. My trials at late-binding fail durinng
compilation when the Excel.Application() object is created. The compiler
tells me that there is no reference to Excel. My understanding is that, in
late binding, the Excel COM object is created and compiled during the
execution of the program. It shouldn't need a referencing 'using Excel'
statement.

Has anyone in this newsgroup had experience with this problem? Perhaps,
you've dealt with this, or you can refer me to another newsgroup where
someone might be more knowledgable about this issue.

Thank you,
Dennis
 
System.IO.FileNotFoundException: File or assembly name
"Microsoft.Office.Interop.Excel, or one of its dependencies, was not
Click to expand...
found."

if you use Excel.Application in your .NET code, you assume to have a
COMInterop assembly generated from Excel type library. just look for it in
the /debug or /release folder and distribute it with your application. that
way the early binding will work.

however, if you wish to use late binding, you do not have to use
Excel.Application object. in fact, you should not use it at all! instead,
create a type that correspond to it, create object of the type and invoke
its metods/properties. it is much easier to work with late binding with
VB.NET, though, because vb.net supports late binding at language level.

Regards, Wiktor

// c# code
try
{
Type t = Type.GetTypeFromProgID( "Word.Application" );
object w = Activator.CreateInstance( t );

// w.Visible = true
t.InvokeMember( "Visible", BindingFlags.SetProperty,
null, w, new Object[] { true } );

// w.Documents...
object docs = t.InvokeMember( "Documents", BindingFlags.GetProperty,
null, w, null );
// ...Add
t.InvokeMember( "Add", BindingFlags.InvokeMethod,
null, docs, null );

w = null;
}
catch( TypeLoadException ex )
{
...
}

// vb.net code
dim o as object
o = CreateObject( "Word.Application" )
o.Visible = True
o.Documents.Add
 
Wiktor,
if you use Excel.Application in your .NET code, you assume to have a
COMInterop assembly generated from Excel type library. just look for it in
the /debug or /release folder and distribute it with your application. that
way the early binding will work.
Click to expand...

I was not able to get my early-binding code to work. In short, here is the
code:

c#
using Microsoft.Office.Interop.Excel;
using excel = Microsoft.Office.Interop.Excel;
class GetInventory
{
public static void Main()
{
try
{
excel.Application ExcelObj = new excel.Application();
... (working with ExcelObj)

System.Runtime.InteropServices.Marshal.ReleaseComObject(ExcelObj);
ExcelObj = null;
}
catch(IOException e) //not sure about this catch statement. I need
to study my exception handling.
{
...
}
This is the code that generated the "System.IO.FileNotFoundException" that I
wrote about in my first posting. I looked for the COMInterop assembly in
the locations you suggested, but I didn't find anything. Do I need to use a
specific compiler switch to generate this assembly? I compiled with the
"Build" on the toolbar of Visual Studio 2003. What kind of file extension
am I looking for on that COMInterop assembly?

I tried installing the PIAs for XP on the 2000 machine, but this was a
mistake. I also tried copying Interop.excel.dll and
microsoft.office.interop.excel.dll to that machine and registering them with
regsvr32. This was also an obvious mistake because on both attempts to
register, I got the error

".dll was loaded, but the DLLRegServer entry point was not found.
DllRegisterServer may not be exported, or a corrupt version of
interop.excel.dll may be in memory. Consider using pview to detect and
remove it."

It was a shot in the dark on my part.
if you wish to use late binding, you do not have to use
Excel.Application object. in fact, you should not use it at all! instead,
create a type that correspond to it, create object of the type and invoke
its metods/properties. it is much easier to work with late binding with
VB.NET, though, because vb.net supports late binding at language level.
Click to expand...

With regards to the late binding example, thank you. It works just as you
said. I had tried a couple of other examples, but they didn't work. From
your example, I expect to be able to build something useful.

Thank you,
Dennis
 
I have also followed up your late binding code , its great because
earlier I was creating the ApplicationClass instance

but with this code I get the following exception at the add line

I am using Excel instead of Word
the code is

Type t = Type.GetTypeFromProgID( "Excel.Application" );
object w = Activator.CreateInstance( t );
// Visible True
t.InvokeMember( "Visible", BindingFlags.SetProperty,null, w, new
Object[] { true } );
// w WorkBooks
object docs = t.InvokeMember( "WorkBooks",
BindingFlags.GetProperty,null, w, null );
// w Add method
t.InvokeMember( "Add", BindingFlags.InvokeMethod,null, docs, null );
w = null;

the exception is

"System.Reflection.TargetException: Object does not match target
type.\r\n at System.RuntimeType.InvokeDispMethod(String name,
BindingFlags invokeAttr, Object target, Object[] args, Boolean[]
byrefModifiers, Int32 culture, String[] namedParameters)\r\n at
System.RuntimeType.InvokeMember(String name, BindingFlags invokeAttr,
Binder binder, Object target, Object[] args, ParameterModifier[]
modifiers, CultureInfo culture, String[] namedParameters)\r\n at
System.Type.InvokeMember(String name, BindingFlags invokeAttr, Binder
binder, Object target, Object[] args)\r\n at
ExcelTry2.ExcelInterOp.Init() in c:\\documents and settings\\champ\\my
documents\\visual studio
projects\\exceltry2\\exceltry2\\excelinterop.cs:line 41"
 
I have tried the same thing,it started working ,but when i integrated this with sql server by creating clr function it is throwing error as

Msg 6522, Level 16, State 2, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "fn_xirr":
System.Security.SecurityException: Request for the permission of type 'System.Security.Permissions.SecurityPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
System.Security.SecurityException:
at System.Security.CodeAccessSecurityEngine.ThrowSecurityException(Assembly asm, PermissionSet granted, PermissionSet refused, RuntimeMethodHandle rmh, SecurityAction action, Object demand, IPermission permThatFailed)
at System.Security.CodeAccessSecurityEngine.ThrowSecurityException(Object assemblyOrString, PermissionSet granted, PermissionSet refused, RuntimeMethodHandle rmh, SecurityAction action, Object demand, IPermission permThatFailed)
at System.Security.CodeAccessSecurityEngine.CheckSetHelper(PermissionSet grants, PermissionSet refused, PermissionSet demands, RuntimeMethodHandle rmh, Object assemblyOrString, SecurityAction action, Boolean throwException)
at System.Security.CodeAccessSecurityEngine.CheckSetHelper(CompressedStack cs, PermissionSet grants, PermissionSet refused, PermissionSet demands, RuntimeMethodHandle rmh, Assembly asm, SecurityAction action)
at Wpt.Framework.SQLClrInternal.Calculations.XIRR(String _cash, String _dates)
at UserDefinedFunctions.XIRR(String cash, String dates)

Thankyou
 
Back
Top