Updated time on Cell

[sujittalukde]

The code shows digital time However I want that the code shall put the time (without clicking start time) and keep updating the same as long as the WB is open at Cell A1 of Sheet 1 whenever the WB is opened and shall delete the time before close.

Sub StartClock()
   UpdateClock
End Sub
 
Sub UpdateClock()
'   Updates the clock that's visible
 
'       DIGITAL CLOCK
       ThisWorkbook.Sheets("Clock").Range("DigitalClock").Value = CDbl(Time)
 
 
'   Set up the next event one second from now
   NextTick = Now + TimeValue("00:00:01")
   Application.OnTime NextTick, "UpdateClock"
End Sub

[Charlize]

Use the workbook_open event and change the range DigitalClock to A1 (and maybe the name of the sheet). A1 needs to be formatted as timevalue with hh:mm:ss (by code or manual).

[sujittalukde]

That I had tried but failed as I dont know VBA very will

[Charlize]

normal module : [VBA]Public nexttick
Public Sub UpdateClock()
' Updates the clock that's visible
' DIGITAL CLOCK
With ThisWorkbook.Sheets(1)
.Range("A1").Value = CDbl(Time)
.Range("A1").NumberFormat = "hh:mm:ss"
End With
' Set up the next event one second from now
nexttick = Now + TimeValue("00:00:01")
Application.OnTime nexttick, "UpdateClock"
End Sub[/VBA]the workbook_open and before_close[VBA]Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnTime nexttick, "UpdateClock", schedule:=False
ActiveWorkbook.Sheets(1).Range("A1").ClearContents
ActiveWorkbook.Save
End Sub
Private Sub Workbook_Open()
Call UpdateClock
End Sub
[/VBA]

[sujittalukde]

Thanks this is working.

[tqm1]

Dear Sir,

Your codes are great and working ver well but it slow down the speed and cursor blinks abnormaly.

How to maintain default speed?

My second question is
How to get Windows TikCount(),

I want to know how many time has passed when Windows was startd.

Thanks

[Charlize]

I think you better use some kind of scriptlanguage (.bat) and store the commands to have the time and date in that file. When windows starts, you can autorun that batfile. In excel you could probably read the contents of the file that was outputted to a file by using that .bat file and substract the time of the current time.

Example of possible batchfile outputs to log.txt. Next windows session the file gets overwrited.

echo @prompt set date=$d$_set time=$t$h$h$h > {a}.bat
%comspec% /e:2048 /c {a}.bat > {b}.bat
for %%v in ({b}.bat del) do call %%v {?}.bat
echo %date% %time% > log.txt

if you want a specific location of output, include the path (c:\logs\log.txt)

[tqm1]

echo @prompt set date=$d$_set time=$t$h$h$h > {a}.bat
%comspec% /e:2048 /c {a}.bat > {b}.bat
for %%v in ({b}.bat del) do call %%v {?}.bat
echo %date% %time% > log.txt

Where to write above codes?
Please explain more.
It seems very interesting thing.

Thanks

[Charlize]

Where to write above codes?
Please explain more.
It seems very interesting thing.

Thanks

It's a plain and simple .bat file.
- open notepad
- paste those command lines into the file
- save as (all files) startwindowslog.bat

When you put startwindowslog.bat in the autostart map of your computer, the file will execute those lines.

I believe that if you create a autoexec.bat file (way back we needed this for dos to startup and to configure your computer devices) and as last line you say call startwindowslog.bat it will also work (or you could past those commandlines to the autoexec.bat file).

This file is tested on winxp.

If you change > log.txt to >> log.txt the logfile won't be overwritten. Instead a new line with the start date and time is added.

[tqm1]

Dear Sir,

I paste following codes into Notepad,
[vba]
echo @prompt set date=$d$_set time=$t$h$h$h > {a}.bat
%comspec% /e:2048 /c {a}.bat > {b}.bat
for %%v in ({b}.bat del) do call %%v {?}.bat
echo %date% %time% > (c:\logs\log.txt)
[/vba]
and then save file startwindowslog.bat to the following location

C:\Documents and Settings\Tariq Mehmood\Start Menu\Programs

I started WindowsXP many times but no log file is created anywhere on computer.

what is wrong with my work.?
Where to locate log file?

[Charlize]

Dear Sir,

I paste following codes into Notepad,
[vba]
echo @prompt set date=$d$_set time=$t$h$h$h > {a}.bat
%comspec% /e:2048 /c {a}.bat > {b}.bat
for %%v in ({b}.bat del) do call %%v {?}.bat
echo %date% %time% > c:\logs\log.txt
[/vba] and then save file startwindowslog.bat to the following location

C:\Documents and Settings\Tariq Mehmood\Start Menu\Programs

I started WindowsXP many times but no log file is created anywhere on computer.

what is wrong with my work.?
Where to locate log file?

The directory c:\logs must exist.

[tqm1]

Dear Sir,

Following directory is present

C:\LOGS

But there is nothing in it.

Please see codes again
[vba]
echo @prompt set date=$d$_set time=$t$h$h$h > {a}.bat
%comspec% /e:2048 /c {a}.bat > {b}.bat
for %%v in ({b}.bat del) do call %%v {?}.bat
echo %date% %time% > c:\logs\log.txt
[/vba]

[tqm1]

Dear Sir,

The problem has solved, there was location problem of bat file
I put the file in following location
C:\Documents and Settings\Tariq Mehmood\Start Menu\
instead of
C:\Documents and Settings\Tariq Mehmood\Start Menu\Programs\Startup

Now everything is ok

Thanks

[Charlize]

Ok. Glad it works for you.

[tqm1]

When Windows starts then programs in STARTUP folder execute.

Is there any action that works ONSHUTDOWN event.
I mean when I shutdown my computer then datetime msut save in log file.

[Charlize]

When Windows starts then programs in STARTUP folder execute.

Is there any action that works ONSHUTDOWN event.
I mean when I shutdown my computer then datetime msut save in log file.

In the startmenu, choose run and type following text 'gpedit.msc' (without ') and hit enter. Now you'll see some +'s . In the computerconfiguration you'll see windows-configuration. When you hit the +, choose scripts. There you can add a script to be run on shutdown. If you want to run it on logoff of the user, use the user-configuration.

This will work on win xp pro. Not tested on something else. Hope it helps.

[tqm1]

Dear Sir,

What you say if I copy following codes to
Windows settings ---> scripts (startup/shutdown) --->startup

echo @prompt Set date=$d$_set time=$t$h$h$h > {a}.bat 
%comspec% /e:2048 /c {a}.bat > {b}.bat 
For %%v In ({b}.bat del) Do Call %%v {?}.bat 
    echo %date% %time% > c:\logs\log.txt 

</DIV>

Will above codes work?

[Charlize]

I'll say it in a different way :

1. Start
2. Run
3. In the dialogbox type in : gpedit.msc and hit enter
4. Two panes. In left pane choose windows setting (+)
5. On right pane choose shutdown scripts
6. right click mouse and choose properties for those scripts that needs to be executed when you shut down windows.
7. choose add and find the location where the batfile is stored on your drive and add the file to the list of scripts that you want to execute when windows shuts down.
8. probably some ok's to click to confirm everything.