excel - VBA .VBProject.VBComponents.Item ("ThisWorkbook").CodeModule.AddFromString 不工作

Question

我正在创建一堆动态链接的工作簿，我需要抑制链接的警告，所以我试图添加一个 Workbook_Open() sub 使用正确的代码。

唯一的问题是代码实际上并未添加到工作簿中。

' do not display alerts while processing so many files
Application.DisplayAlerts = False
wbNew.SaveAs FileName:=path, FileFormat:=51, CreateBackup:=False

' add a little Workbook_Open method to the new workbook's ThisWorkbook dynamically
' this line doesn't actually do anything - why?
wbNew.VBProject.VBComponents.Item("ThisWorkbook").CodeModule.AddFromString ( _
    "Private Sub Workbook_Open()" & vbCrLf _
    & "    Application.AskToUpdateLinks = False" & vbCrLf _
    & "    Application.DisplayAlerts = False" & vbCrLf _
    & "End Sub")
wbNew.Save  
wbNew.Close

文件已创建，一切正常，只是我刚刚制作的工作簿中实际上没有添加任何代码，它是一个普通的工作簿，它的 ThisWorkbook 是空白的。 (我还检查了我没有禁止任何有关动态代码编写的警告，只是正常的 saveas 提示)。

我怎样才能使这项工作？

Answer 1

这对我有用，没有提示或缺少代码:

Sub test()

    Dim wbnew As Workbook

    Set wbnew = Workbooks.Add


    Application.DisplayAlerts = False
    wbnew.SaveAs Filename:="C:\temp\abc.xlsm", FileFormat:=XlFileFormat.xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

    wbnew.VBProject.VBComponents.Item("ThisWorkbook").CodeModule.AddFromString ( _
        "Private Sub Workbook_Open()" & vbCrLf _
        & "    Application.AskToUpdateLinks = False" & vbCrLf _
        & "    Application.DisplayAlerts = False" & vbCrLf _
        & "End Sub")

    wbnew.Close True
    Application.DisplayAlerts = True

End Sub

对于宏提示设置 Fil--> Options--> Trust Center --> Macro Setting to this 将摆脱提示: