Counting the number times a file is opened

[mr_pivot]

I have a workbook setup as Read only for reporting purposes. I would like to capture the number of times and if possible, the users that are opening the file....is there a way?

thanks,
MP

 

[SteveO59L]

Private Sub Workbook_Open()
Sheets("stats").Activate
Range("f" & Rows.Count).End(xlUp).Offset(1).Value = Environ("username")
End Sub


But if the file is Read-Only, it cant be saved, and therefore neither will the record of who opened it.

 

[Nogslaw]

This may or may not work for you, but it was suggested as a solution to a different problem I was having. Essentially, when the file is opened, saved, or closed, the macros update a text file located in the same folder as the actual file.

Here is a link to the thread:

http://www.mrexcel.com/forum/showthread.php?t=337833&page=2

 

[mr_pivot]

Thanks...I will give this a try!

MP

 

[erik.van.geit]

Hi,

you could put this in the workbook module

Private Sub Workbook_Open()
    Open "C:\test.txt" For Append As #1
        Print #1, Now, Environ("username")
    Close #1
End Sub

TO INSTALL IN THISWORKBOOK CODE WINDOW:
1. Rightclick the little Excel-icon on the topleft of your page just beside the Filemenu
2. Select "View Code" in drop down menu
3. VBE window gaat open ... paste code in and exit VBE

kind regards,
Erik

EDIT: of course you will change the path to your suits

 

[Nogslaw]

Remember of course that none of these solutions work if the user selects "Disable Macros" ...

 

[mr_pivot]

Good to know. Our users have been educated that if the disable macros, they will not see the most current information as this file is setup to import data from a text file. They also loose some functinality.

MP

 

[Nogslaw]

Hey Erik - two questions:

1. I haven't worked with text files before. Can you explain the "For Append As #1" and the "Print #1" portions?

2. How could you modify the code you provided to remove the first line of the text file before adding a new line at the bottom? Say, if you wanted to limit the text file to only the last 10 entries or something.

 

[erik.van.geit]

I didn't work with them a lot neighter, it should be quite easy once you know the syntax, but I'm short of time to check this out.
Click in the word "append" and press function key F1 to get help: you will be pointed to "OPEN, INSTRUCTION"

 

[erik.van.geit]

I couldn't really resist and "made" some sparetime... I'm quite sure this code could be made shorter. Works nicely for me.

Sub KeepLastLinesOfTextFile()
'Erik Van Geit
'091015
 
'delete all lines in a textfile except the last ones
 
Dim LineIn As String
Dim FirstLine As Long
Dim i As Long
Dim j As Long
Dim arr() As Variant
 
Const FileNm As String = "C:\test.txt"
Const NrLines = 10 'number of lines to keep
 
ReDim arr(1 To NrLines)
 
'count total number of lines
Open FileNm For Input As #1
 
    Do While Seek(1) <= LOF(1)
    Line Input #1, LineIn
    i = i + 1
    Loop
 
Close #1
 
If i < NrLines + 1 Then Exit Sub
FirstLine = i - NrLines + 1
 
'get last lines
Open FileNm For Input As #1
 
i = 0
    Do While Seek(1) <= LOF(1)
    Line Input #1, LineIn
    i = i + 1
        If i >= FirstLine Then
        j = j + 1
        arr(j) = LineIn
        End If
    Loop
 
Close #1
 
'write only last lines
    Open FileNm For Output As #1
    Print #1, Join(arr, vbNewLine)
 
Close #1
 
End Sub