Date/time Stamp?

[Bowes813]

Anyone know a date time stamp for a macro. I have a file that i
created in an old version of excel. I currently have a macro tha
opens it and does "save as" change the type to "Excel Workbook" i
another folder. The reason is cause access cant read the old version.
I want to add a date/time stamp to the updated file so I know it is th
most up-to-date. Anyone got any ideas?

Bowe

 

[STEVE BELL]

I like to use a date string = yymmdd

Dim dt As String, wbn as string

dt = Format(Year(Date), "yy") & Format(Month(Date), "mm") &
Format(Day(Date), "dd")
wbn = "MyWorkbook" & dt

or
wbn = dt & "MyWorkbook"
' this auto sorts your workbooks by date

 

[Harald Staff]

Hi Steve

No. No no no. Format(Month(Date), "mm") will always return 01 and
Format(Year(Date), "yy") will return 05 until year 2193.

Format(Date, "yymmdd")

HTH. Best wishes Harald

 

[STEVE BELL]

Harald,

My bad!

Started playing with it after I sent it in and couldn't figure out why it
didn't work just right.
Changed it to your setting and it worked...

Can you help me understand why it didn't work...

thanks...

 

[Guest]

CTRL + ; is used for Date Stamp

CTRL + SHFT + : is used for Time Stamp

I don't believe there is a short-cut key that does it together

GH.

 

[Piranha]

Hi,
Keyboard shortcut to stamp the time and date in a cell, press
Ctrl + ;
then spacebar
then Ctrl + Shift + ;.
Thx
Dave

 

[Bowes813]

ok, here is the script I am using to save a copy of the xls file in a
archive. I'm just starting to use VBE and macros and stuff so I reall
don't know what you mean by adding strings and Dim stuff. Could yo
show me how to insert the date stamp you gave me into this code:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 6/2/2005 by
'

'
ChDir "L:\"
Workbooks.Open Filename:="L:\pipeline.xls"
Workbooks.Open Filename:="L:\trades.xls"
ChDir "Y:\0008 ARCHIVE\Pipeline Dated"
ActiveWorkbook.SaveAs Filename:="Y:\0008 ARCHIVE\Pipelin
Dated\trades.xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWindow.WindowState = xlNormal
ActiveWindow.Close
ActiveWorkbook.SaveAs Filename:="Y:\0008 ARCHIVE\Pipelin
Dated\pipeline.xls" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWindow.Close


End Su

 

[Harald Staff]

Something like

Workbooks("trades.xls").SaveCopyAs "Y:\0008 ARCHIVE\trades_" & _
Format(Date, "yymmdd_hhmm") & ".xls"

HTH. Best wishes Harald