Allow User-Specific Login Credentials To Be Pulled With Excel VBA
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 SubBesides 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.
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 SubExplaining 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 0Next, 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 IfIf 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:
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 TextFileTo 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!
After 10+ years of creating macros and developing add-ins, I've compiled all the hacks I wish I had known years ago!
Keep Learning
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.