asked on

Complete Excel automation reference?

I'm doing an increasing amount of work with Excel in my Visual Basic .NET programming, and I'm finding the lack of a comprehensive source of information frustrating. I know bits and pieces about how to read and write data in spreadsheets, but it seems to me there ought to be, somewhere, a complete reference to the full Excel Automation object model (all the classes, methods, properties, etc.). I've been looking all over and have come up empty. The best I can find is the information provided by Intellisense while programming, but to allow my software to work with multiple versions of Excel, I'm using late binding, which means that Intellisense more or less disappears. Some information is provided in the online help, but it's neither complete nor systematic, IMHO.

What I'm hoping for is something similar to how classes are described in the online help for VB, with a list of all the classes provided and each one's members. Does it exist anywhere outside a vault in Redmond?

SOLUTION

VBRocks

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jeroen Rosink

Perhaps the object explorer in VBA editor might of some help:
Just press F2.

regards,
Jeroen

ElrondCT

ASKER

I'm not doing VBA, I'm using Visual Basic .NET in Visual Studio (2003, though 2005 is very similar). The coding is not the same (I do work in VBA as well), though they are certainly related. But not all the calls in VBA work in VB .NET, or work the same way, and you need to do other things in VB .NET, like initializing the Application object, which VBA handles automatically.

VBRocks

I recognize that ElrondCT. But the VBA help does give you the essentials... I know, I've used it to
answer some of your questions... :)

ElrondCT

ASKER

Understood. But my question is specifically a search for a complete reference of the model used for .NET. I'm tired of putting bits and pieces together, trying to figure out what's the same as in VBA, what's different, what's unique. It seems to me there ought to be somewhere that the complete object model for automation is written out, but I haven't been able to find it.

As one simple example, in VBA I can use ActiveCell.SpecialCells(xlLastCell) to find the last cell filled in for a worksheet. Neither ActiveCell nor .SpecialCells exist in VB .NET (at least, that I've been able to find); one must use a much more tedious methodology to find the end of the sheet. I don't need that specifically for my current work; I'm just offering that as an example of how knowing VBA doesn't tell me what I need.

Jeroen Rosink

If im correct you can use the VB.Net reference to approach Excel and use the VBA references to do things in Excel.

Perhaps this link gives you further information:
http://msdn2.microsoft.com/en-us/library/bb726434.aspx

ElrondCT

ASKER

Roos01, as I noted in my previous message, not all VBA operations are available in VB .NET; even some that have the same name don't have the same range of permissible arguments to the call.

I looked at the link, and followed several paths from it, but didn't see anything that provided comprehensive information about using Excel within VB .NET.

Jeroen Rosink

I thnk there is no comprehensive information of VB.Net in Excel. As Excel doesn't support VBnet itself.
Though you can use VB.Net to communication with excel parsing VBA command to excel. Therefor VBA references might come in handy.

ASKER CERTIFIED SOLUTION

Rory Archibald

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ElrondCT

ASKER

Exploring it further based on comments here, it seems that part of my issue is simply not recognizing how to properly qualify items like ActiveCell. Rorya, you're right that Application.ActiveCell works to reference that. Another issue was parsing methods that have a number of optional arguments, and which in Excel VBA are set up as a command like:

Workbooks.OpenText Filename:=DataOpen2, Origin:=xlWindows, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=True, Space:=False, Other:=False, _
FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3, 2), Array(4, 1))

(actual code I use in VBA to open a comma-delimited file)

That's not permissible in VB .NET syntax, but it turns out that you can do

Workbooks.OpenText(Filename:=DataOpen2, Origin:=xlWindows, ...)

and everything seems to get processed properly. If you're passing all arguments and doing them in the official order, the "PropertyName:=" coding may not be necessary, but I don't think it ever hurts to have it there.

So I stand corrected; it seems that basically everything in VBA is available to be called from VB .NET. It still seems odd to me that there's no structured explanation of these tweaks that are necessary to generalize use of VBA within VB .NET for calling Excel. Just providing examples doesn't always help with going beyond the needs described in the example itself. One would think that Microsoft in particular would want to encourage use of Office automation, to lock us all ever more tightly in their embrace.

Rory Archibald

That seems to be more a feature of VB.Net itself rather than anything to do with Office - you must use parentheses with all routine calls and you must supply all arguments, even if you just supply System.Missing
Thanks for the grade.
Rory

ElrondCT

ASKER

Thanks for mentioning System.Missing; I wasn't aware of that. Again, I find it frustrating that this kind of information doesn't seem to be pulled together in a single place in the VB .NET documentation. However, my experience of doing the calls is that if I name the arguments with the "PropertyName:=" prefix, it's OK to leave out the optional arguments. And the names are provided by Intellisense if I'm doing early binding, or in the VBA Help, so it's not impossible to put them in (and perhaps it documents the code a bit better!).