VBA LOG - best way to do it

[mahmed1]

Hi All

Please can you help me the best possible way to ensure I capture this
I have a workbook that I send out via email to people. In this workbook, in the workbook open code, I keep a log and update another excel file with the userid and timestamp regardless of whether the workbook I sent is for read only. The log file is closed straight away in code when its updated.
The problem im having is that if the log file is opened when im updating then it wont uodate the file as its saying its locked for editing.

The file can be opened at the same time so I don’t know how to get around this

What I want is to keep a log of each time this workbook is opened regardless of whether its opened at the same time to keep a track of how many people are viewing this spreadsheet.

What is the best way to keep a log regardless of whether its stored on a text file and then transferred to my excel log file

Please – im hoping someone has an amazing solution to get around this

I thought of creating a new txt file each time its opened somewhere and in the excel log file - have a button that copies the data from each text file and then deleting those text files but I'm not sure if this is the best way

please advise the best way to do it

Thank You

 

[Youngdand]

Could you test to see if the workbook is open, and if True write a temporary file, which you then merge in once available possibly within the same macro.


example from https://support.microsoft.com/en-gb/help/291295/macro-code-to-check-whether-a-file-is-already-open

<code>Function IsFileOpen(filename As String)
    Dim filenum As Integer, errnum As Integer

    On Error Resume Next   ' Turn error checking off.
    filenum = FreeFile()   ' Get a free file number.
    ' Attempt to open the file and lock it.
    Open filename For Input Lock Read As [URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=filenum"]#filenum[/URL] 
    Close filenum          ' Close the file.
    errnum = Err           ' Save the error number that occurred.
    On Error GoTo 0        ' Turn error checking back on.

    ' Check to see which error occurred.
    Select Case errnum

        ' No error occurred.
        ' File is NOT already open by another user.
        Case 0
         IsFileOpen = False

        ' Error number for "Permission Denied."
        ' File is already opened by another user.
        Case 70
            IsFileOpen = True

        ' Another error occurred.
        Case Else
            Error errnum
    End Select

End Function</code>

 

[Logit]

.
My mind is working from the other direction. Why would the Log File be opened ? Does it have a multi-purpose ? If so ... why ?

My first inclination is to insure the Log File is closed at all times. KISS principle.

 

[Youngdand]

From what i could gather on sheet open it opens the file writes the log and closes, the problem is, if he is using the log file, or if people open the sheet simultaneously. Possibly, he would be better writing the log data to an access DB if he has the opportunity.

 

[mahmed1]

Thank you - re post #4 - this is my problem so i guessill have to go down the access route