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]

Looks like both xlsm and xls are saved to the temp folder in the above code.

Insert this between 2 savesas operations:

Path = Application.Path & "\"

or if that doesn't work

Path = "C:\Users\PC WORLD\Desktop\"

or

Path = Environ$("userprofile") & "\Desktop\"

 

[Alaa mg]

@Gokhan Aycan that's better ,but I said

I don't want the orginal file as xlsm . it should delete it and replace xlsx replace for it .

isn't clear ? it should when save the file as xlsx and xlsm should delete orginal file is xlsm from this directory

Path = "C:\Users\PC WORLD\Desktop\"

if it's possible close the file directly after run the macro .

 

[GWteB]

There's some misunderstanding here. @Alaa mg, see if this works for you.

Private Sub Workbook_Open()
   
    Const TEMPPATH As String = "C:\Temp"                    ' <<<< change to suit

    Dim ExpDate As Date: ExpDate = DateSerial(2021, 9, 18)  ' <<<< change year, month, day to suit
    Dim oWs         As Worksheet
    Dim Shp         As Shape
    Dim OldFullName As String
    Dim NewFullName As String
   
    If Date >= ExpDate Then
        With ThisWorkbook
            Application.DisplayAlerts = False
            OldFullName = .FullName
            NewFullName = BuildFullName(.Path, VBA.Replace(.Name, ".xlsm", ".xlsx", , , vbTextCompare))
            ' save temp version
            .SaveAs BuildFullName(TEMPPATH, .Name), xlOpenXMLWorkbookMacroEnabled
            ' delete all shapes on all sheets
            For Each oWs In .Worksheets
                For Each Shp In oWs.Shapes
                    Shp.Delete
                Next Shp
            Next oWs
            ' save as XLSX in original folder
            .SaveAs NewFullName, xlOpenXMLWorkbook
            Application.DisplayAlerts = True
            Kill OldFullName
            .Close False
        End With
    End If
End Sub

EDIT:

Didn't post all the code:

Public Function BuildFullName(ByVal argPath As String, argFileName As String) As String
    If Right(argPath, 1) <> "\" Then argPath = argPath & "\"
    BuildFullName = argPath & argFileName
End Function

 

[Alaa mg]

@GWteB thanks for your sharing . but it gives error compile error " sub or function not defined" in this word

BuildFullName

and the code is crashed and close the file and return the open

 

[Zot]

You should stated all in the beginning

Private Sub Workbook_Open()
   
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
    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

 

[Gokhan Aycan]

EDIT:

Didn't post all the code:

And here I was thinking I learned a new native function

@Zot Can you delete the file which is running the macro?

 

[Alaa mg]

@GWteB

Didn't post all the code:

sorry about it .
I have a problem . this missed me . when I save the file as xlsm I don't want your macro in c:\ temp\ because when I try open it will close the excel . is it possible way to get rid of the current macro when save as xlsm . and last thing when run macro should close the whole application excel .

 

[Gokhan Aycan]

Perhaps: When macro is run the first time, set a cell with a special string or something. And check for it also in the if check. This should stop it being executed on subsequent calls.

 

[Alaa mg]

@Zot it's great !can you delete the your macro which file save as xlsm from c:\temp\ when open it? it shows error because run file changes location

 

[Alaa mg]

@Zot Can you delete the file which is running the macro?

I think you're wrong . @Zot 's code does really it