Macro to count the number times the excel/powerpoint has been opened

[Abegail0203]

Hi,

Is there a way to count the number of viewers of each report (may it be a ppt or xlsx in a shared drive) with maintaining and running only one macro?

Example: I have these files in our shared drive:

Monthly Amortization Report 2024.01.26 12-56.xlsx
Monthly Accrual Report 2023.12.27 23-07.xlsx
ideaproject.ppt

The filenames (Monthly Amortization Report & Monthly Accrual Report are constant/fixed while the dates are dynamic in nature).

The macro should create a summary list of the number of times a specific file (excel or power point) has been opened/viewed, and the number of unique viewers.

The output should be-
1st column: name of file
2nd column: number of times the file has been opened
3rd column: number of unique viewers

 

[Jeffrey Mahoney]

I have some code at home that could be adopted. Basically, each time any of the workbooks is opened, it saves a new line to a CSV file with:
Name of Workbook, Name of User, Date/Time

CSV files are imported into Excel very easily. It would be up to you to summarize the results. I haven't added VBA to PPTM files, but I suspect that it's very similar.

Potential Issues:
1) Every user would have to have rights to read/write files on a server folder
2) Macros don't work on Sharepoint
3) All workbooks and Powerpoint files must be macro workbooks and the users must have their VBA options set to run macros on startup

 

[6StringJazzer]

2) Macros don't work on Sharepoint

Can I clarify that? SharePoint is a file repository (to oversimplify it). You can open a SharePoint file using your desktop version of Excel and VBA will run. However, if you open a SharePoint file with the browser-based online Excel, VBA will not run. When I was using SharePoint, which was about three years ago, you could open the online Excel and then there was a button to open the file using the desktop version, so you could even change horses in the middle of the stream.

 

[Jeffrey Mahoney]

As I understand it also. So therefore, anybody opening and changing the workbooks in the online version won't be counted

 

[Jeffrey Mahoney]

Ok, here is some code you can add to every one of your workbooks you want to get stats on.

You need to change the location where the Log file is kept by change the value for LogPathFile. If the Log file is open, either by another log being created or you opening it manually, the timer will wait 10 seconds and keep trying to create a new log. The log file attribute is hidden, so you'll need to change your file explorer setting to see it.

This code goes in ThisWorkbook module

Private Sub Workbook_Open()
  CreateNewLogEntry
End Sub

This code can be put in a standard module

Sub CreateNewLogEntry()
  
  Dim A As String
  Dim T As Single
  Dim TB As String
  Dim FF As Integer
  Dim UserName As String
  Dim UserID As String
  Dim WBName As String
  Dim DT As String
  Dim LogPathFile As String
  
  UserID = UCase(Environ("UserName"))
  UserName = Application.UserName
  WBName = ThisWorkbook.Name
  DT = Format(Now(), "MM/DD/YYYY HH:MM:SS")
  LogPathFile = "C:\Temp\UserLogData.txt"
  
  TB = Chr(9)
  
  A = Dir(LogPathFile, vbHidden)
  If A <> "" Then
    T = Timer
    Do
      Select Case IsFileOpen(LogPathFile)
        Case False
          SetAttr LogPathFile, vbNormal
          Exit Do
        Case True
          If Timer - T > 10 Then
            'MsgBox "A timout has occured while trying to open the Log File"
            Exit Sub
          End If
        Case Else
          'MsgBox "An error occured while trying to open the Log File"
          Exit Sub
      End Select
    Loop
  End If
  
  
  FF = FreeFile
  Open LogPathFile For Append As #FF
  If A = "" Then
    Print #FF, "Workbook Name" & TB & "User Name" & TB & "User ID" & TB & "Date /  Time"
  End If
  Print #FF, WBName & TB & UserName & TB & UserID & TB & DT
  Close #FF
  SetAttr LogPathFile, vbHidden
    
  
  
End Sub



'Checks to see if file is open by another user or by current user
Function IsFileOpen(FileName As String) As Integer
    Dim filenum As Integer, errnum As Integer

    On Error Resume Next    ' Turn error checking off.
    filenum = FreeFile()    ' Get a free file number.
    
                            ' Attempt to open the file and lock it.
    Open FileName For Input Lock Read As #filenum
    Close filenum          ' Close the file.
    errnum = Err           ' Save the error number that occurred.
    On Error GoTo 0        ' Turn error checking back on.

    ' Check to see which error occurred.
    Select Case errnum

        ' No error occurred.
        ' File is NOT already open by another user.
        Case 0
         IsFileOpen = 0

        ' Error number for "Permission Denied."
        ' File is already opened by another user.
        Case 70
            IsFileOpen = errnum

        ' Another error occurred.
        Case Else
            IsFileOpen = errnum
    End Select

End Function

 

[Jeffrey Mahoney]

It appears that adding a macro to run automatically when a Powerpoint file is open requires some code added to a class module in an add-in. I'm not sure I want to take the time to learn that. This would also defeat the purpose for what you are intending. That method would require all users to have the add-in loaded prior to opening your powerpoint files to be logged.