• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Count # Times File Opened WITHOUT Macro

Renee Beacham

New Member
Hello all,

I'm wondering whether it is possible to count the number of times an excel file has been opened without using a macro. This is needed to track the usage of shared documents. The excel files sit in a shared drive and can be accessed by multiple individuals from various computers. These individuals will not have macros enabled in their settings. Perhaps there is an excel formula that can help?

Thanks,
-Renee
 
Not really. But you can track and see what changes were made by whom and when.

Ribbon Tool -> Review ->Trach Changes
Then go to Highlight changes.

Uncheck When, Who and Where.

Check List Changes on a new Sheet. Output will look like below.
upload_2016-6-9_16-43-47.png
 
Alternately if you have access to SharePoint, you can utilize its version tracking mechanism for document collaboration.
 
Why not setup the file so that it won't give them access to the data unless they enable macro's?
 
Thanks all,

These reports are used by associates unfamiliar with excel and are used in the read-only format. The assocaites access the files from the shared documents folder.

The intent is to track which reports are being used since there's evidence they're not being used, but whenever asked about use, the associates say the reports are being used. We are looking for a data-based answer to our frequency of use question.

Enabling their macros each time and requiring them to re-save the document is not possible in the read-only format. I looked into SharePoint but it appears the tracking feature is only possible with lists. I have links to the reports' locations in the shared folder on the SharePoint, but nothing else.
 
As I discussed above it is possible to setup a file such that when it opens, it displays a warning screens that Macro's Must Be Enabled to gain access to the data.

If Macro's are enabled, they will get access to the data
If Macro's are not enabled, they will get a warning screen and no access to the data

By doing this it is possible to setup a system so that the user ID is logged along with the date/time and file accessed when Macro's are enabled

If you would like me to setup a mockup file please let me know

What data do you want logged? I assume it will be User ID, Date, Time, File Opened, do you want anything else ?
 
Would macros have to be enabled overall, meaning every time they open an excel file their macros will be enabled? Or will this option only enable the macros in the current excel file they are opening?
 
No
Macros will need to be enabled on the file that they open as it is opened
Once it closes it will revert to normal

There is no way to generically enable macros for all files
 
Ok that should work. Would you be able to create the mock-up? I would only need day and time of use, and a count (if possible, a sum of the counts as well). It would look something like this:

Date Time Count
01/06/2016 3:00pm (CNT) 1

1 (<- sum function)

Thanks!
 
Renee

Place the attached 3 files into a directory

Open the File 1 or File 2
You will get a warning screen "Enable Macro's to access the data"

If you don't Enable macro's the data stays hidden
If you enable macro's you get access to the data and the warning screen is hidden

Each time a file is opened it adds a record into the File Usage Register
upload_2016-6-17_14-20-19.png

Now depending on the Security setup of your PC's the next time a file is opened it may automatically enable macro's or not, I am unsure
Either way once macro's are enabled it will add a new record to the register

Enjoy

To add this functionality to new files you have to copy the code in Module 1 and the ThisWorkbook code module to any files

I would also suggest thinking about putting the File Usage Register.xlsx file in a common, network location.
It will then need to be referenced in the code in module 1 in each file
There is a line for that

I would test this with 2 files first and then once happy that it is working as required expand to other files
 

Attachments

  • File Usage Register.xlsx
    9.1 KB · Views: 17
  • File 2.xlsm
    27.5 KB · Views: 8
  • File 1.xlsm
    27.5 KB · Views: 14
Wow this is really cool! Thanks for putting it together.

I've just run into one issue, and that is whenever I try to run the macro, it can't find my save location. I've tried multiple locations and I've checked the spelling.

Here's what it looks like in the code:

wb = "C:\Users\rlb9071\Documents\File Usage Register\"
ws = "Sheet1"

I plan on putting it onto a shared drive once I can get it working.

Thanks
 
Please try the following code
I have added a line to store the Register Directory, leave off the \
I have also made a few small improvements

Code:
Sub Save_Usage_Data()
Dim wb As String, openfile As String
Dim ws As String
Dim lr As Long
Dim RegisterDir As String

wb = "File Usage Register"
ws = "Sheet1"

'Set the directory where you want to store the Usage Register
RegisterDir = "C:\Users\Dads\Desktop\Enable Macros\Usage"

ChDir ActiveWorkbook.Path

openfile = ActiveWorkbook.Name
Workbooks.Open Filename:=RegisterDir + "\" + wb
wb = wb + ".xlsx"

lr = Range("A" & Rows.Count).End(xlUp).Row + 1

Application.ScreenUpdating = False
With Workbooks(wb).Worksheets(ws)
  .Cells(lr, 1).FormulaR1C1 = Date
  .Cells(lr, 2).FormulaR1C1 = Time
  .Cells(lr, 3).FormulaR1C1 = Application.UserName
  .Cells(lr, 4).FormulaR1C1 = openfile
End With

Range("A" & lr - 1 & ":D" & lr - 1).Copy
Range("A" & lr & ":D" & lr).PasteSpecial xlPasteFormats

Range("A" & lr & ":D" & lr).Copy
Range("A" & lr & ":D" & lr).PasteSpecial xlPasteValues

Application.CutCopyMode = False
Application.DisplayAlerts = False
ActiveWorkbook.Save
Application.DisplayAlerts = True
ActiveWorkbook.Close

Application.ScreenUpdating = True

End Sub

Copy the above code into all files replacing the current module
 
Thanks this is working perfectly! One question, I would like to use this with some files that have an auto refresh macro included. Is there a way to save the refreshed file with one module enabled and the other not enabled? That way when the file is opened, the register is enabled but the data won't auto refresh.
 
I'd setup the other module as a stand alone module
Then in the Workbook_Open module call it like I call the Save_Usage_Data module

upload_2016-6-20_22-39-28.png
 
I added the additional call statement, but I may not be understanding how to setup a stand alone module. Here's a screenshot of the setup as well as the code for my other module. It's called Auto_Open. When I enable content, it saves the data in the register, updates the data, and saves a copy to the correct location. The only difficulty I'm having is when you open the file that was saved in the new location (Weekly_Reports\Call_Info_Note_SW) and enable the macros, it also refreshes the data. The data needs to stay static as if in read-only mode.

vba.jpg
 
Back
Top