PDA

View Full Version : Time Capture To Log Files using VBA



rangudu_2008
10-23-2008, 11:55 AM
I developed an excel file in which various users store their daily processed work (which is updated on a daily basis). There's a particular sheet in this workbook (which i named SWSR) in which computations are done for each work done - split up into various units each having specific cycle times. Also, start & end times are captured.

Infact, the information on this sheet is used for various kinds of calculations. The start/end times are captured for any user by clicking the buttons (refer screenshot attached). This information is collated together on a fortnightly/monthly basis for computing the total productive units of all users individually as well as a whole group put together. This collation is done from another master file & data is consolidated into it automatically.

My workbook has various data validations & lots of formulas for cycle time computations. Since the SWSR sheet is common for all users & since duplication is done while consolidating data into the master workbook, i'm thinking of transferring this common sheet to the master file into which productivity of all users needs to be captured.

I'm exploring the possibilities on how this can be established, inorder to

Individual files become smaller in size;
Avoid duplication of data into the master file;
Consolidate total productivity data easily.Inorder to incorporate the SWSR sheet commonly for all users, i'm thinking of establishing the idea of a log-file based storage used to capture & store the start/end times for all users - which should be done while their individual workbooks are opened & closed.

My question here is,
"How can time capture be established & timestamps be written to a log file while opening/closing Excel workbooks"?

These timestamps should be captured in some VBA recognizable format in such a way so that they can be easily used while consolidating each individuals' total productivity.

I thought of doing it this way so that all the above 3 conditions are met easily.

In my current scenario, i have 14 such master files (totally for 200 users) where all individual data is consolidated. These master files along with another set of 14 workbooks (which are used for different purpose) are linked to a BIG master file.

I need to address some storage issues here, as each individual file is about 1.5 MB & the master file is about 3.5 MB in size. Currently, users create new individual files every month along with the master file for that month. Altogether, the total storage for 200 users comes to about 5 GB.

Do anybody have better suggestions than my idea to establish my requirements?

Ranga

rangudu_2008
10-24-2008, 11:09 AM
I've attached a sample template of the file which i designed for capturing the daily productive work of users. Each user maintains a separate copy & uses it for a month.

All these productivity data from the SWSR sheet is consolidated into another master file. There are 14 such master files which along with another set of 14 other master files (which are used for a different purpose) are linked to the BIG master file.

When i change the reporting month in the SWSR sheet, the data will get updated automatically for that month. But while consolidating the productivity, i need to view all months data together.
I wanna modify this file in such a way that an user can use the same file permanently.

I've no problems with the master files, my problem is in what can i modify the individual files so that each user can use the same file each month?
How can i make modifications appropriately to the SWSR sheet so that the productivity data of all months can be viewed for a particular user from the master file while consolidation is done? Any suggestions?Ranga

mdmackillop
10-25-2008, 04:01 AM
How about writing to a simple text file.

Sub LogFile()
Dim LogData As String
Dim LogFile As String

LogFile = "C:\AAA\log.txt"

LogData = ActiveWorkbook.Name & vbTab & _
Environ("Username") & vbTab & _
Now

Open LogFile For Append As #1
Write #1, LogData
Close #1
End Sub

rangudu_2008
10-28-2008, 06:57 PM
Hi mdmackillop,
How about modifying the above code in such a way that the captured data is sent to an excel sheet (instead of text file)? I need it in such a way inorder to make some further manipulations in the same sheet.

Also in the code where the data is appended to the file, i need to store only the timestamp when the file is opened for the first time (on any day) & the timestamp when the file is closed for the final time (for that day). I need not capture all the timestamps if any particular user opens & closes the files many times during the day. I only need the user name & the initial & final timestamps.

Ranga

mdmackillop
10-29-2008, 02:33 PM
I've attached my logfile setup. You need to set initial values.


Option Explicit
Private Sub Workbook_Open()
Dim wbLog As Workbook
Dim wsLog As Worksheet
Dim Tgt As Range
Set wbLog = Workbooks.Open("C:\AAA\Logfile.xls")
Set wsLog = wbLog.Sheets(1)
Set Tgt = wsLog.Cells(Rows.Count, 2).End(xlUp)
If Int(Now) > Int(Tgt) Then
Tgt.Offset(1, -1) = Environ("Username")
Tgt.Offset(1, 0) = Now
End If
wbLog.Close True
End Sub

