Remove All VBA macro and save as xlsx with current date

[sroyroy]

I am looking for a macro which will
- delete all existing macros in the workbook name.xlsm
- saves the workbook as xlsx with name-current-date.

 

[mikerickson]

SaveAs .xlsx will remove all the macros.

 

[shg]

One way (but untested)

Sub Demo()
     SaveSansMacros Workbooks("name.xlsm")
End Sub

Sub SaveSansMacros(wkb As Workbook)
    With wkb
        If Not .Saved Then
            MsgBox "File not saved. Save, then try again."
        Else
            .SaveAs Filename:=Left(.FullName, InStrRev(.FullName, ".")) & _
                              Format(Date, " yyyy-mm-dd") & "xlsx", _
                    FileFormat:=xlOpenXMLWorkbook
        End If
    End With
End Sub

 

[sroyroy]

I do not want the excel warning message to appear for conformation.

 

[James Snyder]

It's a hideous block of code, but this works:

            Application.DisplayAlerts = False       ' Suppress "SaveAs" dialog box
            Application.EnableEvents = False        ' Suppress BeforeSave event
            wkOrdInWkBk.Save
            Application.EnableEvents = True
            Application.DisplayAlerts = True

 

[shg]

MOL as James suggested,

Sub SaveSansMacros(wkb As Workbook)

    Dim bDA As Boolean

    With wkb
        If Not .Saved Then
            MsgBox "File not saved. Save, then try again."

        Else
            bDA = Application.DisplayAlerts
            Application.DisplayAlerts = False
            .SaveAs Filename:=Left(.FullName, InStrRev(.FullName, ".")) & _
                              Format(Date, " yyyy-mm-dd") & "xlsx", _
                    FileFormat:=xlOpenXMLWorkbook
            Application.DisplayAlerts = bDA
        End If
    End With
End Sub

 

[sroyroy]

thank you shg.