Results 1 to 2 of 2

Thread: environ("username") alternative
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jun 2016
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default environ("username") alternative

    Hi,

    Hoping someone can point me in the right direction with this one.........thanks in advance.

    So as part of the company structure individuals have different access to spreadsheets based on their username.
    Some can only view certain sheets, some read only, some have full access etc etc.

    At present this is controlled through code using If Environ("username") = X then do something or if Environ("username") = y then do something different.

    The problem with this is that it is in around 30 different workbooks. As the staff within the company change it means having to go back and add or remove them from each of the individual workbooks.

    What I would like to do is keep one central list in a separate workbook which I can update as necessary and have each of the other workbooks refer to that list before allowing the appropriate access level. That way it is only 1 list to update.

    I have googled and tried various things but cant find a suitable method to achieve this. Can anyone help?

  2. #2
    Board Regular
    Join Date
    Jan 2018
    Posts
    268
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: environ("username") alternative

    Quote Originally Posted by gordsky View Post
    Hi,

    Hoping someone can point me in the right direction with this one.........thanks in advance.

    So as part of the company structure individuals have different access to spreadsheets based on their username.
    Some can only view certain sheets, some read only, some have full access etc etc.

    At present this is controlled through code using If Environ("username") = X then do something or if Environ("username") = y then do something different.

    The problem with this is that it is in around 30 different workbooks. As the staff within the company change it means having to go back and add or remove them from each of the individual workbooks.

    What I would like to do is keep one central list in a separate workbook which I can update as necessary and have each of the other workbooks refer to that list before allowing the appropriate access level. That way it is only 1 list to update.

    I have googled and tried various things but cant find a suitable method to achieve this. Can anyone help?
    Are your files saved to OneDrive?

    I'm not to savvy with formulas so I don't know if there is one to do this nor have I ever done something with central workbook linking, howevere there are two methods that come to mind.

    Option 1 :

    Assuming everyone is using Windows then you can use a bit of Web Scraping on a text file loaded to dropbox to check for authorized users.

    This example is for checking if there is a new version of an excel file available on DropBox. The concept is the same, except that you would have to use more/different delimiters for the levels of access each person has. Dropbox link needs to be shared.

    Code:
    Private Function Update_Date() As Boolean
    
    
    Dim Path As String, Update As Double, dd As Byte, WinHttpReq As Object, FileN As String, Update_Range As Range, _
    URL As String, html As Object, STR_AR() As String, x As Byte, File_Type As String
    
    
    Set Update_Range = Variable_Sheet.ListObjects("Saved_Variables"). _
        DataBodyRange.Find("Update", LookIn:=xlValues, LookAt:=xlWhole).Offset(0, 1)
        
    File_Type = Update_Range.Offset(1, 0).Value2
    
    
    FileN = "Date_Check.txt"
    
    
    x = Application.Match(File_Type, Array("L", "D", "T"), 0) - 1 '3 different file types are managed
    
    
       #If  Mac Then
           
       #Else  'if on windows
        
            URL = Replace("https://www.dropbox.com/s/7lwhmhul1pqxbf9/Date_Check.txt?dl=0", _
                "www.dropbox.com", "dl.dropboxusercontent.com")'this text file has the version numbers of the most recently uploaded file
                
            Set html = CreateObject("htmlFile")
            
            Set WinHttpReq = CreateObject("MSXML2.XMLHTTP")
                
                With WinHttpReq
                
                    .Open "GET", URL, False ' False means that it has to make the connection before moving on
                    .send         
        
                    html.Body.innerHTML = .responseText
                
                End With
                
                If Round(Update_Range.Value2, 10) < Round(Split(html.Body.FirstChild.Data, ",")(x), 10) Then Update_Date = True'checking version
                'Array elements in order [L,D,T]
        #End  
    
    Exit Function
    
    No_Query:
    
        MsgBox "Check for workbook update failed" 
           
    End Function
    Option 2:

    Alternatively if everyone has access to Power Query [Note: I've never used Power Query for MAC] then you can Query the Central Workbook for different tables assuming users have access to it through your network [like a shared OneDrive Folder?] and load each of them to a hidden sheet in all 30 of your workbooks. Then you can use a vba Function like:
    Code:
    Function Allow_Access(Table_Name As String) as boolean
    
    dim Allowed as range
    
    set Allowed=Hidden_Sheet.listobjects(Table_Name).databodyrange'hidden sheet is the sheet that holds the tables with different levels of access 
    
    if not allowed.find(Environ("UserProfile"), LookIn:=xlValues, LookAt:=xlWhole) is nothing then Allow_Access=True 'if it is found within the table
    
    end Function
    The above function can be edited for a hierarchy of access if needed ie: if found in table 3 then level 3 access is given
    Last edited by MoshiM; Oct 15th, 2019 at 07:47 PM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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