Office: (Office 2007) Logdatei für einen Exceldatei erstellen

Hallo zusammen,

ich brächte mal wieder eure Hilfe.
Ich habe eine Excel Datei mit dem Dateinamen "Regalübersicht".
Meine Frage dazu ist, ist es möglich eine log.txt beim öffnen der Exceldatei mitlaufen zu lassen, und den jeweiligen "User", Datum und was er macht zB. speichert oder ohne Änderung wieder verläst, darin abzuspeichern.

gruß Hans

:)

 

Hallo Hans,

schon wieder ich ;-))

Ein Ansatz:

' **********************************************************************
' Modul: DieseArbeitsmappe Typ: Element der Mappe(Sheet, Workbook, ...)
' **********************************************************************

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
logFile "Close (" & IIf(Me.Saved, "S", "Uns") & "aved)"
End Sub

Private Sub Workbook_BeforePrint(Cancel As Boolean)
logFile "Print (" & Application.ActivePrinter & ")"
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
logFile "Save"
End Sub

Private Sub Workbook_NewSheet(ByVal Sh As Object)
logFile "Sheet Add", Sh.Name
End Sub

Private Sub Workbook_Open()
logFile "Open"
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
logFile "Change", Sh.Name, Target.Address(0, 0), Target(1, 1).Value
End Sub

' **********************************************************************
' Modul: Modul1 Typ: Allgemeines Modul
' **********************************************************************

Option Explicit

Public Sub logFile(ByVal Action As String, Optional Sheet As String, Optional ByVal Target As String, Optional ByVal Value As String)
Dim strLogFile As String, strTmp As String, strAux As String * 12

strLogFile = ThisWorkbook.Path & Application.PathSeparator & ThisWorkbook.Name & "_log.txt"

'oder
'strLogFile = "C:\Ordner\log.txt"

strTmp = Format(Now, "dd.MM.yyyy hh:mm:ss") & vbTab
strAux = Environ("USERNAME")
strTmp = strTmp & strAux
strAux = Action
strTmp = strTmp & strAux
If Len(Sheet) Then strAux = Sheet: strTmp = strTmp & strAux
If Len(Target) Then strAux = Target: strTmp = strTmp & strAux
If Len(Value) Then strTmp = strTmp & Value


Open strLogFile For Append As #1
Print #1, strTmp
Close #1

End Sub





« Gruß Sepp »

 

Danke, werde ich am Montag sofort probieren.

Weis ja jetzt schon das es funktioniert wenn Du das machst.

gruß Hans

 

Hallo Josef,

hat bestens funktioniert.
Ist es auch möglich Änderungen der Spalten "G" und "I-K"zuspeichern.
z.B so "G geändert" oder "G20" geändert wenn "G20" geändert wurde.

Das andere past so.

gruß Hans

 

Hallo Hans,

anbei der angepasste Code. Beim LogFile werden jetzt neue Einträge in der ersten Zeile hinzugefügt.

' **********************************************************************
' Modul: DieseArbeitsmappe Typ: Element der Mappe(Sheet, Workbook, ...)
' **********************************************************************

'// Module : DieseArbeitsmappe, VBA Dokument
'// Author : j.ehrensberger (j.ehrensberger@aon.at)
'// Created : 30.07.2011 - 16:31
'// Modified : 30.07.2011
'// Purpose : LogFile

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)
logFile "Close (" & IIf(Me.Saved, "S", "Uns") & "aved)"
End Sub

Private Sub Workbook_BeforePrint(Cancel As Boolean)
logFile "Print (" & Application.ActivePrinter & ")"
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
logFile "Save"
End Sub

Private Sub Workbook_NewSheet(ByVal Sh As Object)
logFile "Sheet Add", Sh.Name
End Sub

Private Sub Workbook_Open()
logFile "Open"
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Select Case Target(1, 1).Column
Case 7, 9 To 11
logFile "Change", Sh.Name, Target.Address(0, 0), IIf(Target(1, 1).HasFormula, _
Target(1, 1).FormulaLocal & " » ", "") & Target(1, 1).Value
Case Else
End Select
End Sub

' **********************************************************************
' Modul: Modul1 Typ: Allgemeines Modul
' **********************************************************************

'// Module : Modul1, Modul
'// Author : j.ehrensberger (j.ehrensberger@aon.at)
'// Created : 30.07.2011 - 16:32
'// Modified : 30.07.2011
'// Purpose : LogFile

Option Explicit

Public Sub logFile(ByVal Action As String, Optional Sheet As String, Optional ByVal Target As String, Optional ByVal Value As String)
Dim strLogFile As String, strTmp As String, strOld As String
Dim strUser As String * 12
Dim strAction As String * 15
Dim strSh As String * 12
Dim strAddr As String * 12
Const strSep As String = ", "

strLogFile = ThisWorkbook.Path & Application.PathSeparator & ThisWorkbook.Name & "_log.txt"

'oder
'strLogFile = "C:\Ordner\log.txt"

strTmp = Format(Now, "dd.MM.yyyy hh:mm:ss") & strSep
strUser = Environ("USERNAME")
strTmp = strTmp & strUser & strSep
strAction = Action
strTmp = strTmp & strAction & strSep

If Len(Sheet) Then strSh = Sheet: strTmp = strTmp & strSh & strSep
If Len(Target) Then strAddr = Target: strTmp = strTmp & strAddr & strSep
If Len(Value) Then strTmp = strTmp & Left(Value, 1024)

If Right(strTmp, Len(strSep)) = strSep Then strTmp = Left(strTmp, Len(strTmp) - Len(strSep))

Open strLogFile For Binary As #1
strOld = Space$(LOF(1))
Get #1, , strOld
Close #1

strTmp = strTmp & vbCrLf & strOld

Open strLogFile For Output As #1
Print #1, strTmp
Close #1

End Sub





« Gruß Sepp »

 

Hallo Josef,

funktioniert bestens.

gruß Hans