Usage Tracker

[INSONIC]

Hi all, i'm new to the site.

I have to produce a weekly report in excel and i want to track who open the report.

I have a script that tracks it in the document, but i want it to write it to another excel document on a shared drive, as not everyone saves it.

UserName = Environ("UserName")
myUser = "User: " & UserName & _
", On: " & Application.WorksheetFunction.Text(Now(), "m/d/yyyy at hh:mm AM/PM")
Worksheets("usage").Range("A65536").End(xlUp).Offset(1, 0).Value = myUser
End Sub

 

[JazzSP8]

Welcome to the site!

Can't you just save the workbook as soon as you've wrote to the cell?

UserName = Environ("UserName")
myUser = "User: " & UserName & _
", On: " & Application.WorksheetFunction.Text(Now(), "m/d/yyyy at hh:mm AM/PM")
Worksheets("usage").Range("A65536").End(xlUp).Offset(1, 0).Value = myUser

ActiveWorkbook.Save

End Sub

Thats how I do it

 

[INSONIC]

The only problem i have, is that around 20 people could open the document at any time, so they would open it as a read only, not saving on the document, this way it would write straight to another document that i could have auto save. i dont want to share the document due to errors i find normally.

Thanks everyone for your help

 

[JazzSP8]

Ah - Sorry, bit of a lazy post there...

Try this

Dim wb As Workbook
    Set wb = Workbooks.Open("test.xls")
        wb.Sheets("usage").Select
        UserName = Environ("UserName")
        myUser = "User: " & UserName & _
        ", On: " & Application.WorksheetFunction.Text(Now(), "m/d/yyyy at hh:mm AM/PM")
        Worksheets("usage").Range("A65536").End(xlUp).Offset(1, 0).Value = myUser
        wb.Close savechanges:=True

Obivously replace "test.xls" with your Workbook that you want to record your details in

 

[INSONIC]

That works a treat, thanks you for your help