Private Sub Workbook_Close()
Dim wbLog As Workbook
Dim wsLog As Worksheet
Dim Tgt As Range
Set wbLog = Workbooks.Open("C:\AAA\Logfile.xls")
Set wsLog = wbLog.Sheets(1)
Set Tgt = wsLog.Cells(Rows.Count, 4).End(xlUp)
If Int(Now) > Int(Tgt) Then
Tgt.Offset(1, -1) = Environ("Username")
Tgt.Offset(1, 0) = Now
Else
If Now > Tgt Then
Tgt.Offset(, -1) = Environ("Username")
Tgt = Now
End If
End If
wbLog.Close True
End Sub

rangudu_2008
10-31-2008, 09:30 AM
Hi mdmackillop,
I've a few more clarifications & modifications to make to suit my needs.

Is it possible to add the path of the file that is opened by the user instead of just ActiveWorkbook.Name before the name column in the log file?
Since the productivity data from the SWSR sheet is consolidated into another master file, (as there are 14 such master files) i'll need to use these timestamps for further computation. Once the master file is updated, these timestamps have to be cleared off. In other words, these timestamps exist in the till the next update of the master file & will be cleared off after updation is done.
One of my major computations with the captured time stamps is to compute the total work time for any user. While computing this, i might need to compute across 24 hrs. i.e., when an user starts working from 2pm till past midnight, the start & end timestamps would indicate different days. In my master file, while computing the total time for any user, this day difference should be taken care of. How can this be done?

I tried running your code (before posting this reply) to check whether the start/end times are captured correctly but i came across 2 small bugs - Refer screenshots.

I know that the 2nd bug can be fixed easily, but i've come across the 1st bug while working with other macros aas well. I should not let this happen, since i need to update the master file properly without missing out any information.

Ranga

mdmackillop
10-31-2008, 10:03 AM
Check out Path in the Help file. We are here to assist, not do do all your work.
Please suggest a logical method yourself for the 24 hour problem, then it can probably converted into code.
There may well be problems with multiple users saving to one workbook, which is why I suggested a txt file. A database file would probably be more foolproof, the most secure way to save your data, and the most useful for analysis.

rangudu_2008
10-31-2008, 07:12 PM
In general, to compute the total time for any user, we need to split the date part from the timestamp that is captured. To compute the total time across 2 diff. dates, it might be required to check the date part of the timestamp. A function has to be developed to do the date/time split from the captured timestamps.

I don't mind using an database file (.mdb) instead of an excel file to capture & store the start/end times. I have another code module which i can incorporate here - one which exports data from an (.mdb) file to excel.

My only concern is, how can the table which stores timestamps be cleared off? (Clearing off the data can be done once in 30 days - but it should not consume much time)

Ranga

rangudu_2008
11-09-2008, 05:03 AM
Here are a few questions for which i'm trying to find solutions:

1. Capturing Timestamps
How can the captured timestamp data for any user be appended to a database table based on the condition that Only the timestamp when the file is opened for the first time (on any day) & the timestamp when the file is closed for the final time (for that day)?

If any particular user opens & closes the files many times during the day, i need not store such timestamps generated in the database. I only need the user name & the initial & final timestamps that were generated during any particular day. In other words, the path of the source file (which the user opens), the username (Environ("Username")) & the timestamps when the file was opened for the first time (on any day) & the timestamp when the file was closed for the final time (for that day) need to be stored in a table.

Those timestamps that are generated if any particular user opens & closes the files many times during the day, need not be captured & stored. These captured timestamps will be used for computing the total time for which a user has worked on any day.

2. Computing total time for an User
To compute the total time for any user, the date part from the initial & final timestamps that is captured needs to be split. This date part needs to be checked and if its the same then the difference between the times will be computed to get the total time. The same logic applies when date parts of two timestamps are different. In this case, we need to compute the total no. of hrs till midnight of the earliest date & add the remaining hrs past midnight.

Care needs to be taken here as these computations have to be done automatically for all users (daily basis). I'm thinking of having 12 tables in the database - one for each month. The results of these daily computations may need to be stored in another set of 12 tables. The 2nd set of 12 tables also have a specific structure which i'm currently deciding upon.

3. Reporting Interface
I need a reporting interface in Excel for reporting the total times & the work performance of users computed via this SWSR file which i need to report every fortnight or at the end of the month.

How to query out stored timestamps from a particular month's table in the database & how the intermediary computations be carried out in excel?

The total time computation needs to be done based on demand - meaning that the total time for all users needs to be computed & stored in a table via a query (or set of queries). I can take care of the reporting part since i already do have the base code for exporting results of a database table as excel sheets.
This reporting will be done atleast twice in a month.
There are some additional computations that i'll need to make based on demand from the data captured from the users for which i'm currently exploring the requirements.

Can anyone suggest me any solutions for the above questions?

Ranga