Save file as non-macro enabled and delete original file.

[Kevin Rush]

Hi all,

This is kind of a follow up from previous question I had. Now that I’m happy with my level of protection of my workbook I would like to achieve the following.

Once a certain date has passed I would like the workbook to to the following.

- Delete 2x sheets Named “Parameters” & “Macro” (Macro is a splash sheet
- Save the file as a non macro enabled workbook
- Close and delete the macro enabled workbook
- open the non macro enabled workbook.

Before this file is saved a non macro enabled it will removed all formulas (I have a macro for this that I will call).

I’m choosing this method instead of just locking users out of the file.

Hope I’ve explained my self well enough and thanks for your time.

[Logit]

https://www.exceldome.com/solutions/...el-worksheets/

Please Login or Register  to view this content.

https://forum.ozgrid.com/forum/index...ithout-macros/

Please Login or Register  to view this content.

https://excelchamps.com/vba/delete-f..._Kill_Function

Please Login or Register  to view this content.

[Kevin Rush]

Thanks, but the saving doesn’t seem to work. I would like to save the new File on the same folder (path) as the macro enabled file.

[Logit]

You'll need to edit this line so the path matches where you want to save :

Please Login or Register  to view this content.

[Kevin Rush]

You'll need to edit this line so the path matches where you want to save :

Please Login or Register  to view this content.

Yeah I tried that, I set a constant call mypath which is the file path of the workbook it’s seemed to work (no error pop up) but then wasn’t saved in that folder.

[jolivanes]

Re: "Before this file is saved a non macro enabled it will removed all formulas"
Which sheets? All sheets left in the workbook?

[Kevin Rush]

Re: "Before this file is saved a non macro enabled it will removed all formulas"
Which sheets? All sheets left in the workbook?

Yes all sheet, formula just turn to values

[jolivanes]

Please Login or Register  to view this content.

The .xlsx workbook will be the active workbook after you run this.
You can't delete the .xlsm workbook from the new workbook because the .xlsx workbook has no macros.

[Artik]

It's a bit confusing, but try it

Please Login or Register  to view this content.

Artik

[Kevin Rush]

It's a bit confusing, but try it

Please Login or Register  to view this content.

Artik

Thanks I’ll try that!

[Artik]

I would prefer the answer once you've tried. Then it would be known whether the macro works as expected or not.

And please:
1. do not quote entire statements of predecessors
2. do not create new threads with the same or very similar problem.

Artik

[Kevin Rush]

That has worked, I had to move the call for formula to value and unprotect to the top but is worked great. I just need to add deleting the Command button.

Thanks so much!

[jolivanes]

The code from Post #8 did not work?

[Kevin Rush]

I havent tried yet but i will and see which is quicker. Thank you

[Artik]

The code from Post #8 did not work?

In theory, it should work. However, in practice it is a bit different. The XLSX file (created with SaveAs), although it does not have macros anymore, the macros from the XLSM oryginal file are still active until the XLSX is closed. Because macros reference sheets that have been programmatically deleted, a runtime error occurs. Only after reopening the XLSX file will it be macro-free.
In the attachment the file on which I conducted the tests. You will see the symptoms that I am talking about above when you run the Save_As_XLSX procedure. After the macro is finished, change the values anywhere in the workbook and try to save the workbook.
That's why I created such a crazy code.

Artik

[jolivanes]

How about this.

Please Login or Register  to view this content.