locked
Alternatives to USysRibbons RRS feed

  • Question

  • We are working on a project that will be using Access to connect to SharePoint. One of the requirements we have will require a rather extensive batch update to several hundred rows in SharePoint. We would like to make the call to execute this batch update called from a Ribbon control within Access. We have demonstrated that we can use USysRibbons to call a macro and call a SharePoint page using WinHttp.WinHttpRequest that would kick off our batch edits on the server. However, I am worried about HTTP timeouts since this will likely be a long running process. As such, I would like to ask others if they know of an alternative approach to adding a Ribbon control to Access that could execute some .Net code, ran within Access, to do the batch update using one of SharePoint's client side HTTP API's (most likely REST or CSOM)?

    I see that Visual Studio has Add-in templates for Excel, Word, etc. but not for Access. Would it be possible to tweak one of those projects to use in Access? 

    Apologies if this is a remedial question, web searches haven't been very helpful on this regarding Access.

    Tuesday, March 18, 2014 9:34 PM

Answers

  • Hi,

    >> I would like to ask others if they know of an alternative approach to adding a Ribbon control to Access that could execute some .Net code, ran within Access, to do the batch update using one of SharePoint's client side HTTP API's (most likely REST or CSOM)?<<

    We can create a com add-in to execute the managed code and custom the ribbon by implementing IRibbonExtensibility interface. I also write a demo for your reference and you can download this project from here.

    using Microsoft.Office.Core;
    
    using System;
    
    using System.Collections.Generic;
    
    using System.Linq;
    
    using System.Runtime.InteropServices;
    
    using System.Text;
    
    using System.Threading.Tasks;
    
    using System.Windows.Forms;
    
    
    namespace Access2013AddIn
    
    {
    
       
    
        [GuidAttribute("E4F7953D-6268-4896-8D16-AF313EDB8718"),
    
        ProgId("Access2013AddIn.Connect")]
    
        public class Connect : Object, Extensibility.IDTExtensibility2, IRibbonExtensibility
    
        {
    
            public void OnAddInsUpdate(ref Array custom)
    
            {
    
               
    
            }
    
    
            public void OnBeginShutdown(ref Array custom)
    
            {
    
                
    
            }
    
    
            public void OnConnection(object Application, Extensibility.ext_ConnectMode ConnectMode, object AddInInst, ref Array custom)
    
            {
    
                MessageBox.Show("AddIn Loaded Successed");
    
            }
    
    
            public void OnDisconnection(Extensibility.ext_DisconnectMode RemoveMode, ref Array custom)
    
            {
    
                
    
            }
    
    
            public void OnStartupComplete(ref Array custom)
    
            {
    
                
    
            }
    
    
            public string GetCustomUI(string RibbonID)
    
            {
    
               
    
                string ribbonXML="";
    
                ribbonXML+="<mso:customUI xmlns:mso=\"http://schemas.microsoft.com/office/2009/07/customui\">";
    
                  ribbonXML+="<mso:ribbon>";
    
                    ribbonXML+="<mso:qat/>";
    
                    ribbonXML+="<mso:tabs>";
    
                      ribbonXML+="<mso:tab idQ=\"mso:TabHomeAccess\">";
    
                        ribbonXML+="<mso:group id=\"Group1\" label=\"MyGroup\" autoScale=\"true\">";
    
                          ribbonXML+="<mso:button id=\"myButton1\" label=\"MyButton1\" visible=\"true\" onAction=\"OnButtonClick\" />";
    
                        ribbonXML+="</mso:group>";
    
                      ribbonXML+="</mso:tab>";
    
                    ribbonXML+="</mso:tabs>";
    
                  ribbonXML+="</mso:ribbon>";
    
                ribbonXML+="</mso:customUI>";
    
                return ribbonXML;
    
              
    
            }
    
    
            public void OnButtonClick(IRibbonControl control)
    
            {
    
                MessageBox.Show("Ribbon Button Clicked");
    
            }
    
        }
    
    }
    

    You can get more detail about com add-In from article below:
    How to build an Office COM add-in by using Visual C# .NET

    And below is link is about creating com add-in for OneNote:
    How to develop an OneNote 2010 ribbon add-in application

    If you have any question about SharePoint, you can get more effective response from SharePoint 2013 - Development and Programming.

    Best regards

    Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by lgoolsby Thursday, March 27, 2014 8:45 PM
    Wednesday, March 19, 2014 12:30 PM

All replies

  • Hi,

    >> I would like to ask others if they know of an alternative approach to adding a Ribbon control to Access that could execute some .Net code, ran within Access, to do the batch update using one of SharePoint's client side HTTP API's (most likely REST or CSOM)?<<

    We can create a com add-in to execute the managed code and custom the ribbon by implementing IRibbonExtensibility interface. I also write a demo for your reference and you can download this project from here.

    using Microsoft.Office.Core;
    
    using System;
    
    using System.Collections.Generic;
    
    using System.Linq;
    
    using System.Runtime.InteropServices;
    
    using System.Text;
    
    using System.Threading.Tasks;
    
    using System.Windows.Forms;
    
    
    namespace Access2013AddIn
    
    {
    
       
    
        [GuidAttribute("E4F7953D-6268-4896-8D16-AF313EDB8718"),
    
        ProgId("Access2013AddIn.Connect")]
    
        public class Connect : Object, Extensibility.IDTExtensibility2, IRibbonExtensibility
    
        {
    
            public void OnAddInsUpdate(ref Array custom)
    
            {
    
               
    
            }
    
    
            public void OnBeginShutdown(ref Array custom)
    
            {
    
                
    
            }
    
    
            public void OnConnection(object Application, Extensibility.ext_ConnectMode ConnectMode, object AddInInst, ref Array custom)
    
            {
    
                MessageBox.Show("AddIn Loaded Successed");
    
            }
    
    
            public void OnDisconnection(Extensibility.ext_DisconnectMode RemoveMode, ref Array custom)
    
            {
    
                
    
            }
    
    
            public void OnStartupComplete(ref Array custom)
    
            {
    
                
    
            }
    
    
            public string GetCustomUI(string RibbonID)
    
            {
    
               
    
                string ribbonXML="";
    
                ribbonXML+="<mso:customUI xmlns:mso=\"http://schemas.microsoft.com/office/2009/07/customui\">";
    
                  ribbonXML+="<mso:ribbon>";
    
                    ribbonXML+="<mso:qat/>";
    
                    ribbonXML+="<mso:tabs>";
    
                      ribbonXML+="<mso:tab idQ=\"mso:TabHomeAccess\">";
    
                        ribbonXML+="<mso:group id=\"Group1\" label=\"MyGroup\" autoScale=\"true\">";
    
                          ribbonXML+="<mso:button id=\"myButton1\" label=\"MyButton1\" visible=\"true\" onAction=\"OnButtonClick\" />";
    
                        ribbonXML+="</mso:group>";
    
                      ribbonXML+="</mso:tab>";
    
                    ribbonXML+="</mso:tabs>";
    
                  ribbonXML+="</mso:ribbon>";
    
                ribbonXML+="</mso:customUI>";
    
                return ribbonXML;
    
              
    
            }
    
    
            public void OnButtonClick(IRibbonControl control)
    
            {
    
                MessageBox.Show("Ribbon Button Clicked");
    
            }
    
        }
    
    }
    

    You can get more detail about com add-In from article below:
    How to build an Office COM add-in by using Visual C# .NET

    And below is link is about creating com add-in for OneNote:
    How to develop an OneNote 2010 ribbon add-in application

    If you have any question about SharePoint, you can get more effective response from SharePoint 2013 - Development and Programming.

    Best regards

    Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by lgoolsby Thursday, March 27, 2014 8:45 PM
    Wednesday, March 19, 2014 12:30 PM
  • You have two questions here.

    One is you seem to wanting to much avoid the use of the USYSRibbons table, why is this?

    You have to build + place the XML some place and it might as well go in Access.

    That custom ribbon code can call VBA directly which in turn can call any .net com add-in.

    Simply have your on-Action in the ribbon call some VBA code which then calls a .net com object.

    So you ribbon XML button in the USYSRibbon table looks like this:

      <button id="MyShareExport" label="Export Data"
            imageMso="ExportAccess" size="large"
            onAction="=MyVBAExport()"
       />

    The above thus on-Action setting calls your VBA.

    Public Function MyVBAExport()
    
       ‘ code here to use the .net com object
    
       DIM mySPobject   as new MyNetObjectImade
    
       mySPobject.ExportdataMethod
    
    End Function

    So creating a simple COM object in Visual Stuido also works very well.

    The following VB.net code is a base class object and works fine FROM Access as a COM object.

    Imports System.Runtime.InteropServices
    
    Public Class Albert1
    
         Public Function HelloWorld() As String
    
            HelloWorld = "Hello Albert"
    
        End Function
    
    End Class

    The above is ALL you need!

    Now, when you compile, simple ensure you click the box called:

    [x] Make assembly COM-Visible

    And then for compile options make sure this:

    So two mouse clicks, and the above object will now be seen in the Access VBA editor if you set a reference using VBA ->tools->references.

    The Access VBA code to run above is this:

          Dim t       As New AlbertVB1.Albert1

           Debug.Print t.HelloWorld

    (you need to set a reference in VBA editors (Tools->references).

    So I don’t see any problem having a few lines of XML to add a button to the ribbon (or even simple a button on Access form if you have some “big” issues against the USYSRibbon table).

    So a simple COM add in works well with a few lines of VBA. The poster above has kindly posted a add-in example.

    So between the two, you have lots to go on. With a simple COM object approach, you don't really need a add-in. However the add-in might be perffered since you likely can get .net to load the app.config settings somewhat easier.

    Best regards,

    Albert D. Kallal (Access MVP)

    Edmonton, Alberta Canada

    Friday, March 21, 2014 3:26 AM
  • The main reason for wanting to keep out of USysRibbons is deployability. We will be pushing this add-on to multiple users and pushing updates (which the COM add-in would solve for nicely) and many of the users that will be consuming this add-in are not what you would consider Access power users. Explaining to them how setup the table could potentially (almost certainly will) be a major hassle. If we could setup the add-in to deploy updates using ClickOnce similar to this blog post then that would be ideal. We could potentially push the COM component with ClickOnce but would still have the initial setup overhead of the USysRibbon table.
    Friday, March 21, 2014 1:48 PM
  • Right, but I assumed the code would create the table, not the users.

    However if you going with an add-in then I guess you really don’t need the USYSRibbon table anyway. I think the  choice is add-in vs com object.

    And note that from VBA you can use the VBA command loadcustomUI “your XML goes here”.

    So a ribbon load can be done in VBA code – you don’t necessary have to use the ribbon table nor  place the XML in the ribbon table.

    Best regards,

    Albert D. Kallal (Access MVP)

    Edmonton, Alberta Canada

    Friday, March 21, 2014 5:18 PM
  • In addition, we can deploy the ClickOnce Application using ClickOnce. A ClickOnce application is any Windows Presentation Foundation, Windows Forms, or console application published using ClickOnce technology( refer to ClickOnce Deployment Overview ).

    So I am afraid we can't deploy the Com-addin via ClickOnce now. Also below article may be helpful:

    Automatic update for Windows installer

    And if you have any questions about ClickOnce or Windows Installer, you can post it to ClickOnce and Setup & Deployment Projects to get more effective response.

    Best regards

    Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, March 24, 2014 8:35 AM