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]

@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

Sorry. I am in different time zone here.

Now. I getting confused on what you were trying to do.

You have a macro file in C:\Users\PC WORLD\Desktop\
You want:
1) The file to be saved in C:\Temp\ when it is open on or after specific date
2) Save a copy of the file without macro in original folder C:\Users\PC WORLD\Desktop\
3) Remove the macro file in original folder
That was what my code do, right?

Are you saying that now you want to remove the macro from the macro file in C:\Temp when you opened it?

If that is so, it means all you want is:
1) A non-macro file in C\Temp
2) A non-macro file in C:\Users\PC WORLD\Desktop\

It is like:
1) Run macro file in C:\Users\PC WORLD\Desktop\
2) Create a non-macro copy in C:\Users\PC WORLD\Desktop\
3) Create a non-macro copy in C:\Temp\
4) Let the macro file kill itself (like Mission Impossible self destruction message ?)

When you ran macro file in C:\Users\PC WORLD\Desktop\ then save a macro file in C:Temp\, the control is with macro file in C:\Temp\. That is why I can Kill the macro in original folder. After I save a non-macro file, the non-macro file is taking over as active file. So, no more macro is running.

When you open the macro file in C:\Temp\ , it will try to go to same routine. Now the original folder and destination folder is the same folder. It will try to save a macro copy to C:\Temp\ which is unto itself. That probably cause the error.

 

[Zot]

And here I was thinking I learned a new native function

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

No, as far as I know.

 

[Zot]

@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

You could easily move the remove shape loop just before saving as non-macro file. My code was removing it early in the beginning.

 

[Zot]

I tried to open the macro file in the Temp folder. The error I get is because the macro is trying to delete its own file. That causes the error, not because trying to save on itself as stated in my post #41

 

[Zot]

Here is the code to save the macro version file with shapes intact

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, 20)
Path = "C:\Temp\"

Set wb = ActiveWorkbook
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 
    ' 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 
    Application.DisplayAlerts = True
End If

End Sub

 

[Alaa mg]

@GWteB

An alternative could be to work with a key file. If the workbook expires, the code will search for the key file. If not, only an XLSX version will be retained and the original XLSM will be destroyed. If this key file is present, and you are the only one who has that key file obviously , then nothing happens and the workbook is just opened with full access.

can you give me the idea how do that . how put this procedure in the code? or this diffrent thread .

So I persist in my advice of two versions: one user version which expires at a certain date and one for yourself, with the workbook open event handler disabled so it never expires.

actaully I have copy of my orginal file. the aim all of that . I don't want any user use the file contains macro to not amend my work . just allow use file xlsx it becomes useless .

 

[Alaa mg]

Now. I getting confused on what you were trying to do.

You have a macro file in C:\Users\PC WORLD\Desktop\
You want:
1) The file to be saved in C:\Temp\ when it is open on or after specific date
2) Save a copy of the file without macro in original folder C:\Users\PC WORLD\Desktop\
3) Remove the macro file in original folder
That was what my code do, right?

you're right

If that is so, it means all you want is:
1) A non-macro file in C\Temp
2) A non-macro file in C:\Users\PC WORLD\Desktop\

It is like:
1) Run macro file in C:\Users\PC WORLD\Desktop\
2) Create a non-macro copy in C:\Users\PC WORLD\Desktop\
3) Create a non-macro copy in C:\Temp\
4) Let the macro file kill itself (like Mission Impossible self destruction message ?)

yes this is what I want .

When you open the macro file in C:\Temp\ , it will try to go to same routine. Now the original folder and destination folder is the same folder. It will try to save a macro copy to C:\Temp\ which is unto itself. That probably cause the error.

yes this is the point I would make your attention .I no know if there is way to delete your macro in C:\TEMP\
is it possible or impossiple to vercome the error and over come run repeatedly ? because it will remove the shapes and button again when open file in C:\TEMP\ and the file becomes useless without buttons and shapes

 

[Alaa mg]

@GWteB
I no know if there is way to delete your macro in C:\TEMP\
is it possible or impossiple ?

 

[GWteB]

I no know if there is way to delete your macro in C:\TEMP\
is it possible or impossiple ?

If by "macro" you mean the workbook that contains the macro: yes that's possible.

I don't want any user use the file contains macro to not amend my work

You might consider putting a password on your VBA project.

just allow use file xlsx it becomes useless .

Had the idea that this was the intention, because that's what you asked for in combination with an expiration date, since you said:

if I open file in 30/12/2021 then should delete all macros and forms and shapes , buttons in all the sheets
but before does all what I mentioned should save the file as xlsm in this directory c:\temp

So I'm genuinely at lost for now. Before we go any further, I think it's fair to ask you to make up your mind ...

 

[Alaa mg]

@GWteB first I would apology because every time shows new happens . I don't expect my thread takes about 49 posts to solve it , and I know the rest of members give up from this thread.

You might consider putting a password on your VBA project.

I don't mean amend the code . I mean amending the data in the sheet .even put password in vba project this is not solve the problem because user able run the macros by button

If by "macro" you mean the workbook that contains the macro: yes that's possible.

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