Create copy of .xlsm as .xlsx on save

Question

I am trying to create backup copy of workbook .xlsm and save it as .xlsx

due to the same issue as here: Run time error '1004': Copy method of worksheet class failed - Temp file issue I cannot use SaveCopyAs along with changing format of file

My workaround was to

create new copy of .xlsm file
open this new copy
save it as .xlsx
Close .xlsx file
Remove file from step 1

this is my code

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    On Error GoTo ErrorHandler:
    'define variables
    Dim backupfolder As String
    Dim strFileName As String
    Dim xlsxStrFileName As String
    Dim fullPath As String
    Dim xlsxFullPath As String
    Dim wkb As Workbook

    'get timestamp
    dt = Format(CStr(Now), "yyyymmdd_hhmmss")

    'construct full path to backup file which will be later converted to .xlsx
    backupfolder = "c:\work\excel macro\delete\"

    strFileName = "Test_iz_" & dt & ".xlsm"
    fullPath = "" & backupfolder & strFileName

    xlsxStrFileName = "Test_iz_" & dt & ".xlsx"
    xlsxFullPath = "" & backupfolder & xlsxStrFileName

    ActiveWorkbook.SaveCopyAs Filename:=fullPath

    Set wkb = Workbooks.Open(fullPath)

    wkb.Activate
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs Filename:=xlsxFullPath, FileFormat:=51 'saves the file
    Application.DisplayAlerts = True
    'Application.Wait (Now + TimeValue("00:00:03"))
    ActiveWorkbook.Close
    Kill fullPath
    Exit Sub

ErrorHandler:
    MsgBox "An error occured " & vbNewLine & vbNewLine & Err.Number & ": " & Err.Description
    MsgBox "Backup saved: " & xlsxFullPath
    ActiveWorkbook.SaveAs Filename:=fullPath

End Sub

My problem is that i always end up in ErrorHandler, even though i got expected result

When i comment out line 2

On Error GoTo ErrorHandler:

error Run-time error '91': Object variable or With block variable not set on Debug, it points to row with the code

wkb.Activate

and .xlsm file is not deleted

I guess the problem is that when i create new copy of xlsm file and save it, this entire code would be executed one more time and that issue is there somewhere, but i cannot find it. Thank you

Try adding Application.EnableEvents = False before the line where you save the backup copy (remember to set it back to True). — Excel Developers

G. Jonathan G. Jonathan · Accepted Answer · 2018-03-12T12:23:30

This worked on my computer:

Sub Workbook_BeforeSave()
On Error GoTo ErrorHandler:
'define variables
Dim backupfolder As String
Dim strFileName As String
Dim xlsxStrFileName As String
Dim fullPath As String
Dim xlsxFullPath As String
Dim wkb As Workbook

'get timestamp
dt = Format(CStr(Now), "yyyymmdd_hhmmss")

'construct full path to backup file which will be later converted to .xlsx
backupfolder = "c:\work\excel macro\delete\"

strFileName = "Test_iz_" & dt & ".xlsm"
fullPath = "" & backupfolder & strFileName

xlsxStrFileName = "Test_iz_" & dt & ".xlsx"
xlsxFullPath = "" & backupfolder & xlsxStrFileName

ActiveWorkbook.SaveAs Filename:=fullPath, FileFormat:=52
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=xlsxFullPath, FileFormat:=51 'saves the file
Application.DisplayAlerts = True
ActiveWorkbook.Close
Kill fullPath
Exit Sub
ErrorHandler:
MsgBox "An error occured " & vbNewLine & vbNewLine & Err.Number & ": " & Err.Description
MsgBox "Backup saved: " & xlsxFullPath
ActiveWorkbook.SaveAs Filename:=fullPath
End Sub

Cheers,

Jonathan

Create copy of .xlsm as .xlsx on save

1 Answers