Overwriting changes to a saved excel file

[wildsyp]

Hi,

I have a bit of code I use which opens an existing Excel workbook and saves it as another workbook depending on a variable determined from a form.

When the workbook is saved, it may or may not already exist. If it doesn't exists it works fine, but if it already exists it asks me if I want to overwrite the changes. I can't seem to find a way around this.

Any help would be much appreciated.

Cheers
Paul

variablechange = "CR041220081346.xls"
    Dim xlApp As Excel.Application
    Dim xlWB As Excel.Workbook
    Set xlApp = New Excel.Application
    xlApp.EnableEvents = False
    With xlApp
        .Visible = True
        Set xlWB = .Workbooks.Open("C:\Example\Template.xls", , False)
    End With
DoCmd.SetWarnings False
    xlWB.SaveAs ("C:\Example\" & variablechange)
    xlWB.Close SaveChanges:=True
DoCmd.SetWarnings True

 

[Kafrin]

In Access the SetWarnings commands would sort this out, but you are saving in Excel so you need to use the Excel command of DisplayAlerts. So within your With clause you need:

.DisplayAlerts = False
xlWB.SaveAs ("C:\Example\" & variablechange)
xlWB.Close SaveChanges:=True
.DislpayAlerts = True

 

[wildsyp]

Kafrin,

Worked first time! Thanks for your help.

Cheers
Paul