Use VBA to change a filename while using SaveAs

Excuse the difficult to understand title; I had a tough time trying to summarize what I would like to do. Hopefully, I can explain it better in here.

I have set up a command button with VBA code to automatically save a file based on the date of the file. In the code below, you will see that the save as prompt will save the file in the folder named for the year, and will name the file based on the date found in cell A1 (for example, on 9/14/2007, the file will be saved in the 2007 folder, and will be named 09-14).

Please Login or Register  to view this content.

This code works fine, but I would like to make one addendum: although this doesnt happen frequently, it IS possible that on a date, two separate files may be created. I would like excel to automatically assign a different name to the second file (for example 09-14a) to differentiate them.

Can anyone point me in the right direction?

Hi

You will have to modify this somewhat, but it may get you going.

Please Login or Register  to view this content.

Basically it tests for the existence of the current file name, and if it exists, adds an a, then b, then c... until it finds a name that doesn't exist.


HTH

rylo