Log User Activity of Excel File to Another Text File

Hi,

I am completely new to this VBA Code. Honestly cannot make a sense of anything yet!!

After a lot of internet research I have found a code to track user activity, like who opened the file and closed and what changes were made at what time etc ...(the person who posted that claimed it would do all this stuff!!!)

I copied the text and pasted in the module/code and saved the file as directed on the website. But what next ???

The user mentioned this line " You need to change the Const declaration to point to the location you want to store the file at. The file does not have to exist yet - it will be created if it doesn't already exist, but the path to it must already exist.

'change this to a path accessible to all users of the file
'best to use the full network reference than to use
'a mapped drive letter in case the location is not mapped when the file is used.
Const recordFile = "\\OurSERVER\Folder1\Folder2\useLog.txt"

I am note sure where does this file "Const recordFile" gets created and how to access it, though I know where I would like to map it.

This really feels like, I have got what I wanted to do, But I don't know where to see the results.

Any help will be appreciated.
Lin

The person posting the code said the path must exist, but the file will be created if it doesn't exist. So, the line:

const recordfile = "\\OurServer\Folder1\Folder2\useLog.txt"

is the path to the network file from the sample. He is using an UNC name, meaning the path points to a share on a server. If you want the logged file to be on a mapped drive, or on a local drive, you could change the path to something like:

const recordfile = "g:\folder1\folder2\uselog.txt"

or

const recordfile = "c:\folder1\folder2\uselog.txt"

The UNC convention will work better if an office that doesn't have established drive mappings, in case different users map different drives to the same location. If everyone uses the same drive mapping, or if it is just one user, the g: or c: will work just fine.

Obviously, the "OurServer", "Folder1", "Folder2" and "uselog.txt", even the number of folders in the tree, will need to be changed to match your actual log file location.

As long as you put the correct path for your actual desired location in (c:\folder1\folder2\), the routine should create the log file. If you put in a path that doesn't exist, the routine will not work.

Dear wallyeye,

I have kept the test excel file on my desktop, presuming that the Text File of Log Activity will be generated on the desktop itself. (Not Sure if it works this way??) Or will my file get generated in the "C" drive? (C: = where installation/program files are stored by default in my computer)

I have yet not put that file on the shared drive. Changing the path name would be a secondary question, my first concern is to find where the file is getting generated

Below is the post which guided me with the code, (I believe so..) "http://answers.microsoft.com/en-us/office/forum/office_2007-excel/in-excel-how-do-i-set-up-a-code-or-option-to-log/6667262e-10d3-4b7c-8175-05d0f2310503"

I have simply copy pasted the code, do I need to modify anything in the code?

Kindly suggest.

Dear Kyle,

Thanks for pointing out, this code will indeed make my file sluggish if it is adding the changes log to a text file but more than that it will be difficult If I want to trace any particular change.

I just managed to search another code wherein the code is actually generated in the excel File itself - separate sheet and that sheet can be hidden and the Log size Managed.

But I am very new to this VBA coding hence not sure what I am doing wrong.

I copy pasted the code as directed by the person who posted it & when I click on run Macro, there is only the HideLog/ViewLog Macro.

So far I have managed to Hide and View the Log Sheet, but there are no changes being logged in that sheet as claimed by the user.

Below is the Link which provided the code.

http://www.yogeshguptaonline.com/200...ty-to-log.html

Can you please advise what steps need to be rectified to achieve the desired result.

All that I want to do is same function as Track changes, but this one will be completely hidden.

Thanks

Did you also include the code from the second grey box?

That code goes in the This_Workbook Module (it's listed at the bottom of the sheets). That code will log every time someone opens, prints or closes the workbook.

There are no macros to run, the updates will be done automatically.

Yes, I copied both the code to the file as directed.

As the person who posted it claimed the below things:

1. Add sheet named Log in case it is does not exist in the file. - DONE
2. Record user activity based on events to Log sheet. - Cannot see anything being recorded on the Log Sheet
3. Along with recording a event, it records user information and time of event.Cannot see anything being recorded on the Log Sheet
4. Manage log size and removes old entries while creating space for new entries.- No Log yet, hence could not try this option.
5. User can view the log but can not make changes to the log. - No Log yet, hence could not try this option.

All I can see new within the File is that there are 2 Macros : 1) View 2) Hide

When I run them, the "Log" sheet appears and disappears. But it is all blank, no logs in it at all.

Am I doing something incorrect

I'm assuming that you have closed the file and re-opened it?

It will only log, saves prints and opens.

Yeah, I saved and reopened the File.

Tried changes, saved and reopened it from My computer. Even asked my colleague to do certain changes to the file & save from her machine. (The File is on shared network drive)

Unfortunately, no Logs are showing what so ever.

The Log sheet which was asked to be created is completely blank all the time.

I suspect in that case then you don't have the code in the right place. The code works fine for me.

Are you sure you have placed the code from the second grey box in the ThisWorkbook module. It won't work if it isn't in that particular place

Yeah I added the code as directed on the website.

On This workbook>> Insert Module>> and then I pasted the Big code in that Module.

The Small Code was pasted in the sheet itself by opening the code box by double clicking on it.

I just created a fresh file and tried again. It is still the same.

Have attached it for your reference.

Please Guide.

No, not the sheet. The THISWORKBOOK module, as I mentioned above, it's at the bottom of the sheets

Ohh, Now that I made the changes, I finally got the Log.... Whew.

Thanks a Ton for being so patience with me

However, this Code only shows the User who opened and saved the changes. Cannot this code list the modifications done as in how we see In Track Changes?

My major concern is I doubt that one of my assistant is manipulating data and hence I tried the sharing and track changes. I also password protected it.

But since last week changes done by her is not recorded. Don't know why??? Though certain small changes are visible, like spell correction in any cell, but major entries that she made in the file is not picked up by track changes!!!

Since track changes option limits certain function in excel. I was hoping that this Code will help me to keep a log of entries done/manipulated and also this tracking would be a hidden tracking.

Are there any other code which can fulfill this requirement.

Many Thanks once again.