Text file to measure file usage

I know I can record when a file is opened by writing a
routine that triggers off the open event in This Workbook.
It writes my username to a text file as seen below:

Private Sub Workbook_Open()
Open ThisWorkbook.Path & "\usage.log" For Append As #1
Print #1, Application.UserName, Now
Close #1
End Sub

It creates a text file that reads like this:
ExcelMonkey 04/02/2005 10:21:59
ExcelMonkey 04/02/2005 10:37:04
ExcelMonkey 04/02/2005 10:38:04

However, I also want it to write when I exit the file and
calculated the amount of time I was in it. To do this I
will have to trigger another routine with a Close Event.
It will then have to open this same text file and record
the exit time and calc a duration. I twill read like this:

Me 04/02/2005 10:21:59 04/02/2005 10:22:59 00:01:00
Me 04/02/2005 10:37:04 04/02/2005 10:38:04 00:01:00
Me 04/02/2005 10:38:04 04/02/2005 10:39:04 00:01:00

Does anyone know how to do this?

In the open event write the date and time of opening to a custom document
property

In the beforeclose event, calculate the elapsed time and write to your file.

See Chip Pearson's site for information on working with document properties:
http://www.cpearson.com/excel/docprop.htm

--
Regards,
Tmo Ogilvy

"ExcelMonkey" <[email protected]> wrote in message
news:[email protected]...
> I know I can record when a file is opened by writing a
> routine that triggers off the open event in This Workbook.
> It writes my username to a text file as seen below:
>
> Private Sub Workbook_Open()
> Open ThisWorkbook.Path & "\usage.log" For Append As #1
> Print #1, Application.UserName, Now
> Close #1
> End Sub
>
> It creates a text file that reads like this:
> ExcelMonkey 04/02/2005 10:21:59
> ExcelMonkey 04/02/2005 10:37:04
> ExcelMonkey 04/02/2005 10:38:04
>
> However, I also want it to write when I exit the file and
> calculated the amount of time I was in it. To do this I
> will have to trigger another routine with a Close Event.
> It will then have to open this same text file and record
> the exit time and calc a duration. I twill read like this:
>
> Me 04/02/2005 10:21:59 04/02/2005 10:22:59 00:01:00
> Me 04/02/2005 10:37:04 04/02/2005 10:38:04 00:01:00
> Me 04/02/2005 10:38:04 04/02/2005 10:39:04 00:01:00
>
> Does anyone know how to do this?

Since your example combines the Open and Close time in the same line, you can
handle it one of 2 ways:
1) Keep your routine the way it is, and then in the Close event you would
need to reopen the file for random access, read up to the last "record",
extract the open time from it and do your elapsed time calculation, and
overwrite that last line with the updated info including the close time.
That is quite a bit of processing but could be done.
2) Simpler approach is to store the Open time in a static variable when the
book is opened and then in the Close event simply append the info for that
session in one chunk; e.g:

Private TimeOpen As Date

Private Sub Workbook_Open()

TimeOpen = Now

End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim TimeClosed As Date
TimeClosed = Now

Open "F:\XLlog" For Append As #1
Print #1, Application.UserName, TimeOpen, TimeClosed, Format(TimeOpen -
TimeClosed), "hh:nn:ss")

Close #1

End Sub

- With this approach you will not log the open time if there is an Excel
"crash" but if you don't need to worry about that it is the simpler way, I
think.

"ExcelMonkey" wrote:

> I know I can record when a file is opened by writing a
> routine that triggers off the open event in This Workbook.
> It writes my username to a text file as seen below:
>
> Private Sub Workbook_Open()
> Open ThisWorkbook.Path & "\usage.log" For Append As #1
> Print #1, Application.UserName, Now
> Close #1
> End Sub
>
> It creates a text file that reads like this:
> ExcelMonkey 04/02/2005 10:21:59
> ExcelMonkey 04/02/2005 10:37:04
> ExcelMonkey 04/02/2005 10:38:04
>
> However, I also want it to write when I exit the file and
> calculated the amount of time I was in it. To do this I
> will have to trigger another routine with a Close Event.
> It will then have to open this same text file and record
> the exit time and calc a duration. I twill read like this:
>
> Me 04/02/2005 10:21:59 04/02/2005 10:22:59 00:01:00
> Me 04/02/2005 10:37:04 04/02/2005 10:38:04 00:01:00
> Me 04/02/2005 10:38:04 04/02/2005 10:39:04 00:01:00
>
> Does anyone know how to do this?
>

