Error When Saving 2

[cghoga]

Hello everyone,

In my Excel VBA code I have the following statement to save the active workbook -

ActiveWorkbook.SaveAs Filename:= _
"\\Fs3\is\Project_Management\Time_Tracking\" & lanid & " " & Format(Date, "MMDDYYYY") & ".xls"

If there is a file that already exists at this location with the same name I get the prompt asking me do I want to save over the existing copy. If I click Yes it continues to the next step in the sub routine which is good. The problem is when I click No or Cancel it exits the sub routine.

I need to be able to say No or Cancel and it proceed to the next line of code.

Any help is greatly appreciated.

If I have been unclear, I apologize, please let me know what I need to provide.

Thanks!

 

[SkipVought]

Hi,

Check out the Application.DisplayAlerts method. First assign FALSE, save as, then assign TRUE.

Skip,

_[red][/red]

 

[cghoga]

Thanks Skip,

If I do this there is no warning that a file already exists.

I really need to provide the option whether to overwrite.

Am I missing something?

Thanks,

Cary

 

[SkipVought]

check out GetOpenFilename Method.

Skip,

_[red][/red]

 

[SkipVought]

or GetSaveAsFilename Method

Skip,

_[red][/red]

 

[fumei]

On Error Resume Next?

This is a glaring example of the differences between Word and Excel.

ActiveDocument.SaveAs Filename:="c:\yaddayadda.doc"

will save it whether there is a file already named that, or not. No error message. If you use the menu (File > SaveAs) it DOES give a message. Stating there is an existing file, what do you want to do? Overwrite? Merge? New Name? But Word VBA (2002) does NOT...it just goes ahead and overwrites.

Bad dog Word...bad dog.

faq219-2884

Gerry
My paintings and sculpture

 

[cghoga]

Thanks guys.

I ended up hardcoding this using the FileExists command.

If FileExists then prompt
Else perform the Save
End If

I appreciate your time.

Thanks!