选项1
在您的情况下,最简单的选择是将保存和退出命令构建到Excel宏中,而不是Outlook宏。
也就是说,您可以修改Outlook代码:
Sub AskMeAlerts()
Dim appExcel As Excel.Application
Dim wkb As Excel.Workbook 'Is this declaration necessary for some code elsewhere? You do not use this variable and I would recommend removing the declaration.
Set appExcel = CreateObject("Excel.Application")
With appExcel
.Workbooks.Open ("C:\Ask me question workflow.xlsm")
.Visible = True
.Run "'Ask me question workflow.xlsm'!AskMeFlow"
'No need to explicitly set alert values or save workbook as Excel macro will handle this.
End With
Set appExcel = Nothing
Set wkb = Nothing 'Again, is this necessary?
End Sub
您可以将以下内容添加到“Ask me question workflow.xlsm”文件的末尾:
Application.DisplayAlerts = False
ThisWorkbook.Close SaveChanges:=True
Application.Quit
注意:如果您还将手动运行宏或在其他情况下不希望工作簿保存、关闭和退出,您可以考虑向AskMeFlow宏添加一个输入变量,默认值为False,但由Outlook设置为True。我认为这略微超出了本答案的范围,所以我不会进一步阐述,但如果您对此选项感兴趣,请告诉我。
选项2已被删除。请参见Uri的解决方案;我建议的改进并没有从根本上改变那个解决方案。
选项3:根据Excel代码的性质,您可以将其转换为函数并捕获输出变量。类似于以下内容:
Sub AskMeAlerts()
Dim appExcel As Excel.Application
Dim wkb As Excel.Workbook
Dim StrOutput as string
StrOutput = "Excel macro did not complete."
Set appExcel = CreateObject("Excel.Application")
appExcel.Workbooks.Open ("C:\Ask me question workflow.xlsm")
appExcel.Visible = True
StrOutput = appExcel.Run "'Ask me question workflow.xlsm'!AskMeFlow"
MsgBox StrOutput
appExcel.DisplayAlerts = False
appExcel.ActiveWorkbook.Save
appExcel.Quit Set appExcel = Nothing
Set wkb = Nothing
End Sub
您需要将AskMeFlow更改为函数,并添加以下代码:
然后您需要将AskMeFlow更改为函数,并添加以下代码:
Function AskMeFlow() as String
AskMeFlow = "Uncaught error executing Excel code."
'Your code here
AskMeFlow = "Excel code completed successfully!"
End Function
Set appExcel = CreateObject("Excel.Application") appExcel.Workbooks.Open ("C:\Ask me question workflow.xlsm") appExcel.Visible = True appExcel.Run "'Ask me question workflow.xlsm'!AskMeFlow" ---- 在此处我想等到宏完成 ---- appExcel.DisplayAlerts = False appExcel.ActiveWorkbook.Save appExcel.Quit Set appExcel = Nothing Set wkb = Nothing
End Sub - user3016795