Excel Data Date/Time Log

Posted on 2012-08-30
Last Modified: 2012-09-11
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.
Question by:lucasd1973
    LVL 24

    Expert Comment

    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.

    Author Comment

    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 ?
    LVL 24

    Expert Comment

    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?

    Author Comment

    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.
    LVL 24

    Accepted Solution

    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)
    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?

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Join & Write a Comment

    Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
    This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
    The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
    This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now