how to preserve volatile dates (today(),now())? 1

[Lothmor]

greetings to all,
As I have been spying on this forum for a little while, I got much help just by reading the threads. So I thought perhaps somebody could have found a solution for my next problem.
Making every month a report in Excell I have to date it with month and year, I use the funtion today() to automatically put the date on the report. But when I need to print this report later on, the date changes (of course). My question is : Is there a way to prevent this updating without having to type it in ?
I hope somebody has an answer...
bye for now

 

[xlbo]

Dunno if this is exactly what you are looking for but:
CTRL+;
will enter the date (static)
and
CTRL+SHIFT+;
will enter the time (static)

Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[Lothmor]

Hi Geoff,
Thanks for replying. No, that's not what I'm looking for because I have to do a validation on the date and with the shortcuts it can't be done (I think). It must be automatic and preservable.
I'll keep looking for a solution and if found I'll put it on the tips.
regards
Raymond

 

[bandit600]

Geoff,

I found your post helpful, I can use this myself. Have a star on me.

bandit600

 

[TLady]

I know 100 ways to do this, but I have to know more, like are you updating Report having Access running ?
Is this is a query that gets updated ?

I am using Global module in Access where I am having
ReportPrintDate variable, which populates or not populates according to what you doing.

So, when I run Update_Excel function I have:
ExcelWK.Sheets(i).Range("W1&quot.Value = "Report Print Date: " + Format(ReportPrintDate, "mmmm, dd yyyy&quot

Want more info - ask.Good luck

TIA

 

[xlbo]

What's the process surrounding this need for a date stamp ie how does it interact with, for example opening the workbook. Does the workbook get used in between creating the report?? Is it a template or a new workbook generated each time

you can use code to enter a static date....

sub enterdate()
[A1] = format(now(),"dd/mm/yy&quot
end sub Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[Lothmor]

Sorry for leaving you alone with my question for a couple of days. On the road I don't get often the chance to go online.
My question related to a single workbook in which I every month use a new sheet to make a report and it has to bear the month and the year in capitals. I have no problem doing this even with an automated dating. My only problem is that I want to keep this automatic dated report for later print purposes but by then it occurs that the dating is not the same anymore (i.e. it has the date of the printing).
In Word you have the field Createdate that does what I expected to do in Excell but I can't find a function like that. It seemed not to be a difficult question.
But, thank you very mucht TIA, because you make me realise that I'd better study some VBA. My thanks also to you Geoff. I will open the VB Editor and try this out.
And could you send a little more help on the format. I want the date on my report as "FEBRARY 2003".
Hope to hear more and again sorry for my disappearence.
greetings
Raymond

 

[avayaman]

I do it on worsheets by assigning a cell with the =TODAY() function to display the current date. Then I create an auto close macro to copy this date into an adajcent cell. As long as you select "save changes" when you close the workbook, it works.
Here is my auto close:

Sub Auto_close()

On Error GoTo line100
Range("E11&quot.Select
Application.CutCopyMode = False
Selection.Copy
Range("E12&quot.Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

line100:
End Sub

In this case todays date is in cell E11 by defining it as =TODAY() . When the workbook closed it will place it in cell E12. When I open the workbook, the current date appears in E11 and the last date it was worked on in E12. It should be noted that this will not work if you just save the workbook, when you close it off make sure you select save changes. Paul Beddows
Avaya Implementation
Telus
Vancouver, Canada
E-mail via

http://www.vancouver.hm/email.html

 

[Lothmor]

thanks for your input Paul. It surely was helpfull in my research for a solution. As I dived in VBA, I found a wealth of possibillities. AND the ultimate solution as wel...
What I do now is the following:
The first sheet in the workbook is date named (like Jan 2003). I made then a module telling to count the sheets and copy the last one. And named it by extrating the datevalue from the name of the previous one and adding 1 month to it and reformating it to a string (mysheet.Name=format(dateserial(myyear, mymonth + 1, 1), "mmm yyyy&quot. Then I let it remove all data that had not to be replicated in the new sheet. And finaly I put the same format function as above into a variable with a little change : myvar=format(dateserial(myyear, mymonth + 1, 1), "mmmm yyyy&quot. Then I assigned the result of this variable to the cell in the new sheet where I wanted it to be : [A1] = UCase(myvar) and with the UCase as I wanted it to be in uppercase. This worked fine and gave me much more than I expected.
I thank the people who responded to my question and I hope to be helpfull for you in the future.
Kind Regards
Raymond