Automatically Generate a unique file name...

[Ian L]

I have an excel (XP version) application in use on a
network. The app is being run by 50+ different users,
and currently they output the final result sheet to a
printer on completion.

I would like to save some trees and instead of printing
the output, I would like to save it to a folder on the
network. The problem is, each user prints between 5 &
10 of these sheets per shift.

Not all of the users are computer savvy, so in order to
avoid everyone saving their files over everyone elses, I
would need to write a macro that would save the file
automatically to a specific folder. At the same time it
would also need to generate a unique file name for each
individual user!

Does anyone know if this is possible, and can you give me
any pointers please?

Thanks in advance.

Ian

 

[Bob Kilmer]

some ideas

GetTempName Method of the FileSystemObject
(Reference Microsoft Scripting Runtime - scrrun.dll)

http://msdn.microsoft.com/library/en-us/vbenlr98/html/vaobjfilesystemobject.asp?

http://support.microsoft.com/?id=kb;en-us;Q195763

http://www.mvps.org/vbnet/code/fileapi/gettempfilename.htm


I sometimes use Format() to create file names that change by the second. It
has the advantage, if it matters, that the file names are numbered
sequentially by date and time:

fn = "MyFile_" & Format(Now(),"yyyymmddhhmmss") & ".ext"

You could also make creative use of Rnd().

Bob Kilmer

 

[Tom Ogilvy]

Thisworkbook.SaveAs "X:\SpecialFolder\ & "ABC" & _
format(now,"yyyymmdd_hhmm") & ".xls"

Regards,
Tom Ogilvy

 

[Ian L]

Thanks Tom & Bob. That helps a lot.

Ian ;o)