另存为并禁用公式和宏

Question

我想做一个VBA代码保存在地图中，同时关闭所有的公式和宏。

这就是我到目前为止想出的方法，但它不起作用。

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

Answer 1

如果将文件另存为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

Answer 2

除非提供更多信息，否则我猜测对“Microsoft Visual Basic for applications Extensibility #.#”的引用不是在“工具”|“引用”中选择的，也不是在代码中手动添加的。

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

我按原样复制了您的代码，代码在VBProj声明为VBIDE.VBProject时中断了。一旦我引用了前面提到的引用，代码就会执行。