Numbering A Saved Spreadsheet

[Guest]

I use a macro to save a query as an excel spreadsheet into a folder, the
spreadsheet is named using a formula, i.e. = format(Date(), "dd mm yy") & " -
" & [Type] & ".xls", however if two spreadsheets are saved on the same day
the second overwrites the first. Does anyone know of a way of adding an
incremental number to the spreadsheet, i.e. it looks to see if the
spreadsheet is all ready there and if it is adds '2' onto its name and if '2'
is already there it adds '3' and so on? Or any other thoughts on how to do
this, the naming HAS to be done automatically, there can be no user input.

Thanks very much.
Emma

 

[Chris]

If you are already putting the system date as part of the name, couldn't you
also put system time out to the second. That way it would have a unique
date/time stamp in file name and would not be overwritten because they would
never be exactly the same to the second.

 

[Steve Schapel]

Emma,

A couple of thoughts spring to mind.

1. Instead of the incremental numbering, you could include the time as
well as the date in the file name, so it might be...
=Format(Date(), "dd mm yy \(hh\)") & " - " & [Type] & ".xls"

2. You could make a simple table, one record, 2 fields LastSent (date)
and SerialNumber. At the same time that you do your Excel export, you
could use an Update Query to reset the values in this table. LastSent
would be updated to Date() and Serial Number would be updated to
something like...
IIf([LastSent]=Date(),[SerialNumber]+1,1)
Then, you could refer to this table for your file name, for example...
=Format(Date(), "dd mm yy") & " (" &
DLookup("[SerialNumber]","ExportSent") & ") - " & [Type] & ".xls"

Other than that, you would need to use a VBA procedure rather than a
macro to check on the existence of the named Excel files.