Consulting

Results 1 to 10 of 10

Thread: Environ Function Help!

  1. #1
    VBAX Newbie
    Joined
    Oct 2008
    Posts
    2
    Location

    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

    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!!

  2. #2
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,903
    Location
    [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.

  3. #3
    VBAX Regular Apps's Avatar
    Joined
    May 2006
    Posts
    38
    Location
    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?

  4. #4
    VBAX Newbie
    Joined
    Oct 2008
    Posts
    2
    Location
    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...

  5. #5
    VBAX Regular Apps's Avatar
    Joined
    May 2006
    Posts
    38
    Location
    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 OLEDBatabase 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 OLEDBon'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.

  6. #6
    VBAX Regular Apps's Avatar
    Joined
    May 2006
    Posts
    38
    Location
    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.

  7. #7
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,903
    Location
    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]

  8. #8
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,903
    Location
    See attached files.

  9. #9
    VBAX Tutor david000's Avatar
    Joined
    Mar 2007
    Location
    Chicago
    Posts
    275
    Location
    Sub myenvirons
    For I = 1 to 100
    Cells(I,1)=environ(I)
    Next
    End sub
    "To a man with a hammer everything looks like a nail." - Mark Twain

  10. #10
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •