SaveCopyAs

B

broogle

Hi, I am running a code

ActiveWorkbook.SaveCopyAs Filename:= _
"C:\Temp" & "\" & Range("c10") & Range("c7") & ".xls"

Unfortunatelly, everytime I run it, it will override the previous file
without saying that the file name already exist. Tried to use
Application.DisplayAlerts = True, it didn't work. Any idea ?

Thanks
 
F

Fredrik Wahlgren

broogle said:
Hi, I am running a code

ActiveWorkbook.SaveCopyAs Filename:= _
"C:\Temp" & "\" & Range("c10") & Range("c7") & ".xls"

Unfortunatelly, everytime I run it, it will override the previous file
without saying that the file name already exist. Tried to use
Application.DisplayAlerts = True, it didn't work. Any idea ?

Thanks

You need to check if the file exists in your code before your SaveCopyAs
command.

/Fredrik
 
D

Dave Peterson

Using Frederik's suggestion:

Dim myfilename as string
dim resp as long
myfilename = "C:\Temp" & "\" & Range("c10") & Range("c7") & ".xls"

resp = vbyes
if dir(myfilename) <> "" then
resp = msgbox (Prompt:="Overwrite existing file?", buttons:=vbyesno)
end if
if resp = vbyes then
activeWorkbook.SaveCopyAs Filename:= _
"C:\Temp" & "\" & Range("c10") & Range("c7") & ".xls"
else
msgbox "not saved as copy!"
end if

Anther option may be to just add something unique to the filename each time:

myfilename = "C:\Temp" & "\" & Range("c10") & Range("c7") _
& format(now,"yyyymmdd_hhmmss") & ".xls"

Ps. I think I'd fully qualify those ranges.

with activeworkbook.worksheets("Sheet1")
myfilename = "C:\Temp" & "\" & .Range("c10") & .Range("c7") & ".xls"
end with

Just in case the activesheet isn't the correct one.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top