Thank-you. Exactly what I needed. 2 Quick questions if I
may.

1) How do I decrease the space between the fields in the
text file?

2) When I open the excel file, I would like to create a
message box which illustrates user stats as calculated
from the text file. This will obviusly go into an Open
Event. Firstly I would like to add up all the time spent
in the file (i.e. sum of values in field 4). And lastly
count the number of occurences of my username in field
#1. I have never done calculations on text file fields.
So I am not sure how to approach this.

Thanks



>-----Original Message-----
>Since your example combines the Open and Close time in
the same line, you can
>handle it one of 2 ways:
>1) Keep your routine the way it is, and then in the Close
event you would
>need to reopen the file for random access, read up to the
last "record",
>extract the open time from it and do your elapsed time
calculation, and
>overwrite that last line with the updated info including
the close time.
>That is quite a bit of processing but could be done.
>2) Simpler approach is to store the Open time in a static
variable when the
>book is opened and then in the Close event simply append
the info for that
>session in one chunk; e.g:
>
>Private TimeOpen As Date
>
>Private Sub Workbook_Open()
>
>TimeOpen = Now
>
>End Sub
>
>Private Sub Workbook_BeforeClose(Cancel As Boolean)
>Dim TimeClosed As Date
>TimeClosed = Now
>
>Open "F:\XLlog" For Append As #1
>Print #1, Application.UserName, TimeOpen, TimeClosed,
Format(TimeOpen -
>TimeClosed), "hh:nn:ss")
>
>Close #1
>
>End Sub
>
>- With this approach you will not log the open time if
there is an Excel
>"crash" but if you don't need to worry about that it is
the simpler way, I
>think.
>
>"ExcelMonkey" wrote:
>
>> I know I can record when a file is opened by writing a
>> routine that triggers off the open event in This
Workbook.
>> It writes my username to a text file as seen below:
>>
>> Private Sub Workbook_Open()
>> Open ThisWorkbook.Path & "\usage.log" For Append As
#1
>> Print #1, Application.UserName, Now
>> Close #1
>> End Sub
>>
>> It creates a text file that reads like this:
>> ExcelMonkey 04/02/2005 10:21:59
>> ExcelMonkey 04/02/2005 10:37:04
>> ExcelMonkey 04/02/2005 10:38:04
>>
>> However, I also want it to write when I exit the file
and
>> calculated the amount of time I was in it. To do this
I
>> will have to trigger another routine with a Close
Event.
>> It will then have to open this same text file and
record
>> the exit time and calc a duration. I twill read like
this:
>>
>> Me 04/02/2005 10:21:59 04/02/2005 10:22:59 00:01:00
>> Me 04/02/2005 10:37:04 04/02/2005 10:38:04 00:01:00
>> Me 04/02/2005 10:38:04 04/02/2005 10:39:04 00:01:00
>>
>> Does anyone know how to do this?
>>
>.
>

hi,
not with a text file. we do have a number of files that we
track usage on. but we keep the data in the file on a
hidden sheet. we use a formula to calculate the time.
A word of caution. if the excel file bypassing the on
close event for any reason such as a gpf or power failure,
this will cause a lost exit entry and you may started
getting some wierd calculations, like someone in a file
for 20 hrs. if we do loose an entry, we can insert row,
adjust formula and we're calculating right again.
just thought i would pass that on to you.
good luck

>-----Original Message-----
>I know I can record when a file is opened by writing a
>routine that triggers off the open event in This
Workbook.
>It writes my username to a text file as seen below:
>
>Private Sub Workbook_Open()
> Open ThisWorkbook.Path & "\usage.log" For Append As #1
> Print #1, Application.UserName, Now
> Close #1
>End Sub
>
>It creates a text file that reads like this:
>ExcelMonkey 04/02/2005 10:21:59
>ExcelMonkey 04/02/2005 10:37:04
>ExcelMonkey 04/02/2005 10:38:04
>
>However, I also want it to write when I exit the file and
>calculated the amount of time I was in it. To do this I
>will have to trigger another routine with a Close Event.
>It will then have to open this same text file and record
>the exit time and calc a duration. I twill read like
this:
>
>Me 04/02/2005 10:21:59 04/02/2005 10:22:59 00:01:00
>Me 04/02/2005 10:37:04 04/02/2005 10:38:04 00:01:00
>Me 04/02/2005 10:38:04 04/02/2005 10:39:04 00:01:00
>
>Does anyone know how to do this?
>.
>