另存为并禁用公式和宏

I want to make a VBA code to save as in a map and also turn off all formulas and macros.

我想制作一个VBA代码以保存在地图中,并关闭所有公式和宏。

This is what I've come up with so far but it isn't working.

这是我到目前为止所提出的,但它无法正常工作。

Sub Opslaanzonderformules()
  Dim strFileName As Variant, strPath As String
  Dim VBProj As VBIDE.VBProject, VBComp As VBIDE.VBComponent, CodeMod As VBIDE.CodeModule
  strFileName = Application.GetSaveAsFilename(InitialFileName:=strPath & [AJ2], _
                                              FileFilter:="Excel Files (*.xls), *.xls, Excel 2007 Files (*.xlsm), *.xslm", _
                                              FilterIndex:=1, _
                                              Title:="Kies de juiste map en pas eventueel de bestandsnaam aan!")
  If strFileName = False Then
    MsgBox "Oh oh... je hebt niet opgeslagen! "
  Else
    ActiveSheet.Copy
    With ActiveWorkbook
        With .Sheets("blad1")
            .Unprotect
            .UsedRange.Value = .UsedRange.Value
            .Protect
        End With
     Set VBProj = .VBProject
     For Each VBComp In VBProj.VBComponents
            If VBComp.Type = vbext_ct_Document Then
                Set CodeMod = VBComp.CodeModule
                With CodeMod
                    .DeleteLines 1, .CountOfLines
                End With
            Else
                VBProj.VBComponents.Remove VBComp
            End If
        Next VBComp
    .SaveAs Filename:=strFileName
End With
  MsgBox "Gelukt!  Opgeslagen als: " & strFileName

  End If
End Sub

2 个解决方案

#1

If you save the file as xlsx then any code is automatcially removed. Which simplifies to:

如果将文件另存为xlsx,则会自动删除任何代码。这简化为:

Sub Opslaanzonderformules()
  Dim strFileName As Variant, strPath As String
  Dim VBProj As VBIDE.VBProject, VBComp As VBIDE.VBComponent, CodeMod As VBIDE.CodeModule
  strFileName = Application.GetSaveAsFilename(InitialFileName:=strPath & [AJ2], _
                                              FileFilter:="Excel Files (*.xls), *.xls, Excel 2007 Files (*.xlsm), *.xslm", _
                                              FilterIndex:=1, _
                                              Title:="Kies de juiste map en pas eventueel de bestandsnaam aan!")
  If strFileName = False Then
      MsgBox "Oh oh... je hebt niet opgeslagen! "
  Else
      ActiveSheet.Copy
      With ActiveWorkbook
            With .Sheets(1)
               .Unprotect
               .UsedRange.Value = .UsedRange.Value
               .Protect
            End With
      .SaveAs Left$(strFileName, InStrRev(strFileName, ".")) & "xlsx", xlOpenXMLWorkbook
      End With
  MsgBox "Gelukt!  Opgeslagen als: " & Left$(strFileName, InStrRev(strFileName, ".")) & "xlsx"
  End If
End Sub

#2

Unless more information is provided, my guess is that the reference to 'Microsoft Visual Basic for applications Extensibility #.#' is not selected in 'Tools'|'References' or manually added in code.

除非提供更多信息,否则我的猜测是在“工具”|“引用”中未选择“Microsoft Visual Basic for Applications Extensibility#。#”,或者在代码中手动添加。

http://www.cpearson.com/excel/vbe.aspx

I copied your code as is, and the code broke on the declaration for VBProj as VBIDE.VBProject. As soon as I referenced the aforementioned reference, the code executed.

我按原样复制了你的代码,代码在VBProj的声明中打破了VBIDE.VBProject。一旦我引用了上述参考,就执行了代码。

#1