locked
Excel -VBA- Ribbon calling a macro in a worksheet that is not open RRS feed

  • Question

  • I have an Excel macro that performs a few functions, it takes a excel table from worksheet, creates an array of it contents, performs a mail merge in word, Creates a form, and a few emails(from MS-Word documents). If the macro is executed from the main spreadsheet when it is open,  everything works normally. I want to place this macro on the ribbon allowing a user to launch it without having (or knowing where the main Excel document is located or having to open manually)., Ideally making a check to upgrade the macro at a later date if changes are made.

    The user needs to validate the table before the forms are created,  I have made several attempts to Open the Speardsheet if it is not open without running the macro or run the macro if the spreadsheet is open. I created a sub to check to see if the spreadsheet was open and modified the ribbon to include an icon for the macro; which works.

    However, when launched from the ribbon while the main Excel spreadsheet is not open, it opens the workbook and runs the macro in entirety (Without executing the open workbook line of the macro). I assume the spreadsheet is being open because the macro that is being called resides with it (makes sense). I have also tried to save the worksheet as an excel add-in *.xlam activating it, but found not method of placing an icon on the ribbon to execute the macro when the spreesheet is closed.

    My objective is 

    1. place icon on Ribbon
    2. Check to see if the spreadsheet is open - if not - Open the spreadsheet but do not execute the  macro, allowing the user to validate the data.   
    3. Run Macro (again) from the ribbon, and run the macro since the spreadsheet is open.


    Does anyone have a recommended approach or best practices? below is my opening sub.  Thank you in advance.

    Sub MainForm()

    Dim WorkingFolder As String
    Dim File01 As String 'Main Excel Data File, where all data is
    Dim File02 As String 'Preliminary Email to send to user
    Dim File03 As String 'Final Email to Send to user when production is complete
    Dim wb As Workbook

    WorkingFolder = "C:\Temp\"
    File01 = "01-MainData.xlsm"
    File02 = "02-PreProductionEmail.docx"
    File03 = "03-FinalProductionEmail.docx"

    If wbIsOpen(File01) = True Then
    MsgBox "Workbook Is Open"
    Run - Sub and Functions to create forms and emails
    Else

    MsgBox "oh oh – not open, opening workbook"
    ‘Set wk = Workbooks.Open(WorkingFolder & File01)
    End If

    End Sub

    Sunday, November 1, 2020 3:49 PM