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]

Didn't give up actually.

I have some concerns though. Does anyone else has access to this xlsm file? If so, how are we certain it will be on the desktop?

If any other user opens this xlsm file after the set date, the code will fail because they won't have:

1 - C:\Temp folder
2- Desktop path will be different due to different user.

These are easy to fix, but did you think about them?

Using a custom document property seems to be a nice way of stopping the workbook_open macro to stop from running without depending on external files. Maybe also a check on username?

To be honest, I am a bit lost as well. The objectives are not clear enough. If only Alaa has access to the xlsm version, the provided code should have already fixed the issue.

 

[GWteB]

finally this is what I want to prevent your macro run repeatedly every time run the macro when open the file in c:\temp\

I see, I'll try to make it al clear for you.
Basically you are asking, when a certain date has been reached (or already passed), to clone an existing workbook which contains macros. The clone must become a version without shapes and without macros in the current folder on disk (i.e. .XLSX). The original version must be moved unaffected to the disk folder C:\temp. The moved version should ignore the expiration date when opened, hence be able to be opened with full access at any time.
The code in my post #38, to be put in the to be cloned workbook, does all this. The code cannot be made self-destructive.

 

[Zot]

Just to get clarification again.

Now from my understanding since you do not even want to have the macro file in C:\Temp, meaning you will just end up with on-macro file without shape and button in originating folder after opening the macro file. That's it.

The original file with macro will be kept personally somewhere else.

If this is so, then there is solution for that.

 

[Alaa mg]

@Zot

Now from my understanding since you do not even want to have the macro file in C:\Temp

I would explain this point . what I mean when save the file in C:\Temp should save as xlsm with all the buttons and shapes and the macros . what I want to the file doesn't contain your macro becaues when I open the file from C:\Temp it will run the macro repeatedly and shows error . I would open the file from C:\Temp as was in orginal file in desktop . if you see this is not possible then at least disabling your macro . thanks again

 

[Zot]

@Zot

I would explain this point . what I mean when save the file in C:\Temp should save as xlsm with all the buttons and shapes and the macros . what I want to the file doesn't contain your macro becaues when I open the file from C:\Temp it will run the macro repeatedly and shows error . I would open the file from C:\Temp as was in orginal file in desktop . if you see this is not possible then at least disabling your macro . thanks again

If you open it is C:\Temp, what do you expect it to happen? Nothing to happen I guess.

 

[Zot]

If you want macro file to not doing anything when you run in C:\Temp, then an easy solution is to stop execution if path is C:\Temp. I just added the red line. It will stop execution if opened in C:\Temp.

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
If wb.Path = "C:\Temp" Then End
cPath = wb.Path & "\"
FName = Replace(wb.Name, ".xlsm", "")
If Date >= d Then
    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
    For Each shp In ActiveSheet.Shapes
        shp.Delete
    Next shp
    ActiveWorkbook.SaveAs Filename:=cPath & FName, FileFormat:=51 ' or xlOpenXMLWorkbook
    Application.DisplayAlerts = True
End If

End Sub

 

[Alaa mg]

If you open it is C:\Temp, what do you expect it to happen? Nothing to happen I guess.

yes this is like the orginal file in desktop before using your macro

the part important occures just in desktop . then the user can't take advantage from it and the file in C:\TEMP\ nobody knows it except me

 

[Alaa mg]

@Zot
magnificent ! just last thing can you mod this line

d = DateSerial(2021, 9, 21)

as you know after this date your macro not work so I would add another condition by bigger more than this date

thanks for your help

 

[Zot]

@Zot
magnificent ! just last thing can you mod this line

d = DateSerial(2021, 9, 21)

as you know after this date your macro not work so I would add another condition by bigger more than this date

thanks for your help

The concern is that what if no one open the file on that date due to holiday or whatever. Maybe give the time period between certain date perhaps.

 

[Gokhan Aycan]

Also if you open it from Temp on the same day it will still error

@Zot Why don't you consider using a Custom Document Property to check if file is already is processed?

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