OPEN EXCEL WORKBOOK FROM ACCESS CUSTOM UI RIBBON XML

Question

I am trying to use objects in my custom UI ribbon in Access 2010 to open an Excel Workbook which I have data linked to the database.  I created the Sub(not sure if it needs to be sub or function) pasted below in a Module in VBA and I've listed 'OpenScheduleWorkBook' as the OnAction string in the Ribbon XML code, but it does seem to find the Sub.  Can someone tell me if this code should work to open an existing workbook?  I know it seems like a simple task, but I am not exactly a code expert.-Thanks, JS.

SUB OpenScheduleWorkbook (ByVal control as IRibbonControl)
Const strTemplate = "Z:\DASHBOARD\SCHEDULE\MASTER SCHEDULE.xlsx"
    Dim objXl As Object
    Dim objWb As Object
    Dim objWs As Object
    On Error GoTo ErrHandler
    ' Start Excel
End sub

Below is the XML I'm attempting to use to run the Sub from above:

<button id="SchedForm" imageMso="FilePrint"
size="large" label="SCHEDULE WORKBOOK" screentip="UPDATE SCHEDULE"
onAction="OpenScheduleWorkbook" />

Answer 1

Hi, I think you need to add an equal sign to the front of the function name in the onAction callback. For example, onAction="=SubName". Hope it helps...

Answer 2

A few tricks can really shorting this code. Don’t use a ribbon call back, but simply place the “function” name you want to call in the on-Action of the Ribbon.

So your ribbon code can look like this:

<customUI xmlns="http://schemas.microsoft.com/office/2006/01/customui">
  <ribbon startFromScratch="false">
    <tabs>
     <tab id="MyExcel" label="Show Master Schedule">

        <group id="ShowDoc" label="Master Schedule">
          <button id="Sexcel" label="Master Excel" 
                   imageMso="ExportExcel" size="large"
                   onAction="=ShowExcelDoc()"/>
        </group>

       </tab>

    </tabs>
  </ribbon>
</customUI>

Now, place a function in a standard code module (or even in the form that you viewing when you hit the ribbon – you can place it in either location (forms code module or standard code module). However of course if the ribbon going to be active for different forms, then place the following code in a standard code module:

Public Function ShowExcelDoc()

  Application.FollowHyperlink "Z:\DASHBOARD\SCHEDULE\MASTER SCHEDULE.xlsx"
   
End Function

Note VERY careful the format I used in the above on-action. This allows you to directly call/run a VBA function – so no “iribbion” or anything else is required. Just some plane Jane VBA and the correct “on-action” setting in the ribbon XML. And you can even pass the document (so you could have several buttons to show different docs.

eg:

                   onAction="=ShowExcelDoc('path name to doc goes here')

and:

Public Function ShowExcelDoc(strDocName as string)

Application.FollowHyperlink strDocName

Very little code is required here.

Regards,
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada