Automatic file saving from VB6 code

My VB6 application reads from data stored in an Excel spreadsheet, modifies the data once a week, and then replaces the spreadsheet with the modified one.

In the code (below):
When the save statement is executed, the system querys the user if he wants to replace the old file with this new one. If the user says “No”, an error is generated that really is not an error. I use iErr to detect this, and skip over the error message in the Error Handler.

In fact, normally, the user does not say “No”. The user finds that “do you want to replace” system-generated message annoying. He has already click a “SAVE” command button, which calls the routine in the code below.

Question: How can I accomplish that file save with an automatic “Yes” answer to the query, without bugging the user with that query?

One way around this would be to increment an “index number” as part of sWorkbookName, so that every save is a brand new file. That would require me to establish some sort of archival storage, store the index number in an “ini” file, use that to call up last week’s file, then write this week’s file back to the archive folder with an incremented index in its name.

I’d rather not go that route, if there was a straight-forward way to automate “yes” to the “replace?” query.

Note: oXLApp.Quit unlocks the saved file. Otherwise it is saved with a read-only status. The user doesn’t have to know about that. I need it during debugging.

On Error GoTo ErrorHandler
iErr = 1
Set oXLApp = CreateObject("Excel.Application")
Set oXLBook = oXLApp.Workbooks.Add
Set oXLSheet = oXLBook.Worksheets(1)
sWorkbookName = "DataPlayers.xls"
'
With oXLApp
'
' Build the spreadsheet
'
End With ' oXLApp
'
iErr = 2 ' skips over "no save" response to quety
oXLBook.SaveAs App.Path & "\" & sWorkbookName, FileFormat:=iXLVersion
oXLApp.Quit
Set oXLApp = Nothing
Set oXLBook = Nothing
Set oXLSheet = Nothing
Exit Sub
'
ErrorHandler:
    If iErr = 1 Then
        sErrMsg = "Error in SavePlayersData"
        MsgBox sErrMsg, vbCritical + vbOKOnly, "ERROR"
    End If
    Set oXLApp = Nothing
    Set oXLBook = Nothing
    Set oXLSheet = Nothing
End Sub ' SavePlayersData

Select allOpen in new window