Name and save files with current date & time

[Guest]

Hi,

I've automated my filtering task by using macro; and this macro would help
me to create new files to place the results of the filtering task. But I
would also wish that the macro could save those created files by using the
current date & time!

Could this be done???
Anyone out there has any ideal of how to get this thing done????
Please advice........

Thanking in advance.

 

[NickHK]

Combine .Save/.SaveAs with the constructed filename. Something like:

?format(now(),"yyyymmddhhmmss")
20070625154701
?format(now(),"yyyy-mm-dd hhmmss")
2007-06-25 155327
Also see the help for "User-Defined Date/Time Formats (Format Function)"

Of course, if you are saving more frequently than 1 time/second, you will
have to add finer time resolution or some kind of additional variance.

NickHK

 

[Guest]

Hi Nick,

Thanks for your reply but I still have something not so sure about the code!!
Now, I've the code as below:-

ChDir "C:\Documents and Settings\MY Tan\Desktop\FilterRecords_(To Other
Files)\Vendor_Files"
ActiveWorkbook.SaveAs Filename:=myCriteria, FileFormat:=xlNormal
ActiveWorkbook.Close

For the code above, I just save the file with the name of vendor holds by a
variable, myCriteria. So, now how would I have to combine the variable with
the current date & time??? I had tried out the sample you gave but the code
generated a debug........!!!!

Any advice...????
Thanking in advance

 

[NickHK]

Jac,
Yes, it was just to show you the syntax. May be something like:
Dim MyCriteria as string

MyCriteria ="SomeFilename-" & format(now(),"yyyymmddhhmmss") & ".xls"

NickHK

 

[Guest]

Hi Nick,

If I'm not mistaken, based on the statement below; "SomeFilename" will be a
fixed text, right????
MyCriteria ="SomeFilename-" & format(now(),"yyyymmddhhmmss") & ".xls"

But I actually want for the filename is something like this;
VendorName_DateTime, and there could have different sheets having the
different vendor' names!!!

I have tried to modify the statement as shown below but it couldn't work!!!
Filename:=myCriteria & Format(Now(), "yyyymmdd-hhmmss")

Any advice on how to solve it???
Thanking in advance.

 

[NickHK]

You didn't show how you were building your filename previously, so I assumed
you had that part.

It's up to you how you decide what value the "VendorName" part show be, but
obviously the file can only have 1 single name.

NickHK

 

[Guest]

Hi Nick,

Sorry if I didn't make the thing clear at first!!

What actually I want for the filename is like below:-
- If the file is regarding ABC vendor --> ABC_20070627-120906
- If the file is regarding DEF vendor --> DEF_20070627-1201006
- If the file is regarding GHI vendor --> GHI_20070627-121206

Could it be done??????

 

[NickHK]

Yes, but how do you determine which vendor the file regards ?
I cannot tell you that.

Dim FileName as string
'However you decide the vendor
FileName =<VendorName>

FileName =FileName & "_" & Format(now(),"yyyymmdd-hhmmss") & ".xls"

NickHK