Counting the number times a file is opened

mr_pivot

New Member
Joined
Sep 2, 2009
Messages
6
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

Well-known Member
Joined
Apr 21, 2004
Messages
7,896
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.
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi,

you could put this in the workbook module
Code:
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

Well-known Member
Joined
Jul 9, 2004
Messages
904
Remember of course that none of these solutions work if the user selects "Disable Macros" ...
 

mr_pivot

New Member
Joined
Sep 2, 2009
Messages
6
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

Well-known Member
Joined
Jul 9, 2004
Messages
904
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
I couldn't really resist and "made" some sparetime... I'm quite sure this code could be made shorter. Works nicely for me.

Code:
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
 

Forum statistics

Threads
1,081,560
Messages
5,359,608
Members
400,538
Latest member
leon_oscar

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top