workbook directory where is it?

M

Marilyn

Hello I want to keep track of the people who open a workbook. I added
the following code to the workbook module
this is what I did it
right clicked on the worksheet
view code
selected insert module
this is the code , that I copied from a book
Private Sub Workbook_Open()
Open ThisWorkbook.Path & "\usage.log" For Append As #1
Print #1, Application.UserName, Now
Close #1
End Sub

I made a change to the file and saved the file . then I opened the file
again but I do not know how to view the usage log.

The instructions tell me that the file is stored in the workbook's
directory, and it is named usage.log.
How do I access the workbooks directory.

thanks
 
J

JLGWhiz

If the file was created, you should be able to view it by clicking on the
file open icon while the workbook is open. That should display all files in
the workbook's directory and the usage.log file should be listed. You might
have to change the file types at the bottom of the file open dialog box to
show All Files (*.*)
 
D

Dave Peterson

You stored the workbook in a common folder so that all the users would use that
single copy?

If yes, then you should know where the folder is. Heck, if you opened that file
to make the change to the workbook_open procedure, you should know where that
folder is.

Just open windows explorer
travers to that drive/folder
Open Notepad and then use File|Open to open the log file.

You may want to consider changing the name of the file to usage.txt and change
the code to match, too.

Then you could just double click on that .txt file and the program assocated
with .txt files (usually NotePad) will open the file.

================
On the other hand, if you shared this workbook with others (like via email),
then the users could have stored it on any drive/folder they have access
to--their C:\, their network drives, their thumbdrives...

And you won't have any way to know or access that location.

But on the third hand...

You could change the log file location to a common network location--so that
everyone can write to it and everyone (You!!) can read it.

I would change the print statement to:

Print #1, Application.UserName, Now, me.fullname

Then when you look at the log file, you'll be able to see the user name, time,
and where they stored the file.

====
Be careful, though. There may be times when the common network file is
unavailable to the user--maybe their working on their laptops while not
connected to the network??? The user would get an error.

Depending on what you want, you could either avoid the error (and ignore
updating the log)--or just close the file and not let them work on it.
 
R

Rick Rothstein

selected insert module

If you actually did the above step (from the list of steps you showed us),
then you put your code in the wrong location... you need to put that code
(which is event code, not a macro) in the ThisWorkbook module. To get to
this module, go into the VB editor and look at the Project window (it is the
one that lists all your worksheets... the last item in the worksheet list
should say ThisWorkbook... double click that item to open its window and
then copy/paste your code into that window (don't forget to delete the code
from the module you originally inserted or you will get a duplicate
definition error). Now when you open the workbook, your code will run
automatically.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top