Log time against a job
 

Hello, I would like to create a time logging system.

Basically what I need to do is import Job numbers from a sql db into a
separate worksheet, I can use vlookups for the job numbers for us to
lookup specific lobs. This part I can do.

What I need to do is to be able to log time against that job number
(there must be a timer that records when you have pressed the start
and stop etc). They select the job number and the timer starts until
we stop it. The value must be held somewhere so that we can report on
it.

The time must be stored so that we can run a report to calc total time
spent against the job.

Any help would be greatly appreciated.

Regards,
Byron

Log time against a job
 

Have a look at the Timer function in VBA. You can use it to calculate
total time. Something like:
startTime = Timer
....
place code here
....
endTime = Timer

totalTime = endTime - startTime

HTH

wrote:
Hello, I would like to create a time logging system.

Basically what I need to do is import Job numbers from a sql db into a
separate worksheet, I can use vlookups for the job numbers for us to
lookup specific lobs. This part I can do.

What I need to do is to be able to log time against that job number
(there must be a timer that records when you have pressed the start
and stop etc). They select the job number and the timer starts until
we stop it. The value must be held somewhere so that we can report on
it.

The time must be stored so that we can run a report to calc total time
spent against the job.

Any help would be greatly appreciated.

Regards,
Byron

Log time against a job
 

I think you can use SQL-DMO Job.EnumHistory method to get what you want. One
of the return valuse is "run_duration". It looks something like this:

Dim SQLSrv As New SQLDMO.SQLServer
Dim q As QueryResults

SQLSrv.Connect "ServerName", "Login", "Password"

For Each jb In SQLSrv.JobServer.Jobs
Debug.Print jb.Name
Set q = jb.EnumHistory
For j = 1 To q.Columns
Debug.Print "(" & j & ") " _
& q.ColumnName(j) & " " _
& q.GetColumnString(1, j)
Next
Next

--
urkec


" wrote:

Hello, I would like to create a time logging system.

Basically what I need to do is import Job numbers from a sql db into a
separate worksheet, I can use vlookups for the job numbers for us to
lookup specific lobs. This part I can do.

What I need to do is to be able to log time against that job number
(there must be a timer that records when you have pressed the start
and stop etc). They select the job number and the timer starts until
we stop it. The value must be held somewhere so that we can report on
it.

The time must be stored so that we can run a report to calc total time
spent against the job.

Any help would be greatly appreciated.

Regards,
Byron