excel - Excel VBA 使用“Application.AutomationSecurity”防止宏在模板中由宏创建的新文件中运行

Question

我有一个使用 Excel VBA 的 Excel 项目报告系统。报告文件是按月创建的，即一月一个文件，二月一个文件等。每个月末，我使用第 1 册中的宏打开一个模板文件，然后将当月的汇总数据结转到新月的报告文件（书2）。

在 Book 1 宏例程的末尾，它在 Book 2 工作表之一（到本月 1 日）上重置日期，这就是我的问题，输入此日期的单元格链接到 Book 2 在此日期更改时运行的宏。

通过在 Book 1 宏的开头使用“Application.AutomationSecurity = msoAutomationSecurityForceDisable”，我已经成功地停止了 Book 2 宏的运行，到目前为止一切顺利。

重置日期后，我使用“Application.AutomationSecurity = PreviousSecurity”，其中“Previous Security”是一个变量，其中包含 AutomationSecurity 在被禁用之前的初始状态。

宏结束时没有问题，并将 Book 2 文件带到前台，此时用户需要手动运行 Book 2 宏来更新徽标，但即使我重置了自动化安全性，我也无法在 Book 中运行宏2 直到文件被关闭并重新打开。

Book 1 宏的相关位是：

Sub NewCarryFwd3()

    Dim Folder As String, CFileName As String
' The 'NewClient' and 'NewProject' variables are defined as Public at head of module

' Get file names

    Folder = ActiveWorkbook.Path
    CFileName = ActiveWorkbook.Name

' Stop Excel screen updating

    Application.ScreenUpdating = False

' Stop autocalculation of formulae to speed up procedure

    Application.Calculation = xlCalculationManual
    
'**********
'Prevent Macros in the new Template running

    Dim previousSecurity As MsoAutomationSecurity
    PreviousSecurity = Application.AutomationSecurity
    Application.AutomationSecurity = msoAutomationSecurityForceDisable
    

'**********
' Calculate new DRS month dates

    Dim OldDate As Date, NewDate As Date, NewMonth As String

    OldDate = Workbooks(CFileName).Sheets("Setup").Range("Month_start").Value
    NewDate = DateAdd("m", 1, OldDate)
    NewMonth = Choose(Month(NewDate), "Jan", "Feb", "Mar", "Apr", "May", "June", "July", "Aug", "Sept", "Oct", "Nov", "Dec")

' Open New DRS File from Microsoft user's template folder

    Dim NextDRS As String

    Workbooks.Add template:= _
        "C:\Users\" & Environ("username") & "\AppData\Roaming\Microsoft\Templates\Oasis 3D DRS_V3.xltm"
    
    NextDRS = ActiveWorkbook.Name

'******************************************************************************

**' My Code to update the summary data goes in here - too much to include!**

**' After updating Summary data the macro ends as below**

'******************************************************************************


' Set initial Weekly date to first day of new month?
' Set first weekly data

    Workbooks(NextDRS).Sheets("Weekly").Range("WkDay7").Value = NewDate
    
    Sheets("Weekly").Protect Sheets("Weekly").Protect
    
' Allow macros in New workbook to run

    Application.AutomationSecurity = PreviousSecurity
    
' Create New file Name

    Dim NewFilename As String
    
    NewFilename = Folder & "\DRS " & Workbooks(CFileName).Sheets("Setup").Range("C2").Text & " " & NewMonth & ".xlsm"
    Workbooks(NextDRS).Activate
    Sheets("Daily Report").Select

' Check if new file name already exists if so add numeric ID

    Dim f As Long
    
    f = 1
    Do While Dir(NewFilename) <> ""
        NewFilename = Folder & "\DRS " & Workbooks(CFileName).Sheets("Setup").Range("C2").Text & " " & NewMonth & " " & f & ".xlsm"
        f = f + 1
    Loop

'**********
' Save new workbook for next month in Excel 2007 xlsm format.

    Workbooks(NextDRS).SaveAs FileName:= _
        NewFilename _
        , FileFormat:=52, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=True

' Re-activate screen updating & autocalculation

    Application.ScreenUpdating = True

End Sub

他们是否有某种方法可以重新启用 AutomationSecurity，以便可以运行 Book 2 宏而无需关闭并重新打开文件？

标签： excelvba