Veloz

Hi there

We have been using Visual Studio 98 for an old, but important tool in the company. That application used Automation to start Excel and add worksheets and data.

Now we will be rewriting this applications with VS 2005 in C#, and I'm trying to understand how to achieve the same kind of interaction with Excel.

What are the ways that one can interact with Excel via VS 2005 and in .NET

Is it still possible and/or desirable to use automation to "drive" Excel

Are there other ways that are easier or better now Can one use these methods to interact with Office without VSTO

And lastly, I'm not sure I understand the exact purpose of VSTO.. Can someone help me here

From what I can tell, it looks like you are not writing stand-alone applications that invoke Office apps and interact with them, but instead are writing code that will be stored as part of an Office document (must like a macro used to do )

Any insight here would help. We have to decided between purhcasing VS 2005 Pro and VSTO and aren't sure which we need.

Thanks!
Michael



Re: Visual Studio Tools for Office Newbie Question VSTO v. VS 2005 Pro

Cindy Meister

Hi Michael

To pick up on your last paragraph first: VSTO does include pretty much everything in VS 2005 pro. This link should let you compare the products. So if think you might want to use VSTO, as well as program WinForms, ASP.NET pages or Console apps, purchasing VSTO would cover that.

To give you a really good analysis, it would help to know the version of Office you're targeting.

You are correct that VSTO is not used to automate Office applications. Currently, it provides two basic things:

1. Replace VBA code embedded in a Word or Excel file with a link to managed code (the VSTO project) that will work like VBA does currently - IOW it's linked to a specific document. For 2005, you can only develop against Office 2003, but most solutions should also work in 2007. VSTO 2008 targets Office 2007, specifically.

2. Facilitate creating Add-ins that will run within the Office application interface (2003 and 2007). Like creating a COM Add-in DLL with classic VB 6.0

Looked at broadly, you can do pretty much everything when automating Excel using VS .NET that you could using classic VB (or Delphi or whatever). You're still interacting with the same object model, although you do have to re-learn how to address things to a certain extent - especially with C#, which is rather unforgiving when you try to interface with the COM-VBA world. And you can still address the closed files to work with the data layer.

Prior to Office 2007 there really are no alternatives to what you know from the COM world. With 2007 you have the option of creating and working with files over the new OpenXML file format, meaning you wouldn't have to automate the user interface unless you want to interact with the user in that interface. More information can be found at OpenXMLDeveloper.org

Does this help at all :-)






Re: Visual Studio Tools for Office Newbie Question VSTO v. VS 2005 Pro

Veloz

Yes, that helps a whole lot, thank-you. We are using Office 2003 and probably will be for a while (we just switched in the last year from 2000)

So what I'm getting here is that VSTO won't help me "automate" Office apps in any way (in the sense of a separate application "controlling" Office). Right If we used VSTO and wanted to fire up Excel and programatically populate a worksheet with data it would be the same code as if we were using VS 2005 Pro, right (That is, we'd essentially be using COM/Automation in either tool)

I also get that VSTO is basically "replacing" VBA .. so if you have Office Apps written to used VBA, you know can write them in managed code instead.

I was sort of hoping that with .NET, MS would have provided a managed way to interact with office apps from within managed code in an application.

Do you know of any good resorces/examples for using automation in c# inside either VS 2005 Pro or VSTO

Thanks a lot, you've already been a great help!
Michael






Re: Visual Studio Tools for Office Newbie Question VSTO v. VS 2005 Pro

Cindy Meister

Veloz wrote:
So what I'm getting here is that VSTO won't help me "automate" Office apps in any way (in the sense of a separate application "controlling" Office). Right If we used VSTO and wanted to fire up Excel and programatically populate a worksheet with data it would be the same code as if we were using VS 2005 Pro, right (That is, we'd essentially be using COM/Automation in either tool)

Basically, that's correct, yes. Although you wouldn't use VSTO to fire up Excel. The VSTO tools all work within an already running version of Excel. They're primarily for providing an interface with the user. But the code that does the work in Excel would be the same for VSTO as for general automation.

Veloz wrote:
I was sort of hoping that with .NET, MS would have provided a managed way to interact with office apps from within managed code in an application.

No, the Office applications remain COM. .NET can't do anything about that. The only Office app with a .NET programming language is InfoPath, which was developed after .NET was in existence. It uses VSTA (Visual Studio for Applications).

Currently, the strategy for the "classic" Office applications, in the newer versions, is to make it possible for the developer to do pretty much everything without Office needing to be present. So documents can be processed server-side, for example. This is all being done with XML. If you were interested in Word, rather than Excel, then that would be an option for 2003 because Word provided a fairly complete XML vocabulary. The XML for Excel 2003 is limited to basic table construction; no charts, no pivot tables, etc. While it is possible to generate Excel 2007 files completely using XML, Excel 2003 users can only open the files. They can't save back to that format (unlike Word).

Veloz wrote:
Do you know of any good resorces/examples for using automation in c# inside either VS 2005 Pro or VSTO

A good place to start is the MSFT Knowledge Base and the VSTO and Office parts of the MSDN site. You'll find a number of "How to" kinds of articles. In the KB, search terms like: Excel C# or Excel .NET

This is the entry point for VSTO on MSDN. The articles there with C# code for the object model will apply outside of VSTO, as well.

Then there's the book "Visual Studio Tools for Office" by Carter and Lippert. There are two separate versions, one for VB.NET and the other for C#. Quite a large portion of the book covers working with the object models. The discussions don't go into depth, and don't cover everything, but you at least have a good head start on the object model, so it probably would be helpful.

Beyond that, I'd say search for code samples in this forum, as well as across the office.developer newsgroups (see the Please Read First message for links).