Clever Geek Handbook

Save as and disable formulas and macros

I want to make VBA code to save like on a map and also disable all formulas and macros.

This is what I came up with, but it doesn't work.

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
+3


source to share


2 answers


If you save the file as xlsx

, then any code is automatically deleted. This makes it easier:



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
+1


source


If no more information is provided, I am assuming that the link to "Microsoft Visual Basic for Applications Extensibility #. #" Is not selected in Tools | "Links" or manually added to the code.

https://web.archive.org/web/www.cpearson.com/excel/vbe.aspx



I copied your code as is and the code broke in the declaration for VBProj as VBIDE.VBProject. Once I link to the above link, the code is executed.

0


source






More articles:


All Articles