none
Problem when automating a VSTO Excel workbook from a Windows Forms project RRS feed

  • Question

  • Hi,

    Here's my development environment:

    • VS2008 SP1
    • Office 2007 SP3
    • .NET Framework SP1
    • Windows XP SP3

    I have a Windows Forms project that creates an Excel workbook from a template and dumps some data into it while adding a few worksheets. The Excel template has a VSTO customization of mine. The code is almost OK, because I'm getting the following COMException:

    Call was rejected by callee. (Exception from HRESULT: 0x80010001 (RPC_E_CALL_REJECTED))

    If I remove the VSTO customization from the template, the code works OK. If I leave it, sometimes it works and sometimes it doesn't. Some things to note:

    • I'm not using asynchronous operations nor parallel programming. Code is sequential.
    • I've tried releasing all Excel objects through Marshal.ReleaseCOMObject (very cumbersome).
    • The Excel object model is accessed through reflection.

    Any help is greatly appreciated,

    Carlos Mallen

    Tuesday, August 21, 2012 8:28 PM

Answers

  • Hi Carlos

    My best guess would be that your Windows Forms project is running butting heads with the VSTO customization - both trying to perform tasks in the workbook at the same time. Or maybe the VSTO workbook is monitoring some events that your code is triggering? There are other ways...

    One approach could be, as you suggest, to add the customization after your Windows Form project has completed. This would not have to be done by the user, it could also be done by your Windows Forms project. It would mean closing the Excel file, then opening it again, no matter whether you use the ServerDocument functionality (http://msdn.microsoft.com/en-us/library/bb772073(VS.90).aspx) or add the custom properties(http://msdn.microsoft.com/en-us/library/ds87aeyf(VS.90).aspx).

    The other way you could go would be to not use the "interop" to generate and modify the workbook, but instead work directly with the Office Open XML file, before opening it in Excel. Execution would probably be faster and you'd certainly not run into any conflicts with the Excel application since all the work would be done on the closed file.


    Cindy Meister, VSTO/Word MVP

    • Marked as answer by Carlos Mallen Thursday, August 23, 2012 7:58 PM
    Thursday, August 23, 2012 9:51 AM
    Moderator

All replies

  • Unfortunately you can get that error for any number of reasons. It often just means the application's busy. For instance (and I know this probably isn't your specific problem) it occurs if you try and access a workbook while the 'SaveAs' dialog is up.

    Honestly, the best thing may be to hammer it. So, identify the line the program crashes on, and then:

    bool fail;
    
    do
    
    {
        fail = false;
    
        try 
        { 
            // problem line 
        }
        catch(Exception ec)
        { 
            // Ensure error is RPC_E_CALL_REJECTED, or RPC_E_SERVERCALL_RETRYLATER, or I think there's a literal 'Object busy' one too
            fail = true;
        }
    
        System.Threading.Thread.Sleep(200); // Wait 1/5th second before retrying
    }
    while(fail)

    I know it's not ideal, but, this happens.


    • Edited by JosephFox Wednesday, August 22, 2012 8:47 PM
    Wednesday, August 22, 2012 8:33 PM
  • Hi JosephFox,

    Thanks for your time.

    I already tried that solution (forgot to tell), catching the COMException, sleeping for a while and then attempting to do the task again. With this approach things did improve a bit, but the program eventually raised the same exception. Unfortunately the error didn't occurred at a specific line, which makes sense, since Excel might be busy at any point in time. Every time I used the debugger to identify the line, things worked OK. I also used Debug.Print statements to identify the line, but then another line raised an exception, so I finally wrapped all the procedure inside a try-catch block.

    Sometimes the exception was thrown when creating a new Excel application for the first time (meaning no other instances of Excel were running on the machine). Another problem was that if an exception occurred, the workbook was left there incomplete and couldn't close it because Excel was busy. This lead to a dirty application that sometimes created several useless workbooks.

    I finally decided that this erratic behaviour wasn't acceptable. The only solution that comes to my mind at the moment, is removing the VSTO customization from the template, creating the workbook, and adding the VSTO customization to the template after this.

    Since the new workbook won't have the VSTO customization, users need to add it. They are well trained in adding/removing VSTO customizations and I have projects dedicated to such tasks. They will have no problem in doing so.

    Kind regards,

    Carlos Mallen


    Wednesday, August 22, 2012 9:18 PM
  • Carlos,

    What a pain. I hope Microsoft take note of your thread (Microsoft staff do monitor these forums).

    When you did try/catch/repeat, did you only try and repeat once? The last thread I remember about this kind of thing, the developer said it typically took 4 or 5 iterations.

    Maybe you could try several try/catch blocks. On the other hand, maybe its not worth it, if you have competent users (I wish I had competent users! My users literally cannot find a folder given a file path).

    I hope your future tasks are simpler,

    Joe


    • Edited by JosephFox Wednesday, August 22, 2012 9:32 PM
    Wednesday, August 22, 2012 9:31 PM
  • Hi JosephFox,

    Yes, I used an approach very similar to what you posted first: a stubborn do-while loop that tried to do the task again and again until it was successful, as long as a COMException was raised. If a COMException occurred, it waited 2 seconds before trying again. But as I said above, the application was dirty and the code became more complex without adding a clear benefit.

    Users will be able to cope with this issue. I prefer this solution.

    Cheers,

    Carlos Mallen

    Wednesday, August 22, 2012 10:16 PM
  • Hi Carlos

    My best guess would be that your Windows Forms project is running butting heads with the VSTO customization - both trying to perform tasks in the workbook at the same time. Or maybe the VSTO workbook is monitoring some events that your code is triggering? There are other ways...

    One approach could be, as you suggest, to add the customization after your Windows Form project has completed. This would not have to be done by the user, it could also be done by your Windows Forms project. It would mean closing the Excel file, then opening it again, no matter whether you use the ServerDocument functionality (http://msdn.microsoft.com/en-us/library/bb772073(VS.90).aspx) or add the custom properties(http://msdn.microsoft.com/en-us/library/ds87aeyf(VS.90).aspx).

    The other way you could go would be to not use the "interop" to generate and modify the workbook, but instead work directly with the Office Open XML file, before opening it in Excel. Execution would probably be faster and you'd certainly not run into any conflicts with the Excel application since all the work would be done on the closed file.


    Cindy Meister, VSTO/Word MVP

    • Marked as answer by Carlos Mallen Thursday, August 23, 2012 7:58 PM
    Thursday, August 23, 2012 9:51 AM
    Moderator
  • Hi Cindy,

    Thanks for showing interest in this thread.

    If my understanding is correct, what you propose requires to ask for a file name or having a predefined save location for the new workbooks. I agree this is an option, but need to think about how do we create/use our financial models.

    It'd also have the side effect of having extra workbooks stored somewhere in the user's machine or the server, even though the user might not really want to keep them (think of a workbook created with the wrong parameters or one created just to have a rough idea of how numbers look like). Another option would be to always dump the data in a specified workbook, but again, if the user wants to compare 2 workbooks, she'd have to rename or save one with a different name so it's not overwritten. What if these workbooks are temporal? What if she accidentally overwrites the workbook she really wanted to keep? Who'll be in charge of keeping the save location clean?

    We're a small team, so I asked users what they think about having to add the VSTO customization themselves. They said they're OK with this. As I said before, they're well trained. One of the first things they MUST learn when they arrive, is to do this task. It's natural for them to do so.

    Moving to the core of my post. Is this a bug/limitation of VSTO? Are VSTO documents not candidates for automation? Is it necessary to always use a workaround?

    Kind regards,

    Carlos Mallen

    Thursday, August 23, 2012 4:27 PM
  • Hi Carlos

    <<Moving to the core of my post. Is this a bug/limitation of VSTO? Are VSTO documents not candidates for automation? Is it necessary to always use a workaround?>>

    I think it really depends very much on what kind of automation you want/need to perform and what the functionality of the VSTO document is.

    <<If my understanding is correct, what you propose requires to ask for a file name or having a predefined save location for the new workbooks.>>

    I don't see why? I think maybe we're understanding what I suggested differently - not on the same wave-length.


    Cindy Meister, VSTO/Word MVP

    Thursday, August 23, 2012 4:43 PM
    Moderator
  • Hi Cindy,

    I feel curious at least with respect to the kind of automation I'm doing. Do I always have to use a workaround? I really expected to create a workbook with the VSTO customization added. It'd have been nice. This much more rich Windows Forms project is meant to replace an Access database that dumped data into the template. I never had problems with it and now I'm surprised that the much more powerful .NET Framework has these problems.

    Regarding your suggestions, sorry for the misunderstanding. I'll take the option of manipulating the document properties. In this case the user still needs to close and open the workbook for the customization to show, doesn't she? I made a few tests and adding the properties while the workbook is open has no visible effect. The link you posted says "...when a user opens a document..." and if these properties exist, the VSTO runtime starts the customization. This behavior is acceptable.

    Where can I get the SolutionID at design time? Will it be forever the same? Can I get it using the ServerDocument class or something else? I don't want to hard code it in my application.

    Thanks for your time,

    Carlos Mallen


    • Edited by Carlos Mallen Thursday, August 23, 2012 9:49 PM Comment about Access database.
    Thursday, August 23, 2012 7:57 PM