PDA

View Full Version : Delete all macros/all VBA and save as xls/xlsx



toblju
02-27-2012, 07:46 AM
I have a workbook with a macro that delete some secret information and create another workbook and save the new workbook in a separate folder were a lot of people can reach it (without seeing the secret information). Is there any way to save the new workbook as an xls/xlsx-file (delete all VBA in it when saving the new file)???
(I have buttons connected to the macros. These buttons would have been optional to delete as well).

Thanks in advance
Tobias of Sweden

Kenneth Hobs
02-27-2012, 09:25 AM
Saving as XLSX would do that but not XLS.

frank_m
02-27-2012, 10:50 AM
Saving as an .xlsx, as Kenneth said, will get rid of all the macros.

To delete the buttons in every worksheet try:

Sub BtnDel()
'http://www.mrexcel.com/forum/showthread.php?t=515965
Dim btn As Button, ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
For Each btn In ws.Buttons
btn.Delete
Next btn
Next ws
End Sub

toblju
03-19-2012, 03:13 AM
I try to save the file as an xlsx. It works. But when I try to open it, it says that "cannot open file XXX because file format is not valid. Check if file is damaged and that file format match the files format".

What can I do?

Thanks,
Tobias.

Kenneth Hobs
03-19-2012, 05:21 AM
Do you have 2007+ which allows xlsx saving?

Jan Karel Pieterse
03-19-2012, 07:49 AM
In the SaveAs statement, you must specify the right filetype, not just change the extension of the file.

defcon_3
03-21-2012, 12:53 AM
If my understanding is correct i think its look like this:


Sub save2xlsx()

Application.DisplayAlerts = False
ChDir "C:"
ActiveWorkbook.SaveAs Filename:= _
"filenamehere.xlsx", FileFormat:= _
xlOpenXMLWorkbook, CreateBackup:=False
Application.DisplayAlerts = True

MsgBox "File Saved", vbInformation


End Sub


In that case you will always have a fixed xlsx file with fixed filename and dir.