Using VSTO in a standalone application to access Excel sheets

Question

Dear all,

tried to research on that but sometimes I seem to lack some googling skills...

I want to develop a (standalone) WinForms application which uses automation for communicating with Excel. I already know how to use the Interop, but I thought the VSTO tools would provide a more comfortable or sophisticated way to do that.

My idea was:

• I could build a new standalone project with the excel references prepared.
• I could use a more sophisticated object model supplied by VSTO to communicate with Excel.

However, my findings so far make me think that:

• VSTO can only be used to build add-ins/worksheets for Excel, not to build standalone application.
• There is no more sophisticated object model than the one provided by the Interop (which has such ugly things as a locale bug, a "Open(Missing, Missing, Missing...)" method and so on.
• I found a Worksheet and a Workbook class in the VSTO namespace, but as far as I understand it, these always refer to the CS classes for sheets and workbook which you implement when defining an Excel add-in or a workbook extension. - They cannot be used as comfortable wrappers in a standalone application.

Can anybody confirm these statements or correct me where I am wrong?

Further, if there should be a way to use VSTO and its "Workbook" class to load an excel workbook from a standalone application, then how do I do that?

Thx, chiccodoro

Answer 1

VSTO is for developing Office Add-ons, not seperate applications.

For automating Office from an external application, COM Interop is still the way to do it. If you can use .NET 4.0 (and Visual Studio 2010) then you have access to Named and Optional Arguments, which make Interop a much more pleasant thing.

For example, in .NET 3.5 you would write this:

Workbooks.Open(thisFileName,
        Type.Missing, Type.Missing, Type.Missing, Type.Missing,
        Type.Missing, Type.Missing, Type.Missing, Type.Missing,
        Type.Missing, Type.Missing, Type.Missing, Type.Missing,
        Type.Missing, Type.Missing)

and in .NET 4.0 you can write this:

Workbooks.Open(thisFileName)

I don't believe you can use objects from the VSTO namespace in an external application.

Answer 2

In the meantime, I got to know another tool set to make Office programming more comfortable: The VSTO Power Tools. Although built by the VSTO team, they can be used for standalone applications also. They contain extension methods that allow for the

Workbooks.Open(thisFileName)

Still they do not solve several issues that I've had so far, e.g.

• The locale bug
• Proper releasing of all COM objects as soon as their references fall out of scope.