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

Alaa mg

Board Regular
Joined
May 29, 2021
Messages
116
Office Version
  1. 2019
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 .
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,767
Office Version
  1. 2016
Platform
  1. Windows
Put this under ThisWorkbook to auto run when opening the workbook
VBA Code:
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

Board Regular
Joined
May 29, 2021
Messages
116
Office Version
  1. 2019
thanks . this is not what I want . it should save the file as xlsm in specific directory I specify as in my OP
VBA Code:
c:\temp
not by using open dialog ,and at the same time save the file is open as xlsx directly without ask me save.
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,767
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

another option experts ?
Sorry for not getting back sooner. Try this. Same as before, put in Workbook_Open event.
VBA Code:
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

Board Regular
Joined
May 29, 2021
Messages
116
Office Version
  1. 2019
thanks , but unfortunately it gives "application defined or object defined error"
VBA Code:
ActiveWorkbook.SaveAs Filename:=Path & "\" & FName, FileFormat:=xlOpenXMLWorkbook
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,767
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

thanks , but unfortunately it gives "application defined or object defined error"
VBA Code:
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

Board Regular
Joined
May 29, 2021
Messages
116
Office Version
  1. 2019
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

Well-known Member
Joined
Nov 26, 2020
Messages
1,767
Office Version
  1. 2016
Platform
  1. Windows
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
VBA Code:
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

Board Regular
Joined
May 29, 2021
Messages
116
Office Version
  1. 2019
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
VBA Code:
"C:\Users\PC WORLD\Desktop\delete1.xlsm"

when run the macro first save as xlsm in this directory
Code:
"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:)
 

Forum statistics

Threads
1,144,104
Messages
5,722,500
Members
422,440
Latest member
bhavsarsunil29

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top