remove all macros and forms in file and save the file as xlsx

[Alaa mg]

hi

is there macro remove all macros and forms , shapes and buttons in file XLSM and save the file as XLSX based on date(30/12/2021) .

the file should work until before this date . if I open file in 30/12/2021 then should delete all macros and forms and shapes , buttons in all the sheets

with keep the data contains value, letters ,formatting , borders ,formulas...etc ain all sheets when save the file as xlsx . but before does all what I mentioned should save the file as xlsm in this directory c:\temp just when open file in 30/12/2021 .
I hope my thread is clear .

 

[Gokhan Aycan]

I am not wrong, I just asked a question

 

[Alaa mg]

sorry . I think you tested the code and you've found not delete it

 

[Gokhan Aycan]

Another option to make the macro not run would be to add a custom document property perhaps?

I haven't used it before so can not say if there is a downside, but just made a test. In the immediate window:

thisworkbook.CustomDocumentProperties.add name:="Test",LinkToContent:=false,type:=msoPropertyTypeString,Value:="processed"

? thisworkbook.CustomDocumentProperties("test")
processed

So check for this in the if statement (since cells may change etc.)?

Edit: Persists after re-opening of the file.

Edit2: Throws an error when no such property exists, so error handling required.

 

[Alaa mg]

where I put these lines in @GWteB'S code , @Zot 's code?

 

[Gokhan Aycan]

Don't do anything just yet, let them decide on how to proceed.

 

[Alaa mg]

ok let's wating for them

 

[Gokhan Aycan]

Oh well I did modify Zot's code, try on a copy of course.

Private Sub Workbook_Open()

' Check if file was processed before
Dim strProcessed as String

On Error Resume Next

strProcessed = ThisWorkbook.CustomDocumentProperties("Processed")

' If no error while reading "Processed" property (we don't care about its value, just that it is there), then exit sub
If Err.Number = 0 Then
    On Error GoTo 0
    Exit Sub
End If

Err.Clear
On Error GoTo 0

Dim FName As String, Path As String, cPath As String
Dim wb As Workbook
Dim d As Date
Dim shp As Shape

d = DateSerial(2021, 9, 21)
Path = "C:\Temp\"

Set wb = ActiveWorkbook
cPath = wb.Path & "\"
FName = Replace(wb.Name, ".xlsm", "")
If Date >= d Then
    ' Set "Processed" property
    ThisWorkbook.CustomDocumentProperties.Add Name:="Processed",LinkToContent:=False,Type:=msoPropertyTypeString,Value:="Processed"
    For Each shp In ActiveSheet.Shapes
        shp.Delete
    Next shp
    Application.DisplayAlerts = False
    ' SAve macro file in Temp
    ActiveWorkbook.SaveAs Filename:=Path & FName, FileFormat:=52 ' or xlOpenXMLWorkbookMacroEnabled
    ' Delete macro file in original folder
    Kill cPath & FName & ".xlsm"
    ' Save non-macro file in original location
    ActiveWorkbook.SaveAs Filename:=cPath & FName, FileFormat:=51 ' or xlOpenXMLWorkbook
    Application.DisplayAlerts = True
End If

End Sub

 

[Alaa mg]

great work

 

[Alaa mg]

@Gokhan Aycan ,GWteB ,Zot based on post#27 last thing when save file as xlsm should not remove the buttons and shapes
thanks for your cooperation

 

[Gokhan Aycan]

Hmm, the shapes are deleted before saving at the moment, right?