Track Last Several User to Open a File

[bdmangum]

Howdy all! I'm attempting to write a code which will be able to load a file and tell me who were the last several users to open the file. There isn't really any restriction on the type of file, as I will need to check a wide variety of files which currently reside in a particular folder.

The code below is what I'm currently using to grab each file. However, I'm at a loss as to how to check who last accessed the file. To be honest, I'm not even certain it's possible with Excel 2003 macros to check the last few users to open the file.

Set fso = CreateObject("Scripting.FileSystemObject")
Set UpFold = fso.GetFolder(CurDir)
Set UpFile = UpFold.Files
For Each Item In UpFile
    Set fFile = Item
Next

Set UpFold = Nothing
Set UpFile = Nothing
Set fFile = Nothing

TIA,
BD

 

[tbl]

I can give you code which will return the user logged in on the PC if that helps. As far as I am aware you can't track what you want using the file properties. You could get Excel to write a log of users each time the file is opened but that relies on having a Read/Write destination for your log file available to all users.

Richard

 

[fumei]

There isn't really any restriction on the type of file, as I will need to check a wide variety of files which currently reside in a particular folder."

If the issue is any type of files, not just an Office file, then it is not really a VBA question.

Yes, as tf1 suggests, you could have some code that would write the user login name to a file, on every open. As long as that file was a VBA-compliant file.

faq219-2884

Gerry
My paintings and sculpture

 

[bdmangum]

Thanks for the replies guys! In the past I have used code to record the user login name when they open an office file. I was just curious if any knew of a VBA approach to checking the logs of non-office files. I'm not even sure if the file properties even track the users who viewed/editted the file. Thanks again!

 

[fumei]

I'm not even sure if the file properties even track the users who viewed/editted the file. "

I would doubt that VERY much. Why would they?

faq219-2884

Gerry
My paintings and sculpture

 

[siktir23]

you could have some code that would write the user login name to a file, on every open"

Gerry, I'm looking for some code that would do exactly that. I'm on a corporate intranet and need to be able to track who opens a specific "read file" so I know they've read certain safety notices published on our intranet using Word or PowerPoint. We looked at creating an Adobe file to digitally sign but it doesn't work well with a lot of digital signatures.

Any suggestions on where I might be able to track that code down (sorry for the pun).

Thanks!

DJ

 

[tbl]

I offered the code to register the user login name some time ago. It is relatively simple to put this code in the file to register the logged-in user on the machine that has opened it and then write the name and access times to a text logfile.
I use this code to screen users and allocate privileges.

 

[macropod]

Hi siktir23,

What you're after is an audit log. This is something the Sys Admin person for a corporate intranet ought to be able to set up using standard system tools. Unless there's a reason for not using the tools that came with the system, or freely or commercially available alternatives, why are you re-inventing the wheel?

Cheers

[MS MVP - Word]

 

[fumei]

There could be reasons. He is not the SysAdmin for one. Nevertheless, macropod is correct, you may be able to easily get something like this with existing methods. It is, really, an audit log, and there are tools for this.

Essentially though, if you going to do it yourself, find the procedure that fires when the file is opened (in Word it is Document_Open), and add code that gets the username (either with Environs "Username", or API), and appends the username and date as text to a text file.

faq219-2884

Gerry
My paintings and sculpture

 

[siktir23]

Thanks Gerry...

Yes, I am NOT a admin person, simply a aircraft maintenance guy by trade. Got thrown into a computer position because I knew how to open Word and Excel.

I created what I was looking for... in Adobe. I did learn about some capabilities in Excel that I didn't know existed so thanks for at least pointing me in that direction!

Regards,
DJ