I have noticed a behavior that I hope is not intentional.< xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />
If a user of my Excel VSTO project deletes one of the sheets that is referred to by one of the managed sheet classes (ie Class Sheet1), the next time the application is started this class picks a new sheet to bind itself to. I hope I am explaining this clearly.
Here are the steps to reproduce.
Create a new Excel VSTO project.
Run the Excel app.
*Delete one of the ‘managed sheets’ (I will use Sheet1) from the workbook.
*Add a new sheet to the workbook.
Save and close the Excel app.
Restart the Excel app – Setting a breakpoint inside the Sheet1 startup event shows that the Sheet1 class is now bound to this new sheet.
* These steps can be swapped!
I would think that if a user deleted one of the ‘managed sheets’ that the code would no longer execute.
At very least it could throw an error, but it should not just pick a new sheet to bind itself to.
Please let me know if I have gone astray somewhere.
Thanks in advance for your thoughts.
--MikeH
Problem deleting excel sheets
Slava Imeshev
I am not certain I understand what you mean by the VSTO runtime randonly picking up another sheet to bind to.
If you create a VSTO excel solution that has 3 sheets in it and there is code behind these sheets, how is the runtime to decide whether the code behind a given sheet is critical to the application or not and should it let the application function even though the underlying Excel control could not be attached to
If you wish to allow sheets to be deleted at runtime, you need to not create VSTO classes for those sheets. Create the Excel solution with only the Workbook in it and have all your code there. This will allow the user to add or remove sheets as required without causing any exceptions.
Thanks
Mohit Gupta.
prasanna1579
I guess I should have added that I am using the Visual Studio 2005 Team Suite beta 2 version.
Michael Hinkel
I have repro'd this in the Proposed RTM Build as well
Rgds
Marc Konchar
My application starts with a clean sheet and lets the customer build from there.
There isnt anything I can really check -- "is this my managed sheet "
Thanks for your thoughts.
--MikeH
bashok
Thanks for the details. I have been able to repro your scenario and am looking into it further. It certainly is not what was intended and we are investigating this further.
Thanks
Mohit.
Plenty
In looking through the generated code, I actually think the loader is using reflection to construct all of the classes of a certain type no matter what. I would advise you have some code in the startup event to validate whether the sheet exists as you expect and use this as a flag
Regards
CindyG
As said before I am running the proposed RTM build and this is still happening will be interested to hear any feedback from the team on this but this is how it is at the moment :(
Regards
asdfj
Thanks for the reply.
Unfortunately, this doesn't quite work for me. I need to know in the start up event, but this code example always evaluates false within the start up event.
My problem is that I have code in the start up events, and this would be happening for the wrong sheets -- after the delete scenario described above.
I think I have found a way to remove the code from my start up events, but keep the same functionallity.
So, I think there is still something broken with the way this scenario plays out, but I have at least found a solution to my immediate problem. I hope the MS developers can find the answer in time to get it into the RTM.
By the way, just in case they are reading -- I bought the VSTO book by Eric Carter and Eric Lippert. It is definitely worth picking up if anyone plans on doing VSTO development.
--MikeH
Amy Dullard - MSFT
On the Application object you can use
if
(Application.ActiveSheet == this)MessageBox.Show("Active Sheet is me !");
Juozas Kimtys
Thanks again for looking into it.
JarleS
Hey, Mohit
Thanks for the response.
Mike,
I am not certain I understand what you mean by the VSTO runtime randonly picking up another sheet to bind to.
As I said in my first post:
Here are the steps to reproduce.< xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />
Create a new Excel VSTO project.
Run the Excel app.
*Delete one of the ‘managed sheets’ (I will use Sheet1) from the workbook.
*Add a new sheet to the workbook.
Save and close the Excel app.
Restart the Excel app – Setting a breakpoint inside the Sheet1 startup event shows that the Sheet1 class is now bound to this new sheet.
At start up time, the sheet that Sheet1 was using is no longer found.
"What should it do at this point "
a) Fail miserably
b)Forget about Sheet1 and move on.
c)Pick some other sheet for Sheet1 to bind to.
Currently it is doing choice 'c'. This has been confirmed by others as you will notice in the above posts.
I would like it to do 'b' - although I realize this is probably asking for too much.
As you said "should it let the application function". Okay -- I can buy this answer. Things are screwed up, we cant find our sheets, lets display an error message and shut down -- choice 'a'.
However, choosing to bind to some other sheet, choice 'c', is the absolute worst answer available.
I honestly believe this if this is by design, it is a terrible design decision -- a show stopper that basically makes VSTO an unusable product. If it was just an oversight of an untested behavior, all is forgiven -- just fix it.
--MikeH
Animas
It turns out that this behavior is because of a bug in Office. Let me explain this in a little more detail to clarify.
Create a new workbook in Excel. Now, delete Sheet3 from it. Then insert a new worksheet. Notice that it is called Sheet4.
Now create a new workbook in Excel and add an OLEObject to it's OLEOBjects collection. Perform the same insert and delete again. Notice that the new sheet is called Sheet3 and not Sheet4. The naming algorithm re-uses the name of the sheet that just got deleted in this case.
The latter scenario is what is causing the behavior that you are experiencing with VSTO customized workbooks. To support the customization of the workbook, VSTO adds an OLEObject to the document. This causes the above bug to get exposed. Sheets in the code are bound to the native Excel objects using the names of the sheets. When the code starts up after the save in the scenario, it still is able to find a sheet called "Sheet3" and therefore binds to it.
I hope that clarifies this wierd behavior to some extent. We will be working with the Office team on this bug. We will also be looking for a workaround and will let you know if we find one.
Thanks
Mohit Gupta.
tfieldho
I have repro'd your scenario and am with you on this as it is an odd behaviour, in adding code to sheet2 and then deleting so there is only one sheet seems to still fire the event which am sure is not quite right, I haven't dug much further into this but presume this is a hook into Excel scenario that is a bit of catch.
I will see whether I can find out why this happens, someone from the dev team might have further information on why this is the behaviour.
Regards
Brian Jimdar2
However, I did find a bug report where someone else reported issues with deleting sheets from the workbook.
http://lab.msdn.microsoft.com/productfeedback/viewfeedback.aspx feedbackid=7e869f25-913e-4a11-b799-e25c071e77b1
I find it absolutely unacceptable to say that the answer is not deleting sheets.
VSTO is a product that is meant to add functionality to Excel, yet the answer in the bug report basically says that you can no longer use the delete sheet functionality.
When VSTO does its reflection and the sheet that Class.Sheet1 was bound to is no longer there, it certainly should not randomly pick another sheet to bind to.
This is a problem with VSTO, and the VSTO team should own up to it, fix it, and have a better product for it.