Track User, Computer , Date When Excel Connection is refreshed

[sgaru]

[FONT=&quot]Hi All,[/FONT]
[FONT=&quot]I am fresher to VBA.I need to track and store the data of the users who refresh my Excel Work books. These excel work books are typically connected to OLAP Cubes. I need to get the values - Username, Computer name , Date when refreshed/Accessed to be loaded into my SQL Server database.[/FONT]
[FONT=&quot]Do you have any solutions for this? How do i start with this? Any resources which can help me are greatly appreciated.[/FONT]
[FONT=&quot]Thanks,[/FONT]
[FONT=&quot]SG[/FONT]

 

[mole999]

if you add this code and run it, then look in the immediate window you will see what is already available in the ENVIRON

Sub ListEnvironmentVariables()
'each environment variable in turn
Dim EnvironmentVariable As String
'the number of each environment variable
Dim EnvironmentVariableIndex As Integer
'get first environment variables
EnvironmentVariableIndex = 1
EnvironmentVariable = Environ(EnvironmentVariableIndex)
'loop over all environment variables till there are no more
Do Until EnvironmentVariable = ""
'get next e.v. and print out its value
Debug.Print EnvironmentVariableIndex, EnvironmentVariable
'go on to next one
EnvironmentVariableIndex = EnvironmentVariableIndex + 1
EnvironmentVariable = Environ(EnvironmentVariableIndex)
Loop
End Sub

Then using variations on

Private Sub Workbook_Open()
    Dim LastRow As Long
    LastRow = Sheets("tracker").Range("A100000").End(xlUp).Row
    LastRow = LastRow + 1
    With Sheets("tracker")
        .Range("A" & LastRow) = Now()
        .Range("B" & LastRow) = Environ("USERNAME")
        .Range("C" & LastRow) = Environ("COMPUTERNAME")
        .Range("D" & LastRow) = Environ("LOGONSERVER")
        .Range("E" & LastRow) = Environ("USERDNSDOMAIN")
    End With
End Sub

and combining it with your code on the cube refresh you could have a result

 

[sgaru]

Thanks for the quick reply. Where can I set up SQL connection so that I can write this information to the table directly.

Also how to write code for cube refresh? What method. Sorry for the beginner questions