Environ Function Help!

Hello All,

VBA and Forum Newb here, thanks very much for the help I have acquired by browsing this site...being new to VBA I have been buried in techy/programmer websites all week...mainly doing this :think:

Anyways, my problem

I provide a regular weekly report, using Excel 2003, to the company I work for (approx 100 users). I have been tasked with testing how much this report is opened and read (not just the Email with the report attatched, the Report itself)

I believe the ENVIRON User name function COULD be part of the solution (in that, it can acquire the user name) the tricky part is coding it in such a way that I as the sender am informed or a table is updated each and every time one of the users opens the sheet....

Apologies if this isn't how I should be asking for help...if you have questions, please let me know.

Thanks again for any previous or future help!! :friends:

[vba]MsgBox Environ("username")[/vba] If you want to be notified, then you probably want the Open event to email you. The method depends on what email program you are using. Of course the user may not like that.

A typical audit method is to hide a sheet and keep the information in it. Another method is to put the information in the user's Registry. The last method, is to put it in an external file. This can be a database, a TXT file, or some other type.

Hi,

We do a similar thing on a number of reports and solutions used in the company I work for, so a basic code you could use would be:

[VBA]xOpened$=Environ("Username") & " " & Date & " " & Time[/VBA]
or
[VBA]xOpened$=Environ("Username") & " " & Format(Now,"dd/mm/yyyy hh:mm")[/VBA]

This second example would give you a text string of something like "RyanDale44 03/10/2008 13:58", or you could use the first example to have the components of User/Date/Time recordedly seperately.

Whatever you choose, this code is just placed in the ThisWorkbook section of the VBE as

[VBA]Private Sub Workbook_Open()

'####code goes here

End Sub[/VBA]

...so the code will execute when the user Opens the excel file.

The next question is now that you have the information you want - what are you going to do with it? How are you looking to collate all of these entries together to track who was using the report and when? Are you planning to use a database of some sort - Access/SQL?

Quote:

---

Originally Posted by Apps

Hi,

We do a similar thing on a number of reports and solutions used in the company I work for, so a basic code you could use would be:

[vba]xOpened$=Environ("Username") & " " & Date & " " & Time[/vba]
or
[vba]xOpened$=Environ("Username") & " " & Format(Now,"dd/mm/yyyy hh:mm")[/vba]

This second example would give you a text string of something like "RyanDale44 03/10/2008 13:58", or you could use the first example to have the components of User/Date/Time recordedly seperately.

Whatever you choose, this code is just placed in the ThisWorkbook section of the VBE as

[vba]Private Sub Workbook_Open()

'####code goes here

End Sub[/vba]

...so the code will execute when the user Opens the excel file.

The next question is now that you have the information you want - what are you going to do with it? How are you looking to collate all of these entries together to track who was using the report and when? Are you planning to use a database of some sort - Access/SQL?

---

Excellent, thanks for that. I am hoping to collate the entries in an Access table, ideally. Each time the report is sent we can clear the table and gauge the "Readrate" of the most recently sent report. Do you havea solution/suggestion for that? Like I say...I'm reading hard at the mo trying to keep up, but generally, I'm a VBA beginner...

Try this:

[vba]Private Sub Workbook_Open()

usr$ = Environ("Username")

Dim cN As ADODB.Connection
Dim rS1 As ADODB.Recordset
Dim connString As String
Dim returneddata As Variant
connString = "Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin; _
Data Source=##DatabasePath&Name###.mdb;Mode=Share Deny None;Jet OLEDB:Engine _
Type=5;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet _
OLEDB:Global Bulk Transactions=1;Jet OLEDB:Create System Database=False;Jet _
OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet _
OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False"

Set cN = New ADODB.Connection
cN.ConnectionString = connString
cN.ConnectionTimeout = 200
cN.CommandTimeout = 200
Set rS1 = New ADODB.Recordset
cN.Open
rS1.ActiveConnection = cN

xqry = "INSERT INTO [###TableName###] ( User_ID, [Date], [Time] ) SELECT '" & usr & "', '" & Date & "', '" & Time & "'"

rS1.Source = xqry
rS1.Open

End Sub

[/vba]

... just replace the ##DatabasePath&Name### with the filepath & name of your Access database, and create a table with the columns 'User_Name', 'Date' and 'Time' and replace the ###TableName### with the Table name. :thumb

Apologies - forgot to say that this option will just append the records to the database, but you could then write an Access query to filter out records between a certain Date range, which should show you what you need to know.

Hope this is of some help.

I attached an example xls and mdb. The xls uses both this mdb method and the hidden worksheet methods.

To use this code in your workbook, right click the Excel icon just to the left of the File menu, View Code and paste this. You will need to add the ActiveX reference as commented.

You will need to change the path to where you store the mdb if not in that workbook's path in variables, DBFullName and s. This code has more comments in the attached xls to do other things. You can delete them as I did here.

This adds a record for both Open and Close events.

[vba]Private Sub Workbook_BeforeClose(Cancel As Boolean)
UpdateMDB "Close"
End Sub

Private Sub Workbook_Open()
UpdateMDB "Open"
End Sub

Private Sub UpdateMDB(wbEvent As String)
' the Microsoft ActiveX Data Objects 2.x Library
Dim DBFullName As String
Dim Cnct As String, Src As String
Dim Connection As ADODB.Connection
Dim Recordset As ADODB.Recordset
Dim Col As Integer, Row As Integer, s As String

'On Error GoTo EndNow
'Set window and calc off to speed updates

' Database information
DBFullName = ThisWorkbook.Path & "\Audit.mdb"
'Set a table name in the MDB
s = "tblWorkbook"

' Open the connection
Set Connection = New ADODB.Connection
Cnct = "Provider=Microsoft.Jet.OLEDB.4.0; "
Cnct = Cnct & "Data Source=" & DBFullName & ";"
Connection.Open ConnectionString:=Cnct

' Create RecordSet
Set Recordset = New ADODB.Recordset
Recordset.CursorType = adOpenKeyset
Recordset.LockType = adLockOptimistic

With Recordset
' Filter
Src = "SELECT * FROM " & s
Recordset.Open Source:=Src, ActiveConnection:=Connection

'************ Add a record and field values and update MDB table ***************
.AddNew
Recordset("WorkBook") = ThisWorkbook.FullName
Recordset("UserName") = Environ("UserName")
If wbEvent = "Open" Then Recordset("OpenDate") = Now
If wbEvent = "Close" Then Recordset("CloseDate") = Now
.Update 'Push this disconnected database to update the MDB
End With

EndNow:
Set Recordset = Nothing
Connection.Close
Set Connection = Nothing
End Sub
[/vba]

See attached files.

Sub myenvirons
For I = 1 to 100
Cells(I,1)=environ(I)
Next
End sub

Kenneth,

I would change


[vba]Private Sub Workbook_Open()
UpdateMDB "Open"
End Sub[/vba]
to set a value for a global variable (opentime) and then change

[vba]If wbEvent = "Open" Then Recordset("OpenDate") = Now
If wbEvent = "Close" Then Recordset("CloseDate") = Now[/vba]

to

[vba]If wbEvent = "Close" Then Recordset("OpenDate") = opentime
If wbEvent = "Close" Then Recordset("CloseDate") = Now
[/vba] This creates a single record containing both the open and close time.

This could use a bit of a tidy up but it works.

BH