Date into filename noe I need time also

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
 
santaviga said:
Hi, Put in the new code but had to take away symbols":" as they are not
allowed in filename so when replaced with "-"
Click to expand...
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.
Click to expand...
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
 
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
Click to expand...
 
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
 
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
 
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
 
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
 
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
 
Thanks Alan, Bob must be offline not responded to my previous msg.


Thanks again, very much appreciated

Mark
 
Back
Top