I have the following VBA in workbook (Private Sub Workbook_BeforeSave(ByVal
SaveAsUI As Boolean, Cancel As Boolean)
ActiveWorkbook.SaveAs Filename:="Galashiels Stock as of " & Format(Date,
"dd-mm-yy") & ".xls"
End Sub)

How do I add the time into the text??

Regards


Mark

Use the Now function (returns current date and time) instead of Date
(which only returns the current date), and modify your format string to
include the time part:

ActiveWorkbook.SaveAs Filename:="Galashiels Stock as of " & Format(Now,
"dd-mm-yy hh:nn:ss") & ".xls"

HTH,
Nikos

Hi, Put in the new code but had to take away symbols":" as they are not
allowed in filename so when replaced with "-" it saves but does not recognise
a date, saves as 00-00-00 in the time part.

Any ideas??


M

"Nikos Yannacopoulos" wrote:

Use the Now function (returns current date and time) instead of Date
(which only returns the current date), and modify your format string to
include the time part:

ActiveWorkbook.SaveAs Filename:="Galashiels Stock as of " & Format(Now,
"dd-mm-yy hh:nn:ss") & ".xls"

HTH,
Nikos

santaviga wrote:
Hi, Put in the new code but had to take away symbols":" as they are not
allowed in filename so when replaced with "-"
Good point, I should have thought of that!



it saves but does not recognise
a date, saves as 00-00-00 in the time part.
This is funny, it shouldn't... care to re-post your modified code?

Nikos

Here it is

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
ActiveWorkbook.SaveAs Filename:="Galashiels Tea Fund " & Format(Date,
"dd-mm-yy hh:mm:ss") & ".xls"
End Sub

Thanks

"Nikos Yannacopoulos" wrote:



santaviga wrote:
Hi, Put in the new code but had to take away symbols":" as they are not
allowed in filename so when replaced with "-"
Good point, I should have thought of that!



it saves but does not recognise
a date, saves as 00-00-00 in the time part.
This is funny, it shouldn't... care to re-post your modified code?

Nikos

Mark,

Replace Date with Now in your code.

Alan

santaviga wrote:
Here it is

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
ActiveWorkbook.SaveAs Filename:="Galashiels Tea Fund " & Format(Date,
"dd-mm-yy hh:mm:ss") & ".xls"
End Sub

Thanks

"Nikos Yannacopoulos" wrote:



santaviga wrote:
Hi, Put in the new code but had to take away symbols":" as they are not
allowed in filename so when replaced with "-"
Good point, I should have thought of that!



it saves but does not recognise
a date, saves as 00-00-00 in the time part.
This is funny, it shouldn't... care to re-post your modified code?

Nikos

I take it you will get rid of the colons ( : ) in the format string, as
you said they will not be accepted in the filename. Apart from that,
your time part of the format string reads:

hh:mm:ss (or hh-mm-ss)

whereas it should be

hh:nn:ss (or hh-nn-ss)

as per my original post. It wasn't a typing mistake on my part, Excel
indeed uses n rather than m for minutes (in order to be able to
differentiate from m which is used for month!).

If that fails too, maybe you have non-english (US or English) regional
settings, which use different letters? Check the proposed time formats
in Language and Regional Settings (from the Control Panel) to see what
letters are used.

HTH,
Nikos