VSTO Unit Testing

[Enrique]

Question

I am posting this question again (3rd time) because some issues with my no
spam alias.
Here it is the question:
I have not been able to run unit tests for a VSTO (2005) project. I have an
Excel project and a unit test project in the same solution. I have not found
the way to initialize the runtimecallback as required by the code generated
by visual studio (see statement below). I relay heavily on unit testing and
having this working is a must. I hope I am missing something obvious here.
Please help.

'code generated by visual studio
Dim RuntimeCallback As
Global.Microsoft.VisualStudio.Tools.Applications.R untime.IRuntimeServiceProvider = Nothing 'TODO: Initialize to an appropriate value

[Luke Zhang [MSFT]]

In a Unit Test project, we can simple create a class inplementing the
interface 'IRuntimeServiceProvider', and Initialize a object from this
class here. For example:
add a class to the Test project:
Public Class Class1
Implements
Microsoft.VisualStudio.Tools.Applications.Runtime. IRuntimeServiceProvider
Public Function GetService(ByVal serviceType As System.Type) As Object
Implements
Microsoft.VisualStudio.Tools.Applications.Runtime. IRuntimeServiceProvider.Ge
tService
Return Nothing
End Function

End Class

and change following line:

Dim RuntimeCallback As
Global.Microsoft.VisualStudio.Tools.Applications.R untime.IRuntimeServiceProv
ider = Nothing 'TODO: Initialize to an appropriate value
to:

Dim RuntimeCallback As IRuntimeServiceProvider = New Class1()

If there is any further questions, please feel free to let us know

Luke

[Enrique]

"Luke Zhang [MSFT]" wrote:

In a Unit Test project, we can simple create a class inplementing the
interface 'IRuntimeServiceProvider', and Initialize a object from this
class here. For example:
add a class to the Test project:
Public Class Class1
Implements
Microsoft.VisualStudio.Tools.Applications.Runtime. IRuntimeServiceProvider
Public Function GetService(ByVal serviceType As System.Type) As Object
Implements
Microsoft.VisualStudio.Tools.Applications.Runtime. IRuntimeServiceProvider.Ge
tService
Return Nothing
End Function

End Class

and change following line:

Dim RuntimeCallback As
Global.Microsoft.VisualStudio.Tools.Applications.R untime.IRuntimeServiceProv
ider = Nothing 'TODO: Initialize to an appropriate value
to:

Dim RuntimeCallback As IRuntimeServiceProvider = New Class1()

If there is any further questions, please feel free to let us know

Luke

Luke: I still have the problem. I can not access objects such as the
worksheet collection. For instance:
extendedThisWorkbook.Worksheets.count fails because worksheets collection is
null. I have tried running first ThisWorkbook_startup in ThisWorkbook object
(extended excel) and with and without excel running

Here is the code I have. See the notes that explain what is happening.
Please help.

Public Class Class1 : Implements
Microsoft.VisualStudio.Tools.Applications.Runtime. IRuntimeServiceProvider
Public Function GetService(ByVal serviceType As System.Type) As
Object Implements
Microsoft.VisualStudio.Tools.Applications.Runtime. IRuntimeServiceProvider.GetService
Return Nothing
End Function

End Class
<TestMethod()> _
Public Sub FirstTest()
'this is as suggested in your email
Dim RuntimeCallback As
Global.Microsoft.VisualStudio.Tools.Applications.R untime.IRuntimeServiceProvider = New Class1 'TODO: Initialize to an appropriate value

'this is written by visual studio. I changed the name of the
variable from target to extendedThisWorkbook. Easier for me to understand.
Dim extendedThisWorkbook As Global.ExcelTestWorkbook.ThisWorkbook =
New Global.ExcelTestWorkbook.ThisWorkbook(RuntimeCallb ack)

'my test starts here

'to run a method in a public module in the ExcelTestWorkbook project
Global.ExcelTestWorkbook.Main()

'TestThisFunction is a method in a public module in the
ExcelTestWorkbook project
'It will return true if Global.ExcelTestWorkbook.Main (previous
statement) works ok.
Dim actual As Boolean =
Global.ExcelTestWorkbook.Perro.TestThisFunction

'this does not fail, it is working fine. This means I have a
reference to a public module in the ExcelTestWorkbook Project working.
Assert.IsTrue(actual)

'this does not fail, it is working fine. This means I have a
reference to Thisworkbook (extended workbook) object in the
ExcelTestWorkbook Project.
Assert.IsTrue(extendedThisWorkbook.IAmAlive)

