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 .

 

[Zot]

Put this under ThisWorkbook to auto run when opening the workbook

Private Sub Workbook_Open()
    
Dim FileToSave As Variant
Dim d As Date
Dim shp As Shape

d = DateSerial(2021, 12, 30)  ' Set date here

If Date >= d Then
    For Each shp In ActiveSheet.Shapes
        shp.Delete
    Next shp
    
    Application.DisplayAlerts = False
    FileToSave = Application.GetSaveAsFilename(fileFilter:="File Type (*.xlsx), *.xlsx")
    If FileToSave <> False Then
        ActiveWorkbook.SaveAs Filename:=FileToSave, FileFormat:=xlOpenXMLWorkbook
    End If
    Application.DisplayAlerts = True
End If

End Sub

 

[Alaa mg]

thanks . this is not what I want . it should save the file as xlsm in specific directory I specify as in my OP

c:\temp

not by using open dialog ,and at the same time save the file is open as xlsx directly without ask me save.

 

[Alaa mg]

another option experts ?

 

[Zot]

another option experts ?

Sorry for not getting back sooner. Try this. Same as before, put in Workbook_Open event.

Private Sub Workbook_Open()
   
Dim FName As String, Path As String
Dim wb As Workbook
Dim d As Date
Dim shp As Shape

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

Set wb = ActiveWorkbook
FName = Replace(wb.Name, ".xlsm", "")
If Date >= d Then
    For Each shp In ActiveSheet.Shapes
        shp.Delete
    Next shp
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:=Path & "\" & FName, FileFormat:=xlOpenXMLWorkbook
    Application.DisplayAlerts = True
End If

End Sub

 

[Alaa mg]

thanks , but unfortunately it gives "application defined or object defined error"

ActiveWorkbook.SaveAs Filename:=Path & "\" & FName, FileFormat:=xlOpenXMLWorkbook

 

[Zot]

thanks , but unfortunately it gives "application defined or object defined error"

ActiveWorkbook.SaveAs Filename:=Path & "\" & FName, FileFormat:=xlOpenXMLWorkbook

Oppss my mistake

Remove the backslash because already in Path
ActiveWorkbook.SaveAs Filename:=Path & FName, FileFormat:=xlOpenXMLWorkbook

 

[Alaa mg]

thanks but in C:\TEMP\ the file should save as XLSM and the original file what I run it should save it as XLSX

 

[Zot]

thanks but in C:\TEMP\ the file should save as XLSM and the original file what I run it should save it as XLSX

I have no ide where your xlsm are located now when you run it. I was assuming in the Temp folder.

So just add another line

Private Sub Workbook_Open()
   
Dim FName As String, Path As String
Dim wb As Workbook
Dim d As Date
Dim shp As Shape

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

Set wb = ActiveWorkbook
FName = Replace(wb.Name, ".xlsm", "")
If Date >= d Then
    For Each shp In ActiveSheet.Shapes
        shp.Delete
    Next shp
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:=Path & FName, FileFormat:=52 ' or xlOpenXMLWorkbookMacroEnabled
    ActiveWorkbook.SaveAs Filename:=Path & FName, FileFormat:=51 ' or xlOpenXMLWorkbook
    Application.DisplayAlerts = True
End If

End Sub

 

[Alaa mg]

I have no ide where your xlsm are located now when you run it.

sorry I thought my explenation is clear.

the orginal file run from this directory

"C:\Users\PC WORLD\Desktop\delete1.xlsm"

when run the macro first save as xlsm in this directory

"C:\Temp\"

and save the orginal file as XLSX in the same directory where from run in the same place with considering I don't want the orginal file as xlsm . it should delete it and replace xlsx replace for it .
thanks for your cooperation