ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   How do I create a logfile (https://www.excelbanter.com/excel-programming/312302-how-do-i-create-logfile.html)

Jan Grinwis

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

bigwheel[_2_]

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




Tom Ogilvy

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




Jan Grinwis

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

Dave Peterson[_3_]

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