PDA

View Full Version : Solved: write log of changes in workbook



danovkos
06-01-2009, 06:15 AM
Hi all,
i try figured out, if its possible to made some code, which will always after changing and saving wb, write log in one file (at the START of one log.txt - the newest will be in 1. line).
I mean, if it is possible to write in this log:
- date, time of change
- name of sheet, where was change
- the last 3 position of changed areas (A1, A4: D5, C400)
- user whos did the change

resluts will be something like this:
- 1/6/09 - sheet1 - (E5, W230, A5: w30) - johnyQ
- 1/6/09 - sheet2 - (E5, W230, A5: w30) - suziQ
- 2/6/09 - sheet1 - (E10, W133, D35) - peter
...

thx

lucas
06-01-2009, 08:05 AM
http://www.vbaexpress.com/kb/getarticle.php?kb_id=464

MaximS
06-01-2009, 08:42 AM
or try:

Kenneth Hobs
06-01-2009, 10:50 AM
I demonstrated 2 methods in this thread: save to text file and save to and MDB table. http://vbaexpress.com/forum/showthread.php?t=22620

I think you would be better off using a log file as a database file. You could always use SQL to sort it and get all of the modified cells rather than just the last 3.

danovkos
06-01-2009, 10:44 PM
thank you to all
i will try it your codes or links

danovkos
06-02-2009, 03:23 AM
HI ,
why pls. this code return wrong reference?

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
LogInformation ActiveSheet.Name & Selection.Address & " Changed: " & _
" " & Format(Now, "dd" & "." & "mm" & "." & "yy" & " " & "hh:mm:ss") & " " & Application.UserName
End Sub


f.e. if i write something to I20 it write to log I21 ? :)

danovkos
06-02-2009, 03:31 AM
now i figured out, that it write reference, where ended cursor. If i somehting write to cell and use enter it write row+1, if i use tab it write column +1
:)
how can fix it?
thx
and how can i
- add free row to logfile.txt after save ?
- add "tab" separator between data in one row?

thx a lot

danovkos
06-04-2009, 11:13 PM
i´ve got it :)
only fyi - for other readers:

- tab - is in VBA
vbTAB :)

- free line i write like this - only as other command :)

LogInformation " "

f.e. whole line for writting LOG for me is:


Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
LogInformation Format(Now, "dd" & "." & "mm" & "." & "yy" & " " & "hh:mm:ss") & ";" _
& vbTab & Application.UserName & ";" & vbTab & vbTab & "Changed" & ";" & vbTab & _
Selection.Address & ";" & vbTab & vbTab & ActiveSheet.Name
End Sub


maybe it helps :)