Excel & VBA: run a macro from another workbook from custom ribbon

Question

The subject sums it up...
with WIN10 and 365.

I have created a PERSONAL.XLSB with code that loads a custom tab for my ribbon just by opening a new workbook. I have distributed this PERSONAL.XLSB to another user on the network so they too can load the custom tab for the ribbon just by opening a new workbook. No problems here.

I am getting tripped up with how my buttons on the custom ribbon need to call specific macros.

What I would like to do is have a separate workbook for each tool I am providing. I would like my ribbon button to run a macro contained in another workbook - the published user version of my 'developer' workbook. I want to develop separate tools in separate .xlsm files, publish a debugged copy that is ready for use to a network location, and have all the users access the macros from buttons on a custom ribbon.

Here is the code that creates the button in the custom ribbon:

ribbonXML = ribbonXML + "        <mso:group id='ArchiCAD\_Group' label='ArchiCAD Tools' autoScale='true'>" & vbNewLine 

ribbonXML = ribbonXML + "          <mso:button id='runClmFit' screentip='Adjust Excel to fit ArchiCAD layout guide' supertip='Supertip description' label='Column x Fit' " & vbNewLine 

ribbonXML = ribbonXML + "imageMso='AppointmentColor5' onAction='AdjustColumns'/>" & vbNewLine 

ribbonXML = ribbonXML + "          <mso:button id='runPDF\_Crop' screentip='Crop .pdf file to fit in ArchiCAD layout guide' supertip='Supertip description' label='Crop a .pdf' " & vbNewLine 

ribbonXML = ribbonXML + "imageMso='AppointmentColor5' onAction='RunPdfCrop'/>" & vbNewLine 

ribbonXML = ribbonXML + "        </mso:group>" & vbNewLine 

These custom buttons work great when used in the .xlsm file that contains the source code for the macro. The work great with a MsgBox also.

So... I opened a new workbook and the second from last line of [onAction='RunPdfCrop'] is where I am trying to access the macros.
* I have tried [onAction=Application.Run 'Another Workbook.xlsm'!RunPdfCrop]
... with a wide variety of single quote and double quote placement, hoping to get syntax correct. I end up creating incomplete strings with comments by using different mashups of " and '.
* I have tried [onAction='RunPdfCrop']
... where 'RunPdfCrop' is a sub in PERSONAL.XLSB that has the line [Application.Run 'Another Workbook.xlsm'!PDF_CropDetail] to run the external macro from another workbook. I get a message about how the 'RunPdfCrop' cannot even run because it may not be available in this workbook or all macros may be disabled, even though this macro that calls the external macro is in the PERSONAL.XLSB file.

What am I missing?
Can someone point me in the right direction please?

I cannot imagine that this development strategy is so far fetched that it is impossible...

Thanks to all that read this,
Chris

Answer 1

Sub HowToRunOtherWorkbooksMacro()

Dim w As Workbook 

On Error GoTo OpenFile 

'Check to see if file is already open or not

Set w = Workbooks("Book with macro.xlsm") 

RunMacro:

Application.Run "'Book with macro.xlsm'!MacroName" 

Exit Sub 

OpenFile:

'Open the file because it was not open

Workbooks.Open ("C:\FolderPath\Book with macro.xlsm") 

Resume RunMacro 

End Sub

Answer 2

Success!

in the button definition of on the custom ribbon:
onAction='PERSONAL.XLSB!RunFromRibbon'

in the procedure called (thanks Bernie):

Public Sub RunFromRibbon()

Dim objWrkb As Workbook 

On Error GoTo OpenFile 

'Check to see if file is already open or not 

Set objWrkb = Workbooks.Open("O:\CAD\ArchiCAD\Tools\UL tables.xlsm") 

RunMacro:

Application.Run "'O:\CAD\ArchiCAD\Tools\UL tables.xlsm'!PDF\_CropDetail" 

***objWrkb.Close SaveChanges:=False*** 

Exit Sub 

OpenFile:

'Open the file because it was not open 

Workbooks.Open ("O:\CAD\ArchiCAD\Tools\UL tables.xlsm") 

***ActiveWindow.Visible = False*** 

Resume RunMacro 

End Sub

Adding the lines...
ActiveWindow.Visible = False
...and...
objWrkb.Close SaveChanges:=False
...makes the host workbook for the macro completely unobtrusive while pushing the button on the ribbon in the original workbook that calls the macro. Also, the host workbook is no longer a running process as soon as the macro is called.

Next step is to make this one procedure be the common calling procedure that works with arguments of the file name and the macro name. I probably should also see what the .Close does if I actually do have the host workbook open and want to leave it open. Theoretically though, none of the users should have this source developer file open, just me. We'll see what happens...

Thanks Bernie!
Chris

Answer 3

This part may fail if the workbook is already open:

'Check to see if file is already open or not

Set objWrkb = Workbooks.Open("O:\CAD\ArchiCAD\Tools\UL tables.xlsm")

My original code should have been changed to:

'Check to see if file is already open or not

Set objWrkb = Workbooks("UL tables.xlsm")

Answer 4

I am expecting issues if the file is already open, but I am also expecting the issues will be easy to maneuver around. Once I get to that... Currently working out the correct syntax for having the .xlsm file and the macro name as arguments. And only one procedure to call everything I want from the buttons on the custom ribbon.

I will give...

Set objWrkb = Workbooks("UL tables.xlsm")

...a shot. But was thinking that...
On Error GoTo OpenFile
...would resolve any error.

I'm really surprised I am not finding any more info than I am on this approach to setting up buttons on a custom ribbon.

Currently struggling with passing a string argument as the file name, similar to:
ribbonXML = ribbonXML + "onAction='PERSONAL.XLSB!RunFromRibbon ""UL tables""'/>" & vbNewLine

Thanks for the continued interest.

Chris