最后编辑工作簿时的文档

Question

我在一本书里找到了代码：

Option Explicit

Sub SaveAndCLose()
    Application.DisplayAlerts = False
    Tabelle1.Range("A1").Value = _
    "Last Edition " & Now & " from User " & Environ("Username")
    ThisWorkbook.Close Savechanges:=True
    Application.DisplayAlerts = True
End Sub

是否有可能记录下最后10个编辑。例如:今天，用户X编辑- Range("A1")。第二天，该文件的每个版本都有另一个编辑，生成了Range("A2")等等。

我知道在Excel中没有实现审计跟踪，但是简单的代码提供了进行最后一次编辑的信息。

或者有一种更好的方法来实现Excel文件的审计跟踪？

Answer 1

简单的代码可能是以下代码

Option Explicit
Const X = "X"

Sub SaveAndClose()

Dim rgB As Range
Dim rowX As Long
Dim auditTxt As String

    Set rgB = Tabelle1.Range("B1:B10")
    auditTxt = "Last Edition " & Now & " from User " & Environ("Username")

    rowX = findXA(rgB)
    'rowX = findX(rgB)

    If rowX = 0 Then
        Tabelle1.Cells(1, 1).Value = auditTxt
        Tabelle1.Cells(1, 2).Value = X
    ElseIf rowX = 10 Then
        Tabelle1.Cells(1, 1).Value = auditTxt
        Tabelle1.Cells(1, 2).Value = X
        Tabelle1.Cells(rowX, 2).ClearContents
    Else
        Tabelle1.Cells(rowX + 1, 1).Value = auditTxt
        Tabelle1.Cells(rowX + 1, 2).Value = X
        Tabelle1.Cells(rowX, 2).ClearContents
    End If


    ''    I commented this part of the code for testing purposes
    ''    Uncomment to save and close the file

    '    Application.DisplayAlerts = False
    '    ThisWorkbook.Close Savechanges:=True
    '    Application.DisplayAlerts = True

End Sub
Function findX(rg As Range) As Long
' find the X by putting the range into an array and looping through it
    Dim vDat As Variant
    Dim i As Long

    findX = 0
    vDat = WorksheetFunction.Transpose(rg)

    For i = LBound(vDat) To UBound(vDat)
        If UCase(vDat(i)) = X Then
            findX = i
            Exit Function
        End If
    Next
End Function

Function findXA(rg As Range) As Long
' find the X by usind ragne.find
Dim rgX As Range

    Set rgX = rg.Find(X, , , , , , False)
    If rgX Is Nothing Then
        findXA = 0
    Else
        findXA = rgX.Row
    End If

End Function

代码使用col和B，并将X放入col中，作为最后一行。也许这不是一个“聪明”的代码，但我希望它只是简单的遵循。

​