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, where the macro is, 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 to check to upgrade the macro at a later date if changes are made.
The user needs to validate the table before the forms are create and I have made several attempts to Open the Spearsheet 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 excel is closed, the check to see if the workflow is open executes.
Does anyone have a recommended approach or best practices? 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
The user needs to validate the table before the forms are create and I have made several attempts to Open the Spearsheet 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 excel is closed, the check to see if the workflow is open executes.
Does anyone have a recommended approach or best practices? 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
