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

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

" wrote:

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

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

"Bob Phillips" wrote:

Format the cell as

0 " Weeks"

this will preserve the number if you want to use it.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

wrote in message
ups.com...
Hi try
= your-formula & " Weeks"

where your-formula is the one you wrote down
regards
Paul

santaviga wrote:
I have cells with the following formula in them
(=INT(TODAY()-INDEX('Galashiels Tea Fund'!$1:$1,1,MATCH("x",'Galashiels
Tea
Fund'!4:4)))/7) this returns a number in the cell with reference to the
date,
Is there a way I can also insert a text to go along with the number at
the
end of the formula. e.g. (6 Weeks) the 6 is generated by the current
formula
all I need is to add weeks as text.


Thanks



Mark

Mark,

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

Alan

santaviga wrote:
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

"Bob Phillips" wrote:

Format the cell as

0 " Weeks"

this will preserve the number if you want to use it.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

wrote in message
ups.com...
Hi try
= your-formula & " Weeks"

where your-formula is the one you wrote down
regards
Paul

santaviga wrote:
I have cells with the following formula in them
(=INT(TODAY()-INDEX('Galashiels Tea Fund'!$1:$1,1,MATCH("x",'Galashiels
Tea
Fund'!4:4)))/7) this returns a number in the cell with reference to the
date,
Is there a way I can also insert a text to go along with the number at
the
end of the formula. e.g. (6 Weeks) the 6 is generated by the current
formula
all I need is to add weeks as text.


Thanks



Mark

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


Thanks again, very much appreciated

Mark

" wrote:

Mark,

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

Alan

santaviga wrote:
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

"Bob Phillips" wrote:

Format the cell as

0 " Weeks"

this will preserve the number if you want to use it.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

wrote in message
ups.com...
Hi try
= your-formula & " Weeks"

where your-formula is the one you wrote down
regards
Paul

santaviga wrote:
I have cells with the following formula in them
(=INT(TODAY()-INDEX('Galashiels Tea Fund'!$1:$1,1,MATCH("x",'Galashiels
Tea
Fund'!4:4)))/7) this returns a number in the cell with reference to the
date,
Is there a way I can also insert a text to go along with the number at
the
end of the formula. e.g. (6 Weeks) the 6 is generated by the current
formula
all I need is to add weeks as text.


Thanks



Mark