Creating a New Suffix for a File Each Time Code is Run

[Swoootie]

Hi all,

I have a piece of code that runs and creates an output Excel sheet that currently has the run date as a suffix for the automatically generated file name. However, I now sometimes have to run this report multiple times in one day, which currently means overwriting the previously run spreadsheet (whereas I would like to keep both versions of the ruin output).

Is there a way that I can create some kind of counter for the number of times the report is run and add that as a suffix for the file name? At the moment the only way I can think to do this is to set up code that looks at the file name containing the date field and adds 1 to every number that is added as a suffix, but this doesn't seem very efficient...

I would be grateful for any help.

Thanks</SPAN>

 

[Joe4]

Why not include a date/time stamp in your file names? Then you are guaranteed to have unique file names.
Something like:

myFileName = "Suffix_" & Format(Now(), "yyyymmdd_hhmmss") & ".xls"

 

[Swoootie]

That worked a treat! Thank you for your help.