Excel Saving unwanted Backup Copies upon Open/Close

Question

I'm having some issues where my Access project will open an Excel workbook, delete the first row, then save and close it and repeat 5 times. My only problem is every single time it runs it always exports with backup copies.

Example: File1.xls Backup of File1.xls also gets saved too for some reason. Any idea what I am doing wrong? Is it making the backup due to some kind of write protection f the open file?

Public Sub RemoveFirstRowExcel(SSFile As String)
On Error GoTo Exit_Proc
Dim xlApp As Object
Dim xlSheet As Object
Set xlApp = CreateObject("Excel.Application")
Set xlSheet = xlApp.Workbooks.Open(SSFile).Sheets(1)
With xlApp
'.Application.Sheets(SSSheet).Select
.Application.Rows("1:1").Select
.Application.Selection.EntireRow.Delete
.Application.ActiveWorkbook.Close SaveChanges:=True
.Quit
End With
Exit_Proc:
Set xlApp = Nothing
Set xlSheet = Nothing
End Sub

Mark Fitzgerald Mark Fitzgerald · Accepted Answer · 2014-04-25T00:55:22

There is a boolean read-only workbook property Workbook.CreateBackup. It's set in the SaveAs dialogue by clicking the Tools button (next to the Save button), General Options then checking the Always create backup box.

CreateBackup is also an optional argument in the Workbook.SaveAs method.

You can reset the property to false by saving the workbook as itself with checkbox cleared although you will see the usual File Exists warning.

Or you could adapt the following to fit your code:

Sub SaveAsDisableBackup()
If ActiveWorkbook.CreateBackup = True Then
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs _
        Filename:=ActiveWorkbook.Path & "\" & ActiveWorkbook.Name _
        , CreateBackup:=False
    Application.DisplayAlerts = True
End If
ActiveWorkbook.Close (True)
End Sub

Backups created by virtue of Workbook.CreateBackup = True are saved with a .xlk extension but you mentioned them being saved as Backup of File1**.xls** so it's possible that the Workbook_BeforeSave event is being used to create the backup.

Before modifying the source workbook, check with the owner/author.

Excel Saving unwanted Backup Copies upon Open/Close

1 Answers