PDA

View Full Version : [SOLVED] Shared workbook



jphermans
05-22-2005, 12:01 AM
Hi,

Lets say the workbook is shared, how do i get the list of users currently accessing the workbook? And what time they logged in?

Thankx in advance

jbw92
05-22-2005, 05:33 AM
Hi,

see if this helps:

http://www.vbusers.com/code/codeget.asp?ThreadID=512&PostID=1&NumReplies=0
(http://www.vbusers.com/code/codeget.asp?ThreadID=512&PostID=1&NumReplies=0) http://www.vbusers.com/code/codeget.asp?ThreadID=511&PostID=1&NumReplies=0 (http://www.vbusers.com/code/codeget.asp?ThreadID=512&PostID=1&NumReplies=0)


Regards,
Joop

jphermans
05-23-2005, 09:38 AM
Hi,

see if this helps:

http://www.vbusers.com/code/codeget.asp?ThreadID=512&PostID=1&NumReplies=0
(http://www.vbusers.com/code/codeget.asp?ThreadID=512&PostID=1&NumReplies=0) http://www.vbusers.com/code/codeget.asp?ThreadID=511&PostID=1&NumReplies=0 (http://www.vbusers.com/code/codeget.asp?ThreadID=512&PostID=1&NumReplies=0)


Regards,
Joop

With these examples i can see who's working on the file on that moment.
What i want is looking at the end of the day , who has working today with the file ?

Any idea?

geekgirlau
05-23-2005, 05:26 PM
How about setting up a log file when they open the workbook? Add this code to the Workbook Open event.



Private Sub Workbook_Open()
Dim varUsers() As Variant
Dim strHistory As String
Dim strRecord As String
Dim i As Integer
strHistory = "C:\LogSharedHistory.txt"
' if log history doesn't exist, create and add headings
If Dir(strHistory, vbNormal) = "" Then
Open "C:\LogSharedHistory.txt" For Append As #1
Print #1, "User" & vbTab & "Date opened" & vbTab & "Mode"
Else
Open "C:\LogSharedHistory.txt" For Append As #1
End If
' capture user data
varUsers = ActiveWorkbook.UserStatus
For i = 1 To UBound(varUsers, 1)
strRecord = varUsers(i, 1) & vbTab & _
varUsers(i, 2) & vbTab
Select Case varUsers(i, 3)
Case 1
strRecord = strRecord & "Exclusive"
Case 2
strRecord = strRecord & "Shared"
End Select
Print #1, strRecord
Next
Close #1
End Sub

Bob Phillips
05-23-2005, 05:36 PM
How about setting up a log file when they open the workbook?

Good idea, but how about the event log? Better than all those messy text files.

johnske
05-23-2005, 06:38 PM
Hi geekgirl,

Just as a FYI... there is no need to test for the existence of the .txt doc (and the If-Then-Else statement). The line


Open "C:\LogSharedHistory.txt" For Append As #1

automatically creates the file if it doesn't exist (and opens it if it already exists).

(I used to do exactly the same test as yourself and recently discovered it was unnecessary for .txt files...)

Regards,
John :hi:


EDIT:

OOPS! - Sorryfootinmout My boo-boo, just had a closer look and see now the idea was to put some headings (Please forget I said anything :hide: )

geekgirlau
05-23-2005, 09:03 PM
Good idea, but how about the event log? Better than all those messy text files.

xld, you are absolutely correct - why is it the simplest solutions are the hardest to find?



With ActiveWorkbook
.HighlightChangesOptions When:=xlAllChanges
.ListChangesOnNewSheet = True
.HighlightChangesOnScreen = True
End With

Bob Phillips
05-24-2005, 01:26 AM
xld, you are absolutely correct - why is it the simplest solutions are the hardest to find?

I'll leave it to you to write that:).

The event log is good (great?), but not easy to write to. VB6 has an App.LogEvent, or some such method, but VBA doesn't, so it needs APIs. Best to shove it out to a class file and abstract it.