![]() |
How do I create a logfile
Hi,
I have a shared excelfile with is edited by sometimes 8 users. I want to create a logfile with the following items: Date/time, sheetname, User, Cell, Old value, New value. I only want the log the manually input. I didn't start yet, I first want to figure out a way to make it. I do have a login_username function. I thought of: - create/open a shared logfile - use a worksheet_change function ??? - find out the user, cell, new value, sheetname and date/time BUT::::: - How can I find old_value - How do I find the new line in the logfile - What about multiple acces at the same time in the logfile any suggestion?? is there a better/easyer way?? Jan |
How do I create a logfile
Have you checked out the Track Changes option? (Tools, Track Changes)
"Jan Grinwis" wrote in message om... Hi, I have a shared excelfile with is edited by sometimes 8 users. I want to create a logfile with the following items: Date/time, sheetname, User, Cell, Old value, New value. I only want the log the manually input. I didn't start yet, I first want to figure out a way to make it. I do have a login_username function. I thought of: - create/open a shared logfile - use a worksheet_change function ??? - find out the user, cell, new value, sheetname and date/time BUT::::: - How can I find old_value - How do I find the new line in the logfile - What about multiple acces at the same time in the logfile any suggestion?? is there a better/easyer way?? Jan |
How do I create a logfile
Look at the history option under shared files.
-- Regards, Tom Ogilvy "Jan Grinwis" wrote in message om... Hi, I have a shared excelfile with is edited by sometimes 8 users. I want to create a logfile with the following items: Date/time, sheetname, User, Cell, Old value, New value. I only want the log the manually input. I didn't start yet, I first want to figure out a way to make it. I do have a login_username function. I thought of: - create/open a shared logfile - use a worksheet_change function ??? - find out the user, cell, new value, sheetname and date/time BUT::::: - How can I find old_value - How do I find the new line in the logfile - What about multiple acces at the same time in the logfile any suggestion?? is there a better/easyer way?? Jan |
How do I create a logfile
"Tom Ogilvy" wrote in message ...
Look at the history option under shared files. -- Regards, Tom Ogilvy Thanks Tom, I found what I needed. It now looks like this: Private Sub Worksheet_Change(ByVal Target As Range) Dim old_Val As Variant Dim new_Val As Variant Dim Sheet_Name As Variant If Target.Count 1 Then Exit Sub On Error GoTo ErrHandler Application.EnableEvents = False new_Val = Target.Value Sheet_Name = ActiveSheet.Name Application.Undo old_Val = Target.Value Target.Value = new_Val Open "C:\logfile.txt" For Append As #1 Print #1, Now, ";", Sheet_Name, ";", Target.Address, ";", fOSUserName(), ";", old_Val, ";", new_Val Close #1 ErrHandler: Application.EnableEvents = True End Sub Can someone tell me what will happen when the logfile is accessed by more than one person at the same time..By the way, the logfile is on a netwerkdrive (not c:) Regards Jan |
How do I create a logfile
I think it depends on what program accessed the file.
Some programs will lock the file and your code would error on the "open" line. Some programs won't lock the file (essentially opening it in Readonly mode) and nothing bad will happen. If you know what program you'll use, use it to open the file. And make a few changes in excel to see what happens. I like a program called UltraEdit32 (www.ultraedit.com). It'll open text files and even tell me when some other program has changed them and offer to reload. === But in general, copy that log file to a different name and open the copy. Jan Grinwis wrote: "Tom Ogilvy" wrote in message ... Look at the history option under shared files. -- Regards, Tom Ogilvy Thanks Tom, I found what I needed. It now looks like this: Private Sub Worksheet_Change(ByVal Target As Range) Dim old_Val As Variant Dim new_Val As Variant Dim Sheet_Name As Variant If Target.Count 1 Then Exit Sub On Error GoTo ErrHandler Application.EnableEvents = False new_Val = Target.Value Sheet_Name = ActiveSheet.Name Application.Undo old_Val = Target.Value Target.Value = new_Val Open "C:\logfile.txt" For Append As #1 Print #1, Now, ";", Sheet_Name, ";", Target.Address, ";", fOSUserName(), ";", old_Val, ";", new_Val Close #1 ErrHandler: Application.EnableEvents = True End Sub Can someone tell me what will happen when the logfile is accessed by more than one person at the same time..By the way, the logfile is on a netwerkdrive (not c:) Regards Jan -- Dave Peterson |
All times are GMT +1. The time now is 10:59 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com