×

Allow User-Specific Login Credentials To Be Pulled With Excel VBA

By Chris Newman •  Updated: 11/17/21 •  7 min read
Excel VBA Multiple User storing and reading passwords

There are a ton of great data programs out there that integrate very nicely with Excel. The problem I see handled poorly over and over again is that analysts will store their login credentials directly inside their automated VBA code or even worse, the spreadsheet itself!

In this tutorial, I want to show you an easy way to bring a user’s unique username and password into a VBA macro without exposing this sensitive information within the VBA project or the Excel spreadsheet file itself.

A Real World Scenario

Let’s imagine a real-world scenario that happens all the time in the corporate world.

John wants to create an automated analysis spreadsheet that his 2 co-workers can also use to perform analysis for their company. This spreadsheet will be stored on the company’s shared drive so anyone can access the file whenever needed.

John’s initial thought is to gather his and his co-worker’s username and password and hard code this information into his macro so none of them have to keep logging into the database system when they want to query information.

John uses Array variables to store the credentials into lists that correspond with their individual computer’s ID number. The VBA code looked something like this:

Sub DatabaseLogin()
'PURPOSE: Log into Company's Database via Excel VBA

Dim ID_List As Variant
Dim Username_List As Variant
Dim Passoword_List As Variant
Dim Username As String
Dim Password As String
Dim x As Integer

'Team Members' Credentials
  ID_List = Array("x302918", "x294832", "x392490")
  Username_List = Array("JohnSmith", "JasonBorne", "FelixHarp")
  Passoword_List = Array("ILuvMommy5", "HulkMan123", "IneedSleep665")

'Test for Team Memember's ID
  For x = LBound(ID_List) To UBound(ID_List)
    If Environ("USERNAME") = ID_List(x) Then
      Username = Username_List(x)
      Password = Username_List(x)
      Exit For
    End If
  Next x

'Unidentified User, must manually login
  If Username = "" Then
    Username = Application.InputBox("Enter Your Username")
    Password = Application.InputBox("Enter Your Password")
  End If

'Log Into Database
  AccessDatabase Username, Password, "CustomerDatabase"
   
End Sub

Besides the humiliation of sharing the dorky passwords you created when you were in middle school, hopefully, it is obvious why this above scenario would not be preferred by any means!

A Better Way To Pass-Thru Credentials With VBA

The way I prefer to address this issue is to have each individual store their credentials on their own secure computer. To do this, they can simply create a text file in NotePad in a CSV (comma separated) format. Within the text file, have each user type their username and password (separated with a comma) as shown below.

Excel VBA multiple user login private credential information text file

You need all your users to save the file with the same file name and in the same location on their computers. In this example, the file name will be “Credentials.txt” and the file location will be on the computer’s Desktop (you could easily use the Documents folder or a custom-named folder within the C\ drive).

VBA To Pull Log-In Information From A Text File

The foundation of this VBA code will come from an article I wrote entitled: The VBA Guide To Interacting With Text (.Txt) Files. Within this post, there is code that shows you how to extract text from within a text file with VBA coding. You will need to use this process in order to pull the credential string of text out of the text file created in the previous section. Below is the completed solution and I will walk you through each part of the code in the following section.

Sub DatabaseLogin()
'PURPOSE: Log into Company's Database via Excel VBA & Desktop Text File

Dim Username As String
Dim Password As String
Dim TextFile As Integer
Dim FilePath As String
Dim Credentials As Variant

'File Path of Text File
  On Error Resume Next
    FilePath = Environ("USERPROFILE") & "\Desktop\Credentials.txt"
  On Error GoTo 0
  
'Determine Login creditials (manual or automatic from desktop file)
  If FilePath = "" Then
    Username = Application.InputBox("Enter In Your Username")
    Password = Application.InputBox("Enter In Your Password")
  
  Else
    'Determine the next file number available for use by the Open function
      TextFile = FreeFile
    
    'Open the text file
      Open FilePath For Input As TextFile
    
    'Store file content inside array variable and determine login info
      Credentials = Split(Input(LOF(TextFile), TextFile), ",")
        Username = Credentials(0)
        Password = Credentials(1)
    
    'Close Text File
      Close TextFile
   
  End If

'Log Into Database
  AccessDatabase Username, Password, "CustomerDatabase"
   
End Sub

Explaining How This VBA Macro Works

What this VBA code first does is look for a specified text file within the desktop. You can pull the unique default drive path by using the Environ function. For my computer, Environ(“USERPROFILE”) equates to C:\Users\Chris. You can then append the rest of the folder path and the file name to the Environ function to create a complete path unique to the currently running computer. An error handling statement is wrapped around this line of code in case the user does not have the file saved to the specific location you designate (in which case they will need to manually enter their login information).

'File Path of Text File
  On Error Resume Next
    FilePath = Environ("USERPROFILE") & "\Desktop\Credentials.txt"
  On Error GoTo 0

Next, there is an IF statement to determine if the login credential file was found on the computer. If manual inputs are needed, the user will be prompted by an input box asking for the username and then the password.

'Determine Login creditials (manual or automatic from desktop file)
  If FilePath = "" Then
    Username = Application.InputBox("Enter In Your Username")
    Password = Application.InputBox("Enter In Your Password")
  Else
    'Credentials were able to be pulled from the text file
  End If

If the VBA code was able to find the text file within the designated location, it will then open up the file and extract the text stored inside. Next, a Split function is used to separate the string of text by the comma symbol. All the individual pieces left over from the split are then stored inside an array variable (think list) beginning in position zero. Here is my attempt at showing you visually what is going on:

VBA Split function example

And here is this concept coded in VBA:

'Determine the next file number available for use by the Open function
  TextFile = FreeFile

'Open the text file
  Open FilePath For Input As TextFile

'Store file content inside array variable and determine login info
  Credentials = Split(Input(LOF(TextFile), TextFile), ",")
    Username = Credentials(0)
    Password = Credentials(1)

'Close Text File
  Close TextFile

To complete the login process, you will need to know how to specifically log into your company’s database through VBA. This will typically be located in a database’s API documentation. Below are some links to examples of popular business software documentation showing how you can connect to Excel with VBA.

Feel free to help me build this list out even more by leaving a link in the comments section near the end of this article!

Keep Learning

Chris Newman

Chris Newman

Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.

[FREE Training] 10 Amazing Excel Efficiency Tricks. Why Don't People Know These?!

X