How to write On Error code for saving a file if it already exists?

[lookingforK]

Hi,

I am trying to export a record from Access 2007 to Excel spreadsheet.

I use the following code:

[COLOR=black][FONT=Verdana]...[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]wkbDest.SaveAs strReportPath & "\Initiative Details - ID " & Me.ID, xlOpenXMLWorkbook[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]...[/FONT][/COLOR]

When running the code, if a file name already exsits, I get a popup box mentioning:

"A file named 'xlsx' already exists in this location. Do you want to replace it?

When I click the button No or Cancel, I get a Run-time error '1004':



I want to write On Error code for saving a file if it already exists.

Something like:

[COLOR=black][FONT=Verdana]On Error GoTo chkthere_error[/FONT][/COLOR]
 
[FONT=Verdana][COLOR=black]wkbDest.SaveAs strReportPath & "\Initiative Details - ID " & Me.ID, xlOpenXMLWorkbook[/COLOR][/FONT]
 
[COLOR=black][FONT=Verdana]chkthere_error:[/FONT][/COLOR]
[FONT=Verdana][COLOR=black]If [/COLOR][/FONT][COLOR=red][FONT=Verdana]{[B][COLOR=#000000][COLOR=red]Initiative Details - ID[/COLOR] [/COLOR]*.xlsx[/B] already exists [B]AND[/B] click [COLOR=red]the button [B]No[/B] or [B]Cancel[/B] on the popup box[/COLOR]}[/FONT][/COLOR][COLOR=black][FONT=Verdana] Then[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Exit Sub[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]Else [/FONT][/COLOR]
[COLOR=black][FONT=Verdana]{GoTo ...continue}[/FONT][/COLOR]
[FONT=Verdana][COLOR=black]End If[/COLOR][/FONT]

How to write the On Error code for the part {Initiative Details - ID *.xlsx already exists AND click the button No or Cancel on the popup box}?


Thank you in advance.

 

[pbaldy]

Same answer as here:

http://www.access-programmers.co.uk/forums/showthread.php?t=233820