'here is where I have problems, any call like the one below throws
an exception
'system.nullReferenceException. extendedThisWorkbook is null.
'extendedThisWorkbook.InnerObject is null, same for
extendedThisWorkbook.Worksheets and similars
Assert.AreEqual(5, extendedThisWorkbook.Worksheets.Count)
End Sub

[Luke Zhang [MSFT]]

There is a limitation here. For a work around, I suggest you may create
your own test code in the unit test, for example, open the office document
(Excel workbook) with automation and access its properties.

Luke Zhang

[Enrique]

"Luke Zhang [MSFT]" wrote:

There is a limitation here. For a work around, I suggest you may create
your own test code in the unit test, for example, open the office document
(Excel workbook) with automation and access its properties.

Luke Zhang

Luke:

That workaround will not help me to access the extended excel objects
(aggregated objects) created with VSTO. If I open the workbook with the
extended (aggregated objects) as you suggest, another excel instance is
created that is disconnected to the excel instance that visual studio has
opened for accessing the extended workbook. This last instance is the one
that has the extended objects. If I try to get a reference to this last
instance, I get the following error:

Unable to cast COM object of type 'System.__ComObject' to interface type
'Microsoft.Office.Interop.Excel.Application'. This operation failed because
the QueryInterface call on the COM component for the interface with IID
'{000208D5-0000-0000-C000-000000000046}' failed due to the following error:
No such interface supported (Exception from HRESULT: 0x80004002

I do not understand something here. In summary the situation is:

If I am in a testproject with a main VSTO excel main project:

1. A reference to an extended workbook created as you suggest in your first
email does not provide access to the excel inner object (host object I
believe is the right term).

2. The same reference above does not give access to extended excel objects
(aggregated objects). Please confirm if this is correct.

3. Opening the workbook from the test project fires another excel instance
that does not help to get the extended objects. Please confirm if this is
correct.

The goal is to get VSTO working with unit tests in Visual Studio accessing
somehow both, excel objects and extended VSTO objects. Is this possible or is
this the limitation you mentioned in your email?

Thanks

[Luke Zhang [MSFT]]

I think you need a completed test project here, not just create a "New
Test" from the wizard. You may select menu "File/Add/New Project" to add a
new Test project. There won't be a conference conflicts in this new
project. You don't need to add a reference to the Excel project, just open
it as a normal Excel document with automation.

Luke

[Enrique]

"Luke Zhang [MSFT]" wrote:

I think you need a completed test project here, not just create a "New
Test" from the wizard. You may select menu "File/Add/New Project" to add a
new Test project. There won't be a conference conflicts in this new
project. You don't need to add a reference to the Excel project, just open
it as a normal Excel document with automation.

Luke

Sorry Luke but that is what I did! I created a brand new test project and I
opened normally with automation.

To avoid going around in circles, please tell me if I can access the
extended excel (aggregated objects) from the Test Project and how. I have not
been able to do it. That is the main question. If have no problem with
automation and accesing excel with Visual Studio, I have done plenty of
that. What is new for me are the extended (aggregated) excel objects (such as
namedrange) and the Test Projects on a VSTO 2005 project. I need to make this
work, please help.

[Luke Zhang [MSFT]]

This work on my side in a new Test Project. Here is a Unit test method I
used.

<TestMethod()> Public Sub TestMethod1()
' TODO: Add test logic here

Dim objApp As Microsoft.Office.Interop.Excel.Application

objApp = New Microsoft.Office.Interop.Excel.Application

objApp.Workbooks.Open("C:\Documents and Settings\lukezhang\My
Documents\Visual Studio
2005\Projects\ExcelWorkbook4\ExcelWorkbook4\ExcelW orkbook4.xls")

Assert.AreEqual(5, objApp.Workbooks(0).Worksheets.Count)
objApp.Quit()

End Sub
Is there anything different with your code?

Luke

[Enrique]

"Luke Zhang [MSFT]" wrote:

This work on my side in a new Test Project. Here is a Unit test method I
used.

<TestMethod()> Public Sub TestMethod1()
' TODO: Add test logic here

Dim objApp As Microsoft.Office.Interop.Excel.Application

objApp = New Microsoft.Office.Interop.Excel.Application

objApp.Workbooks.Open("C:\Documents and Settings\lukezhang\My
Documents\Visual Studio
2005\Projects\ExcelWorkbook4\ExcelWorkbook4\ExcelW orkbook4.xls")

Assert.AreEqual(5, objApp.Workbooks(0).Worksheets.Count)
objApp.Quit()

End Sub
Is there anything different with your code?

Luke

Sorry Luke but you are focusing on something that is not an issue.
Automation is not what I am asking about. It works quite well. Please focus
on accessing EXTENDED EXCEL OBJECTS from a TestProject. To make myself clear,
I understand that an EXTENDED EXCEL OBJECT is an object created by VSTO 2005
that acts like excel but IS NOT AN EXCEL object (it is also called a host
control). Also, they are managed objects, they are not COM objects).

Please show me how to access an VSTO extended excel object from a Test
Project and more specifically a NamedRange object. It would be something like:

Dim extObj as Microsoft.Office.Tools.Excel.NamedRange = something_that_works

Please note that the object above is not an Microsoft.Office.Interop.Excel
object. There is not a NamedRange object in excel but there is one in VSTO.
It is not possible, if I understand it correctly, to access an extended excel
object from automation.

The NamedRange I am trying to access is public, it is contained in a VSTO
Worksheet (such as Sheet1) object that is public by default.

Please help.

[Luke Zhang [MSFT]]

Hello,

To use extended excel a object in test project, we need a runtime host and
runtime service provider, for example, to inistialize
Microsoft.Office.Tools.Excel.WorkBook, we need to call its Constructor:

Public Sub New ( _
hostItemProvider As IHostItemProvider, _
runtimeServiceProvider As IRuntimeServiceProvider, _
primaryCookie As String, _
container As Object, _
identifier As String _
)

However, the two interface IHostItemProvider and IRuntimeServiceProvider
is not intended to be used directly from customer's code. Automation is the
only solution here.

Luke

[Enrique]

"Luke Zhang [MSFT]" wrote:

Hello,

To use extended excel a object in test project, we need a runtime host and
runtime service provider, for example, to inistialize
Microsoft.Office.Tools.Excel.WorkBook, we need to call its Constructor:

Public Sub New ( _
hostItemProvider As IHostItemProvider, _
runtimeServiceProvider As IRuntimeServiceProvider, _
primaryCookie As String, _
container As Object, _
identifier As String _
)

However, the two interface IHostItemProvider and IRuntimeServiceProvider
is not intended to be used directly from customer's code. Automation is the
only solution here.

Luke

Luke: If automation is the only solution, then please show me how to access
an extended excel object with automation.
Thanks

[Luke Zhang [MSFT]]

We cannot access extended excel object with automation also. What I means
is open the Excel document with automation and test its properties in a
Unit Test.

Luke

[Enrique]

"Luke Zhang [MSFT]" wrote:

We cannot access extended excel object with automation also. What I means
is open the Excel document with automation and test its properties in a
Unit Test.

Luke

Luke, please answer this: Can I access VSTO 2005 extended objects from a
Visual Studio Test Project? If not, this means that Visual Studio Testing
Tools can not be used for fully testing VSTO 2005 projects with extended
excel objects because there is no way tests methods can access extended excel
objects.

Please say if this is correct or incorrect. Sorry to be so direct but I need
to have this point clear.
Thanks

[Luke Zhang [MSFT]]

There is no any document said that VSTO 2005 extended objects can be
implement in a Test project so far. I am not sure if this will be improved
later. Currently, I would like recommend automation instead.

Luke

[Jay B. Harlow [MVP - Outlook]]

Enrique,
PMFJI: I believe I follow what you are attempting to do, however I'm not
sure you can. (actually I suspect one can, but I'm not sure at what "pain"
cost ;-))

I suspect its a chicken & egg problem: You need Excel to start the VSTO
project, but you need a Test Project to start Excel, while the Test Project
really wants to create the VSTO object... Unfortunately the
Excel.Application you get when starting Excel is not the same as the one
internally used by the VSTO project. I understand the one internal to the
VSTO project is actually an aggregated object, the real Excel.Application
object is hidden under the veneer of VSTO's Excel Application object...

Somehow you need to get the Excel.Application object inside the VSTO
project, outside to the Test Project. Correct?

I'm not sure how one would do that. <thinking out loud>One thought I had is
to let the Test Project start your Excel VSTO project as you have been, then
have your Excel VSTO project hand back what the Test Project needs via some
"backdoor" such as remoting or something. But I don't have a "warm &
comfortable" feeling on what could be used so all parties involved remain
happy & friendly...</thinking out loud>
Have you tried asking "down the hall" in the
microsoft.public.vsnet.vstools.office newsgroup?
BTW: I would be curious if you find a way.

--
Hope this helps
Jay [MVP - Outlook]
..NET Application Architect, Enthusiast, & Evangelist
T.S. Bradley - http://www.tsbradley.net
"Enrique" <en*****@nospam.nospam> wrote in message
news:9F**********************************@microsof t.com...
|
|
| "Luke Zhang [MSFT]" wrote:
|
| > We cannot access extended excel object with automation also. What I
means
| > is open the Excel document with automation and test its properties in a
| > Unit Test.
| >
| > Luke
| >
| >
| Luke, please answer this: Can I access VSTO 2005 extended objects from a
| Visual Studio Test Project? If not, this means that Visual Studio Testing
| Tools can not be used for fully testing VSTO 2005 projects with extended
| excel objects because there is no way tests methods can access extended
excel
| objects.
|
| Please say if this is correct or incorrect. Sorry to be so direct but I
need
| to have this point clear.
| Thanks
|

[Enrique]

"Jay B. Harlow [MVP - Outlook]" wrote:

Enrique,
PMFJI: I believe I follow what you are attempting to do, however I'm not
sure you can. (actually I suspect one can, but I'm not sure at what "pain"
cost ;-))

