Solved: Track File Usage

[MrRhodes2004]

I thought this would be easy but it has been more challenging for me that I believed.
Since I am not an administrator on my network, I do not have the tools available to me to track a file, to see who has used the file, or to see who has it open. Yes, there are ways to look at the owner at the current time but that doesn't tell who has been in the file.

1. Create User defined text file on network (start the user on the same place on the network every time) to collect data
2. Enter full path and file name of file tracked in new text file
3. Add code to 'Workbook Open' to 'append' to text file' Environ("Username") and date - "Opened"
4. Add code to 'Workbook BeforeClose' to 'append' to text file' Environ("Username") and date - "Saved"

I think the adding code to the current file will be the easy part but I could not figure out how to create a user defined text file on the network.

Any advice would be helpful.

[Bob Phillips]

Why not just add a sheet to the workbook, and hide it.

[MrRhodes2004]

Why not just add a sheet to the workbook, and hide it.

There are three main problems with and internal tracking:
1. That is an idea but some files are partially-protected via code. Some users have full access to the files but others have 'read-only' rights. I'd like to track both users. To track both users, the code would have to change the status of the file, add the information, then close the file.
2.If the file is not saved after each use, then the data is not maintained. The code would force the file to save thus changing the properties of the file. Then it would be difficult to track who just opened the file to view it and the ones who opened and saved.
3. If the data is held within the file, each time a user opened the file it would be 'modified' thus making it impossible search the files based upon 'last modified'.

[Charlize]

For the desperate ones. Maybe you can do something with this. Change the path to the logging file to a path that everyone can acces on the network. You can make the file hidden so users can't see it.

Charlize

ps.: Logfile will now be created in same directory as the workbook. Is called the same as the workbook exept .txt as extension.

[Charlize]

Well, MrRhodes2004, was this a suitable solution to start with ?

Charlize

[xls]

Really good

[jammer6_9]

What a ... It's just happen that I need that code also...

[MrRhodes2004]

Well, MrRhodes2004, was this a suitable solution to start with ?

Charlize

It gave my some ideas but it didn't solve the problem.

I'd like to be able to run the macro which would open a dialog box which would allow the user to select a folder that they want their tracking text file to be stored then it would ask them for the name of the file. Once it had that information, it would create that file.

Any ideas?

[Charlize]

It gave my some ideas but it didn't solve the problem.

I'd like to be able to run the macro which would open a dialog box which would allow the user to select a folder that they want their tracking text file to be stored then it would ask them for the name of the file. Once it had that information, it would create that file.

Any ideas?

I don't get this. You want that the user of a file knows that everything the user does, is tracked in an external file. Most users don't like it that someone else can see how they messed up a file and what they have done during the use of a file.

Charlize

[MrRhodes2004]

Typically, the file creator or author would create the tracker code and text file. Once the tracker has been issued, each time the file is opened or saved by other users, a comment would be placed into the text file. This would be done automatically. In a company environment, all movements within the system are prevue to the company including opening, changing, and messing up others files. Therefore, I have no concern with what the user likes or does not like.
Plus, the intent is not track who messed up the file. It is more of an exercise to see who is using the file and when. It will also be helpful to find out who is currently in it since our system is not always helpful. It will sometimes state, when another is in the file and another attempts to open it, 'The file is locked for editing by "Company Name".' This does not help me identify who has the file currently open.

[MrRhodes2004]

[VBA]Sub CreateAfile()
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.CreateTextFile("c:\testfile.txt", True)
f.WriteLine ("This is a test.")
f.Close
End Sub[/VBA]

I can use VBA to Create a text file via the manner above. But the problem is that I dictate the location and file name in the code. Is there a way to have the user define the folder and file name with a dialog box?

[Aussiebear]

Was there ever a solution to the treeview thread some time back, where the user simply selects the file from the graphical listing of the file?

[Aussiebear]

Did a search on treeview and theres a number of threads including this one from Ken Puls

http://vbaexpress.com/forum/showthre...light=treeview

Would this be helpful?

[MrRhodes2004]

[VBA]

Sub CreateAfile()
Dim vntSaveName As Variant

vntSaveName = application.GetSaveAsFilename
If vntSaveName = "False" Then exit Sub

Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.CreateTextFile(vntSaveName, True)
f.WriteLine ("This is a test.")
f.Close
End Sub

[/VBA]

http://www.vbaexpress.com/forum/showthread.php?t=12434

I think I have what I need. Thanks everyone.