Tracking access to Excel file through VBA rutine

Morning and Happy Holidays!

I was looking for some help on a VBA solution. I would like to run an on open event for a scheduling workbook. It should open a logging workbook, write the %username% to the first blank line in A:A then save and close the logging workbook, leaving the scheduling workbook open and ready for input.

The following UNC can be used as the location for the logging workbook.

\\ComputerName\ParentFolder\ChildFolder\LogFiles

If possible, I would like this logging to be transparent to the end user.

As always thanks for the help and have a GREAT HOLIDAY SEASON!

Maybe like this (untested):

Private Sub Workbook_Open()
    Const sFile     As String = "\\ComputerName\ParentFolder\ChildFolder\LogFiles\someLogFile.xls"

    If Len(Dir(sFile)) Then
        Workbooks.Open sFile
        With ActiveWorkbook.Worksheets(1)
            .Cells(.Rows.Count, "A").End(xlUp).Offset(1).Value = Environ("username")
            .Close SaveChanges:=True
        End With
    Else
        MsgBox "Log file " & sFile & " not found."
    End If
End Sub

SHG

Merry Texmas to you too!

Thanks for the try but I don't belive anything is happening with this code.

I tested with a new .xlsm workbook and added your code as a module.

The logfile to write to is named

Daily Schedule Access Logfile.xlsx

and is located in the

\\fspbrf01\GROUP\Maintenance\Work Management\Public\Work Management  KPIs\SAP Scheduling\Schedule logfile\

directory. The extra space between Management & KPIs is not a typo, it was how the directory structure was created long before I got here.

Your code has been edited to match the actual directory structure and work book name.

Private Sub Workbook_Open()
    Const sFile     As String = "\\fspbrf01\GROUP\Maintenance\Work Management\Public\Work Management  KPIs\SAP Scheduling\Schedule logfile\Daily Schedule Access Logfile.xlsx"

    If Len(Dir(sFile)) Then
        Workbooks.Open sFile
        With ActiveWorkbook.Worksheets(1)
            .Cells(.Rows.Count, "A").End(xlUp).Offset(1).Value = Environ("username")
            .Close SaveChange:=True
        End With
    Else
        MsgBox "Log file " & sFile & " not found."
    End If
End Sub

Nothing is being written to "Daily Schedule Access Logfile.xlsx" nor is a message box reporting any errors even if I temporarily remove the "Daily Schedule Access Logfile.xlsx" file.

So you single-step through the code, and what happens?

I had to figure out how to single step through code. I'm getting a Run-time error '438': Object doesn't support this property or method on .

Close SaveChange:=True

It did open the file and log the username as expected.

.Close SaveChanges:=True

That was among the untested parts

Corrected in prior post for future generations ...

Also, note it's .Close, not Close