Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1712
  • Last Modified:

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?
0
ElrondCT
Asked:
ElrondCT
  • 5
  • 3
  • 2
  • +1
2 Solutions
 
VBRocksCommented:
Open Excel, Go to the Tools Menu | Macro | Visual Basic Editor.
When that comes up, go to the Help menu | Microsoft Visual Basic Help

Gives you all of the info you need, with examples...

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

regards,
Jeroen
0
 
ElrondCTAuthor Commented:
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.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
VBRocksCommented:
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... :)

0
 
ElrondCTAuthor Commented:
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.
0
 
Jeroen RosinkCommented:
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
0
 
ElrondCTAuthor Commented:
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.
0
 
Jeroen RosinkCommented:
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.

0
 
Rory ArchibaldCommented:
Re your comment on ActiveCell.SpecialCells(xlLastCell):
Neither of those "exist" in VB.Net because they are part of the Excel object model. Activecell is a shortcut for Application.Activecell so in order to use it in VB.Net, you have to qualify it as belonging to an application object that you have instantiated, though I would generally recommend avoiding using Activecell or Selection unless absolutely necessary. You would also need to declare a constant for xlLastCell if you are using late binding. Other than that, the methods and properties are pretty much the same as far as I am aware, though you may need to explicitly pass all arguments, even if you are not using them. The main thing to be aware of is that you must fully qualify the objects/methods/properties used rather than relying on the default objects as you can in VBA (though you shouldn't really do it there either)
Regards,
Rory
0
 
ElrondCTAuthor Commented:
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.
0
 
Rory ArchibaldCommented:
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
0
 
ElrondCTAuthor Commented:
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!).
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now