.saveas issue. Overwrite existing file?

[nig_44]

Hi All

I am having a minor issue with a program I am working on. The program has a "save as" option that allows the user to save the file with a file name provided by a cell in the work sheet. There are no problems as long as the file doesn't already exist. If the file exists I get the expected error message "A file named " " already exists in this location. Do you want to replace it? Yes No Cancel". Picking yes works fine. How can I use the No button to open the save as dialog and the Cancel button to close the message and not save?

Here is the code

Please Login or Register  to view this content.

[TMS]

Maybe use Dir to check if the file exists before trying to save it:

Please Login or Register  to view this content.

Regards, TMS

[PingPing]

Unfortunately, the SaveAs method of the Workbook object does not return a value that you can use during run-time. Additionally, trying to handle a "No" or "Cancel" differently by catching an exception also fails since the same Err.Number (1004) is returned by either event. So, I don't think you can use the SaveAs method to do what you want.

Instead, You could use the GetSaveAsFilename method of the Application object. This method returns a string (the full path of the proposed file name). You could then write some code to deal with this string, eg. using the methods of the FileSystemObject from the Scripting.Runtime library ("Microsoft Scripting Runtime" in Tools > References of the VBA IDE).

[nig_44]

Thanks Guys.