vba - 从 VBA 中删除使用另存为保存的副本中的 Workbook_Open 代码

Question

我试图强制用户保存新填充的模板(使用 VBA 和其中的宏创建)以避免重新打开时数据丢失(因此用户不必在每次创建执行程序后删除数据) , 而我 read关于启用和禁用事件的一些东西，我试过了，但没有运气。它保存了同一本书，但如果我打开它，它似乎仍然在执行。无论如何要从该副本中删除宏吗？

我唯一有兴趣禁用和/或删除的宏将是 Workbook_Open 中的代码，因为那是调用模块代码的那个......我只对从我的副本中删除它感兴趣m 用另一个名字保存。不是用户首先打开的文件。

进一步说明:

我需要 Workbook_Open 上的代码。让我试着更好地解释一下，我有一张包含所有代码的表格，当它打开时，它会要求您选择一些输入文件以填充工作簿，完成后，它会要求用户保存填充工作簿作为副本，但是当我打开保存的副本时，就像我打开第一张工作表一样。而且我只想显示填充的模板，而不是像一个精确的副本一样。这就是为什么我想删除工作簿

代码:

Application.ScreenUpdating = True
ThisWorkbook.Activate
MsgBox "Site Configuration List" & vbNewLine & "Generated Sucessfully", vbOKOnly, "SCL Generated Sucessfully"
MsgBox "Remember to save the file with a different filename" & vbNewLine & "The next time the program executes it'll erase all information contained in it", vbInformation
SaveWorkbookAsNewFile (Project & "_" & ProjectName)

Private Sub SaveWorkbookAsNewFile(NewFileName As String)
Dim ActSheet As Worksheet
Dim ActBook As Workbook
Dim CurrentFile As String
Dim NewFileType As String
Dim NewFile As String

Application.ScreenUpdating = False ' Prevents screen refreshing.
Application.EnableEvents = False
CurrentFile = ThisWorkbook.FullName

NewFileType = "Excel Files 1997-2003 (*.xls), *.xls"

NewFile = Application.GetSaveAsFilename( _
InitialFileName:=NewFileName, _
fileFilter:=NewFileType)

If NewFile <> "" And NewFile <> "False" Then
ActiveWorkbook.SaveCopyAs Filename:=NewFile, _
FileFormat:=xlNormal, _
Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=True, _
CreateBackup:=False
End If

Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

我发现了类似的东西 here以前，但这真的没有帮助我。

Answer 1

I'm using excel 2007. In the new file (Filled sheet) I don't require any code. I'd like just to show the information and that's it – Splendonia just now

在这种情况下，您只需将文件保存为 .xlsx文件。您不必做删除代码的繁琐工作。该文件将自动剥离所有代码。

换行

ActiveWorkbook.SaveCopyAs Filename:=NewFile, _
FileFormat:=xlNormal, _
Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=True, _
CreateBackup:=False

至

ActiveWorkbook.SaveAs Filename:=NewFile, _
FileFormat:= xlOpenXMLWorkbook , _
Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=True, _
CreateBackup:=False

还记得更改 NewFileType因此。

编辑

Oops, I forgot to say I might need to save it as a .xls file, the program in which the users need to upload this later does not support >excel2007 files – Splendonia 5 mins ago

删除 Workbook_Open ，使用此代码(经过试验和测试)。礼貌 Chip Pearson .请不要忘记阅读 Introduction在尝试此代码之前在链接中提到。

Option Explicit

Sub DeleteProcedureFromModule()
    Dim VBProj As VBIDE.VBProject
    Dim VBComp As VBIDE.VBComponent
    Dim CodeMod As VBIDE.CodeModule

    Dim StartLine As Long
    Dim NumLines As Long
    Dim ProcName As String

    Set VBProj = ActiveWorkbook.VBProject
    Set VBComp = VBProj.VBComponents("ThisWorkbook")
    Set CodeMod = VBComp.CodeModule

    ProcName = "Workbook_Open"
    With CodeMod
        StartLine = .ProcStartLine(ProcName, vbext_pk_Proc)
        NumLines = .ProcCountLines(ProcName, vbext_pk_Proc)
        .DeleteLines StartLine:=StartLine, Count:=NumLines
    End With
End Sub