MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

return time in seconds in VBA?

  Asked By: Matilda    Date: Sep 07    Category: MS Office    Views: 13223

Excel 2003
Win XP

I am trying to create a filename in VBA that incorporates the current
time in seconds. This way the VBA code can create a unique file name
each time it writes data, and doesn't need to prompt the user to
create a filename.

Is there a way to return the current time in seconds within VBA?



4 Answers Found

Answer #1    Answered By: Addison Campbell     Answered On: Sep 07

something along the lines of:

fname = "constantPartOfName" & Time * 86400 & ".xls"
ActiveWorkbook.SaveAs fname

Answer #2    Answered By: Aaleyah Khan     Answered On: Sep 07

The NOW() function returns the serialized date and time. The date is left of the
decimal, the time  is right of the decimal. Format a cell as general, then enter
=NOW() in the cell and you will see the numbers. Perhaps this will give you what
you need.

Answer #3    Answered By: Marta Kim     Answered On: Sep 07

I "assume" when you say "the current time  in seconds" you're referring
to the current  time of day, not the current date/time.
There are several ways to accomplish this.
Both use (as described before) the NOW() function to return  the
current date and time.

One technique would be to use the Format() function to strip off
the Hours, Minutes and Seconds, multiplying the Hours 3600 seconds
per hours, Minutes by 60 second pr minute and adding them all together
will give you the total seconds since Midnight.

If you chose to, you can remove the Integer portion of the NOW result
and what you have left is the decimal representation of the fractional
part of the day. (EXACTLY 12:00 noon would be .5000) You can round
this to any number of digits to achieve a nearly unique  identifier.


Dim ThisDate
ThisDate = Now
MsgBox ThisDate & Chr(13) & _
Format(ThisDate, "h") * 3600 + _
Format(ThisDate, "N") * 60 + _
Format(ThisDate, "s") & Chr(13) & _
Round(ThisDate - Int(ThisDate), 6)

and see what they look like.

Answer #4    Answered By: Shaun Thomas     Answered On: Sep 07

You can use the format() function to define what part of the now()
function result you want to include. If you use the year -> seconds
order you can sort all files ascending by filename.

sTime = Format(Now(), "hhmmss")
sDateTime = Format(Now(), "yyyymmddhhmmss")

Didn't find what you were looking for? Find more on return time in seconds in VBA? Or get search suggestion and latest updates.