Counter to count times a read-only Excel file is opened

[olympus]

I have an Excel file that is networked as a read-only application. The users utilize the worksheet to perform some calculations, then on close the workbook is coded not to save any changes. However, I would like to have a counterthat tells me how many times this Excel app is opened. It would be ideal if I could get a daily summary of how many times the file is opened. I have tried a simple counter that saves the file before the user can change anything. This results in an unacceptable lag time, as one waits for the networked file to be saved. Also, I'm not sure that this code would work if the file is opened as read only.

[G27] = [G27] + 1
ActiveWorkbook.Save

Any help on this would be appreciated. Thank you.

John

 

[boggg1]

Is the reason the lag time is slow because your network is very slow or because the spreadsheet is big or both ?. What I was thinking is could you retrieve and store the number of times the file is opened in another (much smaller) file (eg a text file) which could form the basis for your report. The text file would auto-load and auto-save from an Excel startup macro and it would all finish quickly because text files are small.

Boggg1

 

[olympus]

Boggg1,
The lag time is due to our network being slow. How would I go about saving the value on sheet1 cell g27 to a text file daily at midnight? I would also like to reset cell g27 to zero after its value is copied to a text file. That way I would know how many times the file was opened each day. Of course I would like the text file to retain each days total. As I am a novice at VB I would appreciate your help. Thanks.
John

 

[jm314]

Sub use_counter()

Dim sPath As String
Dim iFileNum As Integer

sPath = "C:\Documents and Settings\All Users\Desktop\filecounter.txt"
iFileNum = FreeFile

Open sPath For Append As iFileNum 'open or create the text file
Print #1, Now 'write current date and time to text file

Close #1 'close text file

End Sub

This procedure creates or updates a log file each time it is called. "Now" is the current date and time. I haven't included any kind of error handling - that's up to you. To get a count on how many times your excel file has been accessed, run a procedure that looks at the text file and counts how many lines there are. The following function does this (again without any error handling).

Function retrieve_access(sPath As String) As Long
Dim iFileNum As Integer
Dim i As Long
Dim sTemp As String

iFileNum = FreeFile
i = 0

Open sPath For Input As iFileNum

Do While Not EOF(iFileNum)
  Line Input #iFileNum, sTemp
  i = i + 1
Loop

Close #iFileNum

retrieve_access = i

End Function

 

[jm314]

Whoops, I just realized I put the cart before the horse. The code above will give the result you are looking for only if it is executed each time the excell file is opened. I don't know where to put the code so that will happen.

 

[jm314]

I just figured out how to get the code to execute. Follow the url to Microsoft's explaination of where to put the code.

http://office.microsoft.com/en-us/assistance/HA010346281033.aspx

look under "Creating a VBA procedure for the Open event of workbook" on that page

Actually, you only need to put a call to the use_counter() procedure in the Workbook_Open() event-procedure. The use-counter procedure should go in a code module and the first line should be altered to:

Public Sub use_counter()

 

[boggg1]

Are you happy olympus ? jm314 has covered one way to do this using two principals:

1) The uses figure is storred in an external file (not the spreadsheet) to reduce required LAN bandwidth
2) The action of opening the spreadsheet triggers the Visual Basic which updates the counter

It is interesting that this method will allow multiple users to open the spreadsheet at the same time whereas storring the counter in the spreadsheet will not.

As a self-confessed beginner you may need more help - please feed back.

Boggg1

 

[olympus]

jm314 & Boggg1,

Thank you. My application will be placed on the network today, so I will be able to try your solutions. I appreciate you taking the time to helping out a beginner.
John