Date into filename noe I need time also

[Guest]

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

 

[Nikos Yannacopoulos]

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

 

[Guest]

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]

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

 

[Guest]

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

 

[acampbell012]

Mark,

Replace Date with Now in your code.

Alan

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

 

[Guest]

Great, your a genious, omly thing was I had to take out the symbols : as
would not let me save the file as these are not allowed in file names, you
have any idea how to combat??

Mark

 

[Nikos Yannacopoulos]

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

 

[Nikos Yannacopoulos]

Alan's right, I missed that.

No, you can't combat that, colons won't be accepted by the file system,
nothing you can do about it in VBA code.

Nikos

 

[Guest]

Thanks for the Help.

Another little problem I have, maybe you can help me out. Read below all I
need to do is format the cells to 0 "weeks" as bob says but dont understand
how to no options to do that in format cells.


Thanks again

Sorry Bob, little confused, can explain how to format the cell as 0 " Weeks"

Thanks


Mark

 

[acampbell012]

Mark,

You should stay with your other post. However, Bob suggestion would be
applied using Format>Cells>Custom at the bottom of the pick list.

Alan

 

[Guest]

Thanks Alan, Bob must be offline not responded to my previous msg.


Thanks again, very much appreciated

Mark