CODEWITHSUNDEEP
excelvba
hta
hta

Reputation: 93

Track activities in multiple Excel files

I am in charge of 10 Excel files in Shared folder. And I am very curious, who is viewing my files, what sheets exactly they are looking at and in what time frame. Is there any possibility to track this activities, please? I am just thinking about that it would be possible to create one extra file with VBA code connected to all of those 10 excel files and somehow extract the data. However, I do not know how to start.

Thanks.

Upvotes: 0

Views: 115

Answers (1)

Naveen Kumar
Naveen Kumar

Reputation: 2006

You can use the following code for all 10 excel files. Whenever any file is opening following code will get executed and write the data to Monitor file.

Private Sub Workbook_Open()
Dim LR As Long
    With Sheets("Sheet1")
        file_name = ThisWorkbook.FullName
        open_time = Time
        user_name = Environ("UserName")
    End With
    Set targetwb = Workbooks.Open("D:\Test.xlsx")
    targetwb.Sheets("Sheet1").Cells(1, "A").Value = open_time 
    targetwb.Sheets("Sheet1").Cells(2, "A").Value = user_name
    targetwb.Sheets("Sheet1").Cells(3, "A").Value = file_name
    targetwb.Save
    targetwb.Close
End Sub

You can get the last row number of Monitor file and then append the new data. For security, you can add password protection to macro for each file so that nobody will be able to change your code.

Upvotes: 1

Related Questions