Name and save files with current date & time
 

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.

Name and save files with current date & time
 

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

"Jac" wrote in message
...
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.

Name and save files with current date & time
 

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" wrote:

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

"Jac" wrote in message
...
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.

Name and save files with current date & time
 

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

"Jac" wrote in message
...
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" wrote:

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

"Jac" wrote in message
...
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.

Name and save files with current date & time
 

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" wrote:

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

"Jac" wrote in message
...
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" wrote:

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

"Jac" wrote in message
...
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.

Name and save files with current date & time
 

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

"Jac" wrote in message
...
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" wrote:

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

"Jac" wrote in message
...
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" wrote:

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

"Jac" wrote in message
...
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.

Name and save files with current date & time
 

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" wrote:

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

"Jac" wrote in message
...
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" wrote:

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

"Jac" wrote in message
...
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" wrote:

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

"Jac" wrote in message
...
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.

Name and save files with current date & time
 

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

"Jac" wrote in message
...
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" wrote:

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

"Jac" wrote in message
...
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" wrote:

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

"Jac" wrote in message
...
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" wrote:

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

"Jac" wrote in message
...
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.