I suspect its a chicken & egg problem: You need Excel to start the VSTO
project, but you need a Test Project to start Excel, while the Test Project
really wants to create the VSTO object... Unfortunately the
Excel.Application you get when starting Excel is not the same as the one
internally used by the VSTO project. I understand the one internal to the
VSTO project is actually an aggregated object, the real Excel.Application
object is hidden under the veneer of VSTO's Excel Application object...

Somehow you need to get the Excel.Application object inside the VSTO
project, outside to the Test Project. Correct?

I'm not sure how one would do that. <thinking out loud>One thought I had is
to let the Test Project start your Excel VSTO project as you have been, then
have your Excel VSTO project hand back what the Test Project needs via some
"backdoor" such as remoting or something. But I don't have a "warm &
comfortable" feeling on what could be used so all parties involved remain
happy & friendly...</thinking out loud>
Have you tried asking "down the hall" in the
microsoft.public.vsnet.vstools.office newsgroup?
BTW: I would be curious if you find a way.

--
Hope this helps
Jay [MVP - Outlook]
..NET Application Architect, Enthusiast, & Evangelist
T.S. Bradley - http://www.tsbradley.net
"Enrique" <en*****@nospam.nospam> wrote in message
news:9F**********************************@microsof t.com...
|
|
| "Luke Zhang [MSFT]" wrote:
|
| > We cannot access extended excel object with automation also. What I
means
| > is open the Excel document with automation and test its properties in a
| > Unit Test.
| >
| > Luke
| >
| >
| Luke, please answer this: Can I access VSTO 2005 extended objects from a
| Visual Studio Test Project? If not, this means that Visual Studio Testing
| Tools can not be used for fully testing VSTO 2005 projects with extended
| excel objects because there is no way tests methods can access extended
excel
| objects.
|
| Please say if this is correct or incorrect. Sorry to be so direct but I
need
| to have this point clear.
| Thanks
|

