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]

No

There is no way for a macro workbook to monitor external files without an elaborate looping checker that would consume Excel resources. If those files were named with extensions .xlsm and .PPTM, a macro could be run at open to save the user name each time to an external file. Too much to maintain.

Jeff

 

[6StringJazzer]

I tend to agree with Jeffrey. There is no way for an Excel VBA process to detect a PowerPoint file open event, so Excel would have to constantly monitor the environment to see if PowerPoint is even running, and if so, if the target file is open. Similarly, there is no event in Excel for opening a file (you can detect that the file containing the macro has been opened, but to detect some other file being opened, you would also have to constantly loop looking for it. And this assumes that it's in the same instance of Excel.

You might be better off looking for a Windows-based solution, possibly written in another language like Python or C#.

 

[Jeffrey Mahoney]

Even if you could get the Opened or Closed status of files from an external application, I don't how you would get user names that open them.

 

[6StringJazzer]

On my second read of this, if the files are on a shared drive I don't think this is possible at all. You can't monitor processes on another machine. You might be able to tell if a file is locked for read/write, but you're getting into the guts of Windows file management there which is out of my range of expertise.

 

[Domenic]

Hey guys, what if we had two add-ins, one for Excel and the other for PowerPoint, so that each one could use the application-level open event handler. This way, whenever an Excel workbook or PowerPoint presentation is opened, their respective event handlers get triggered, and they can each write their specific details to a dedicated textfile? Wouldn't that work?

 

[Jeffrey Mahoney]

Wouldn't that mean that every user would have to load those add-ins?

 

[Domenic]

Wouldn't that mean that every user would have to load those add-ins?

Oh yes, that's right, thanks Jeffrey.

So I guess while it's possible, it's really not all that practical, having to distribute the add-ins, and making sure that everyone installs them properly, etc. Oh well.

 

[Abegail0203]

Thanks all for the responses. I will find another alternative tool for this.

 

[Abegail0203]

By the way, would you know the code to be embedded inside each of the workbook? and if possible, the inclusion in the macro should produce a report in excel format for the number of times the file has been viewed or opened or the number of unique viewers.