We help IT Professionals succeed at work.

Read Only Workbook - How to Log who reads the workbook

74 Views
Last Modified: 2018-08-25
I have a Ready Only workbook that is available to 100 people.  I want to determine who uses the workbook.  However, I can not add code to the OnOpen event as since the Workbook is read only then my code can not write the users name to a cell.  My other concern is a macro may open this read only workbook and I do not want to impact any macros that may open this read only workbook and extract info..

Any suggestions on how I can figure out who uses the workbook without having to email all 100 individuals..
Comment
Watch Question

David FavorFractional CTO
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
Place your workbook behind a CMS which forces a login for access.

You'll know instantly.

Likely much easier to use a working CMS, like WordPress, than hand roll your own.

Author

Commented:
Whats a CMS...Whatever it is I think that solution would be problematic as if today am unaware that a macro opens this read only workbook and then out of nowhere I put up a CMS I assume the macro would fail ?
David FavorFractional CTO
CERTIFIED EXPERT
Distinguished Expert 2019

Commented:
CMS == WordPress, Drupal, Joomla, 100s of others.

If you're new to CMS software, stick with WordPress.

With a CMS, you'd just setup your workbook as restricted content, so a person has to login to use your workbook. Then just track user stats.

You can also go a step further + break apart your workbook into separate content pages, to gather more granular data.

Read up on WordPress. You'll likely be surprised by all the features a CMS can provide for you.
Roy CoxGroup Finance Manager
CERTIFIED EXPERT

Commented:
Maybe write to a text file. Create a text file named log, change the path in the code to suit

Option Explicit

Private Sub Workbook_Open()
    Dim sPath As String
    sPath = ThisWorkbook.Path & Application.PathSeparator & "log.txt"    ''///Change to your folder
    Open sPath For Append As #1
    Write #1, Application.UserName & " , " & Format(Now, "yyyy/mm/dd hh:mm")
    Close #1
End Sub

Open in new window

Group Finance Manager
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Hi Roy, what is the purpose of the ihandle...
Roy CoxGroup Finance Manager
CERTIFIED EXPERT

Commented:
The VBA FreeFile function reserves the next available file number for VBA file input/output. When you open a file for writing or reading in VBA, you must refer to that file with a unique integer file number. The FreeFile function determines this unique file number for you so you can use it to open, read, and write files using VBA. It avoids a conflict if your chosen number has been used.

Author

Commented:
Thanks for the detail
Roy CoxGroup Finance Manager
CERTIFIED EXPERT

Commented:
Pleased to help
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.