Jay: thank you very much for jumping in.

I have had this chicken and egg feeling several times in this topic. I
managed to get access from an Test Project to the excel instance that VSTO is
using. There is a key element here: VSTO extended excel objects are created
“on demand� and VSTO does not like to create its objects while the target
workbook is opened (it stops working with a long message requesting to shut
down excel).

This is what I did to get access to excel objects via automation from the
test project:

1. Open VSTO project.

2. Open any extended excel SheetX or Thisworkbook object on design view.
This will force VSTO to instantiate excel and create the extended excel VSTO
object you are opening.

3. Run your Test Project and get access to the VSTO excel instance by using:
Dim xlApp = DirectCast(GetObject(, "Excel.Application"), excel.application)

At this point, I have access to all excel objects that VSTO is using but to
NONE (0) extended excel objects.

If you look at the code that Visual Studio creates for you for unit testing:

Dim RuntimeCallback As
Global.Microsoft.VisualStudio.Tools.Applications.R untime.IRuntimeServiceProvider = 'TODO: Initialize to an appropriate value

Dim target As Global.ExcelTestWorkbook.ThisWorkbook = New
Global.ExcelTestWorkbook.ThisWorkbook(RuntimeCallb ack)

You could think that you should get access to the extended workbook
including all extended excel objects and “normal� excel inner objects. This
is not happening!

In summary I need from a Test Project:

1. Access to VSTO project. Status: ok, working with RunTimeCallback.
2. Access to excel objects. Status: ok, working with automation as described
above.
3. Access to extended excel VSTO objects. Status: Dead, not working.

Conclusion up to now and hopping it is wrong:

You can not use Visual Studio Unit Test tools for testing VSTO projects
because there is not way to access extended excel objects from a Test
Project. The solution is to get a THIRD PARTY UNIT TEST TOOL.

What a pity, I already got rid of my THIRD party unit test tool and I will
have to install it again!

If you find a way to use VSTO 2005 and Visual Studio testing tools, please
let me know.

I will post this note in the group news you suggested.

Thank you very much

Enrique