View Full Version : Updated time on Cell
sujittalukde
06-29-2007, 01:20 AM
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
06-29-2007, 01:37 AM
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
06-29-2007, 01:45 AM
That I had tried but failed as I dont know VBA very will
Charlize
06-29-2007, 01:49 AM
normal module : 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 Subthe workbook_open and before_closePrivate 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
sujittalukde
06-29-2007, 02:01 AM
Thanks this is working.
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
06-29-2007, 04:40 AM
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.txtif you want a specific location of output, include the path (c:\logs\log.txt)
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
06-29-2007, 11:21 AM
Where to write above codes?
Please explain more.
It seems very interesting thing.
ThanksIt'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.
Dear Sir,
I paste following codes into Notepad,
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)
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
06-30-2007, 03:41 PM
Dear Sir,
I paste following codes into Notepad,
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
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.
Dear Sir,
Following directory is present
C:\LOGS
But there is nothing in it.
Please see codes again
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
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
07-01-2007, 01:57 PM
Ok. Glad it works for you.
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
07-24-2007, 12:52 AM
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.
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
07-24-2007, 03:21 PM
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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.