none
Whether to use VSTO Addin, VSTO Document or VBA code RRS feed

  • Question

  • I have an environment where there are several deployment challenges (versions of Office (now 2007 and 2010), and security restrictions) which make it difficult to implement certain Excel code. The basic scenario is:

      • There are some Excel workbooks that users open from a web application. There is a fair amount of server code that populates a workbooks when it is requested, and this is unlikely to change, so the workbooks are necessarily opened via an intranet url.
      • The user should be able to modify data in the workbook, then click a custom button that calls a web service to save parts of the data. Again, this web service is unlikely to change

      This has all been working, using VBA code in the workbook, and making the web service calls with MSOSOAPLib30.SoapClient30. However, we don't seem to be able to get Office 2010 to consistently retain the reference to MSOSOAPLib30.SoapClient30. So, I'm looking at rewriting the Excel code using VSTO, but my initial tests using Addins or Documents have thrown up some issues:

      • If the code is developed as a VSTO Document, I'm not sure if it will be possible to permit the code to run, as the document is being opened from a network locations. Setting a Trusted location should be a start, but then the code fails with a .Net SecurityException, as it is being loaded from a network location.
      • If the code is developed as a VSTO Addin, then I expect it will be considered to be running code that is local, so I shouldn't get the security issues, but Addins seem to work at the application level, rather than the document level, and I can't see an event that notify the addin when a document is loaded (I need to run some code when the document is loaded), so I'm not sure if an Addin is suitable

      So, any suggestions which approach to try and follow ? My choices seem to be:

      1. Continue the VSTO document route, and hope to find ways to address the security issues
      2. Find a way that a VSTO addin can run code on the load of a document (that is loaded from a network location)
      3. Have the code that calls the web service in a VSTO addin, and have this called from VBA code in the workbook (I believe this is possible - e.g. http://social.msdn.microsoft.com/Forums/en-US/vsto/thread/29a1cade-929d-4941-9202-e2043a6f984d
      4. Retain VBA code, and find a reliable way for this code to call a web service in Office 2007 and Office 2010

    Microsoft CRM MVP - http://mscrmuk.blogspot.com/ http://www.excitation.co.uk

    Thursday, September 6, 2012 2:35 PM

Answers

  • Hi David

    FWIW I think the better place to get a discussion going on your very interesting question would be the Excel for Developers forum. There are many more Excel specialists there, and I'll bet some of them have gone through similar processes as you describe.

    If you'd like me to move it for you, I'd be glad to do so.

    I'm not an Internet person, myself, so my understanding of your scenario is mainly on a theoretic basis. I believe that opening a VSTO document from the Internet should be possible - I think I read about it here in the forum once, but I can't swear to it. And as I recall it wasn't the simplest way to do things :-)

    The Add-in approach should work. Excel has a Open and Activate events that would let the code check for a document property or something in order to provide/hide the necessary UI.

    The VBA approach you suggest can also work, especially if you want to leverage existing code that prepares information for the web service or processes the in-coming data.


    Cindy Meister, VSTO/Word MVP, my blog

    Tuesday, September 11, 2012 11:20 AM
    Moderator
  • Calling add-in code from VBA is completely possible - Walkthrough: Calling Code in an Applicaiton-Level Add-in from VBA.  

    Norm Estabrook

    Tuesday, September 11, 2012 8:47 PM
    Answerer

All replies

  • I've got a feeling that VBA is much more convenient than Add-in.

    I'm learning to develop Add-in, sometimes after I compiled the code I cannot see it on the Add-in list but found it in the disabled items list.
    Monday, September 10, 2012 5:44 PM
  • Hi David

    FWIW I think the better place to get a discussion going on your very interesting question would be the Excel for Developers forum. There are many more Excel specialists there, and I'll bet some of them have gone through similar processes as you describe.

    If you'd like me to move it for you, I'd be glad to do so.

    I'm not an Internet person, myself, so my understanding of your scenario is mainly on a theoretic basis. I believe that opening a VSTO document from the Internet should be possible - I think I read about it here in the forum once, but I can't swear to it. And as I recall it wasn't the simplest way to do things :-)

    The Add-in approach should work. Excel has a Open and Activate events that would let the code check for a document property or something in order to provide/hide the necessary UI.

    The VBA approach you suggest can also work, especially if you want to leverage existing code that prepares information for the web service or processes the in-coming data.


    Cindy Meister, VSTO/Word MVP, my blog

    Tuesday, September 11, 2012 11:20 AM
    Moderator
  • Calling add-in code from VBA is completely possible - Walkthrough: Calling Code in an Applicaiton-Level Add-in from VBA.  

    Norm Estabrook

    Tuesday, September 11, 2012 8:47 PM
    Answerer