• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 477
  • Last Modified:

Excel Data Date/Time Log

I need a vba macro that will update an external worksheet with the date that multiple specific cells of a worksheet are filled in.  Ideally the external sheet would have a reference project title from sheet and date the specific cells are updated.

I'd like this to be portable so that it could be easily used in many worksheets all reporting to a Log Excel sheet . The one with project references  and dates. Not exactly sure how to accomplish this.
0
lucasd1973
Asked:
lucasd1973
  • 3
  • 2
1 Solution
 
SteveCommented:
For me I would "log" using either Database or Text Files

Text files are simple enough, but could be troublesome for multi-users.
Database is slightly tougher, but would work better with multiple users/files.
Either method can then be "imported" to an Excel workbook.

Are you only interested in specific cell changes? Or any change at all?
Is it every change or just the last one before close? If you log all changes you will generate a lot of trafic and could slow down the workbook. just posting the values and time at close would be simpler but may not suit your needs.
0
 
lucasd1973Author Commented:
There are multiple workbooks used to document various aspects of a process. I want each of these workbooks to log activity when specific cells are entered in them. My aim is to use the log to graph where each of these processes are at any given time. Certai n cells are filled in at certain stages in the process so this is the piece of data I can i can use to know where each are in the process. Make sense ?
0
 
SteveCommented:
It does kind of make sense... it sounds like you wish to monitor the files as they progress... so an update on close should be OK rather than as they change.
You can still go with either a simple text file, or Access database.
Have you much familiarity with Access?
0
 
lucasd1973Author Commented:
Due to IT rules Access cannot be used. I do need to update log when specific cells go from blank to filled. That is the only way to know where it is in the process. This file is opened and closed by many different individuals throughout the process.
0
 
SteveCommented:
OK, LogFile attached

Save this file to a folder.
Make some changes to sheet1 or sheet2 and these changes will write to a logfile for the specific file in .txt format in the same location as the LogFile. In sheet1 and sheet2 code there is the following code: (This will fire the write to the logfile)
Private Sub Worksheet_Change(ByVal Target As Range)
For Each c In Target.Cells
    Call ChangeLogFile(Me.Name, Target.Address, Target.Value)
Next
End Sub

Open in new window

then if you run the CSV_Import macro, it will allow you to open the new logfile as a query upload into excel. This is a "run once" as you can then just right click and refresh to update the QueryTable.
This will leave you with a table of changes for the workbook as they happen.
There is going to be a lot of data as changes occur.
We can look at reducing this with If Not Intersect statements later.
but first, does this seem to be the sort of direction you would like to take?
LogFile.xlsm
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now