CODEWITHSUNDEEP
vbaexcelxlsxxlsm
Amos
Amos

Reputation: 15

after run macro to save as xlsx file, the macro file convert to xlsx file

I want the macro to backup my xlsm file as xlsx format, still remain there after save as xlsx type. However, below coding will convert the existing xlsm file to xlsx file, causing my macro file dissapear. Any suggestion to avoid this, I want xlsx file save and close while xlsm file remain?

Sub backup()
    Application.DisplayAlerts = False
    ThisWorkbook.saveas "C:\Users\Documents\Book1.xlsx", xlOpenXMLWorkbook
    Application.DisplayAlerts = True
End Sub

Upvotes: 0

Views: 4396

Answers (2)

Plagon
Plagon

Reputation: 3138

Use SaveCopyAs.

Sub Test()
Dim wb As Workbook, wb2 As Workbook
Dim Path As String
Application.DisplayAlerts = False
Path = "C:\Users\" & Environ("Username") & "\Desktop\"
Set wb = ThisWorkbook
wb.SaveCopyAs (Path & "File.xlsm")
Set wb2 = Workbooks.Open(Path & "File.xlsm")
wb2.SaveAs Path & "File1.xlsx", xlOpenXMLWorkbook
wb2.Close
Application.DisplayAlerts = True
End Sub

This would save a copy to your desktop, but does not affect the Workbook you are working in.

Upvotes: 2

Variatus
Variatus

Reputation: 14373

ThisWorkbook addresses the workbook in which the code is located. If you wish to save the ActiveWorkbook you must address it either as ActiveWorkbook or by its name. Once you save ThisWorkbook in xlsx format the code can't continue to run. To do what you intend to do you might place the code in a third file, like an add-in, and manipulate your workbooks from there.

Upvotes: 1

Related Questions