0

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
Community
  • 1
  • 1
Amos
  • 15
  • 1
  • 7

2 Answers2

2

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.

Plagon
  • 2,689
  • 1
  • 11
  • 23
  • It able to save the file separately without closing the existing file, but the saved file can't open with err "the file format or file extension is not valid". For savecopyas, need to insert the file format? – Amos Sep 09 '17 at 16:00
  • Actually you can only save files with `SaveCopyAs` in the same fileformat as the original. so you could create a copy, reopen and then use `SaveAs` and change the fileformat. – Plagon Sep 09 '17 at 16:37
  • Won't you need a `Kill Path & "File.xlsm"` after saving the workbook again as `Path & "File1.xlsx"`, in order to get rid of the temporary xlsm file? – YowE3K Sep 09 '17 at 19:29
  • What if my xlsm file is shared using my Onedrive, will other people encounter error when try to reopen the file – Amos Sep 10 '17 at 03:31
  • @UGP need to make sure the person I shared with has the access to the destination folder, right? – Amos Sep 10 '17 at 05:12
1

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.

Variatus
  • 14,293
  • 2
  • 14
  • 30