Can Excel use .Net classes?

foozball3000

foozball3000

Executive Member
Joined
Oct 28, 2008
Messages
5,928
Reaction score
1,738
Location
Kyalami
In theory this should be plausible, as Excel is written on the .Net framework.
I'm so sick of coding (99% debugging) in VBA, so I'd love the idea of coding a Excel workbook from C#.Net, or even VB.Net. Is this possible?

I can find mountains of code that controls a WorkBook from Visual Studio, but nothing that says anything about Excel using a .Net scripted class as a macro.

Please tell me that this can be done...
 
I'm actually pretty sure that Excel is NOT written in the .NET framework. You access the Excel libraries through COM+ Interop in .NET. A nice alternative, if you are using Excel 2007, is to use the new .xlsx format, which is OpenXML. It makes creating Excel workbooks / worksheets a breeze. Try search for OpenXML a bit, the URL escapes me right now and I'm too lazy to open a new tab to Google it... :p
 
well vs 2008 can do things with excel, but then you are stuck with a workbook + dll at end.
 
If you want to manipulate XLS file from .Net you need to have the office package installed. Need the Library DLL's, for COM+ interop.
Using FlexCel .Net component from TMS Software, allows you to do anything you can with an XLS file, without having office installed (or any Excel stuff), or any COM+ interop requirements. Works liks a charm.
 
I think the OP actually wants to replace the arb VBA marcos within an Excel workbook to a call or two to a custom made DLL.

So lets say you're trying to work out the weight of the world. There's no such calculation within excel, so you have to write your own macro within excel to be able to call it from a cell... correct?

Now instead of doing (and debugging it within) the stupid VBA macro utility they give you within excel, he wants to write a custom function within his own dll (I suppose?) and get excel to call that. This way it's easier to debug as the custom code is within a language that hasn't died out 10 million years ago and very easy to debug if necessary.

I know there's no answer in my post, I just thought I'd re-iterate what I think the solution is the OP is looking for before people go ahead and recommend 3rd party software to do stuff with that's not related to what the OP wanted in the first place ;)
 
Necuno said:
well vs 2008 can do things with excel, but then you are stuck with a workbook + dll at end.
Click to expand...

I'm 100% fine with sending the client a Workbook with a dll. The current approach is to code the whole project in Excel VBA (2007) then convert it back to 2003. But as we're trying to make Excel do things that it never was intended to be used for... and we might need the power that .Net gives.

If nothing else, coding in .Net and converting it to VBA will be 1000 times faster than coding in VBA
 
AcidRaZor said:
I think the OP actually wants to replace the arb VBA marcos within an Excel workbook to a call or two to a custom made DLL.

So lets say you're trying to work out the weight of the world. There's no such calculation within excel, so you have to write your own macro within excel to be able to call it from a cell... correct?

Now instead of doing (and debugging it within) the stupid VBA macro utility they give you within excel, he wants to write a custom function within his own dll (I suppose?) and get excel to call that. This way it's easier to debug as the custom code is within a language that hasn't died out 10 million years ago and very easy to debug if necessary.

I know there's no answer in my post, I just thought I'd re-iterate what I think the solution is the OP is looking for before people go ahead and recommend 3rd party software to do stuff with that's not related to what the OP wanted in the first place ;)
Click to expand...

ROFL! +1000 you're spot on with your entire post. :D
 
I'm sure someone must have heard of

"Visual Studio Tools for Office" ????

http://en.wikipedia.org/wiki/Visual_Studio_Tools_for_Office

Visual Studio Tools for Office (VSTO) is a set of development tools available in the form of a Visual Studio add-in (project templates) and a runtime that allows Microsoft Office 2003 and later versions of Office applications to host the .NET Framework Common Language Runtime (CLR) to expose their functionality via the .NET type system.
Click to expand...

It's pretty old news by now, and Microsoft started integrating the functionality into later versions of Visual Studio (Necuno up there touched on this)...so when you create a new project in VS...look a bit down under "Other" or "Office" ..

..and no this is more than "Add-ins" (although an Add-In is pretty powerful thing too). I actually went through the same thing where the users WANTED to use Excel and i was not going to sit and write VBA/Macros for the rest of my life. So VSTO allowed me to write full C# .NET code inside Excel, this -includes- manipulating the entire GUI [i.e which menus shows and whatnot] .

An example of what this is like: You would be in Visual Studio and your IDE front end would literally be an Excel Sheet instead of a Windows/Web Form...

The catch is, the actual Workbook /.XLS is the actual program and depending what you do will compile into a .XLS + .DLLs/Modules and sometimes even an .EXE. It needs the VSTO runtimes [a 2MB installation], which is pretty much like installing Flash/Silverlight or something on a PC. Obviously they can't simply email the sheet around without the person on the other end having the same runtimes etc etc.

What i did was coding a new panel [that annoying panel on the right] , which allows connection to a database, selecting some options and generating pivot charts/reports right there...all nicely C# code.

In hindsight i could've created a whole new .NET front end [seperately] which just launched excel and did the same thing, but VSTO actually does have some nifty tools which is not available otherwise, simply because Excel is the front end already..

...that said the advantage of using .NET code meant i could re-use the same code -outside- Excel, if users ever felt they want to do the same thing via the web [as an example] then i do not have to completely rewrite the code..
 
Last edited:
diabolus said:
I'm sure someone must have heard of

"Visual Studio Tools for Office" ????

http://en.wikipedia.org/wiki/Visual_Studio_Tools_for_Office



It's pretty old news by now, and Microsoft started integrating the functionality into later versions of Visual Studio (Necuno up there touched on this)...so when you create a new project in VS...look a bit down under "Other" or "Office" ..

..and no this is more than "Add-ins" (although an Add-In is pretty powerful thing too). I actually went through the same thing where the users WANTED to use Excel and i was not going to sit and write VBA/Macros for the rest of my life. So VSTO allowed me to write full C# .NET code inside Excel, this -includes- manipulating the entire GUI [i.e which menus shows and whatnot] .

An example of what this is like: You would be in Visual Studio and your IDE front end would literally be an Excel Sheet instead of a Windows/Web Form...

The catch is, the actual Workbook /.XLS is the actual program and depending what you do will compile into a .XLS + .DLLs/Modules and sometimes even an .EXE. It needs the VSTO runtimes [a 2MB installation], which is pretty much like installing Flash/Silverlight or something on a PC. Obviously they can't simply email the sheet around without the person on the other end having the same runtimes etc etc.

What i did was coding a new panel [that annoying panel on the right] , which allows connection to a database, selecting some options and generating pivot charts/reports right there...all nicely C# code.

In hindsight i could've created a whole new .NET front end [seperately] which just launched excel and did the same thing, but VSTO actually does have some nifty tools which is not available otherwise, simply because Excel is the front end already..

...that said the advantage of using .NET code meant i could re-use the same code -outside- Excel, if users ever felt they want to do the same thing via the web [as an example] then i do not have to completely rewrite the code..
Click to expand...
This s pretty much how I understood VSTO to work and more or less what the OP wanted, or as close as he would get.
 
You must log in or register to reply here.
Back
Top
Sign up to the MyBroadband newsletter
X