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 Sub
Besides the humiliation of sharing your 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 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 in 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 separated 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 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 documentations showing how you can connect into 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!
How Do I Modify This To Fit My Specific Needs?
Chances are this post did not give you the exact answer you were looking for. We all have different situations and it's impossible to account for every particular need one might have. That's why I want to share with you: My Guide to Getting the Solution to your Problems FAST! In this article, I explain the best strategies I have come up with over the years to getting quick answers to complex problems in Excel, PowerPoint, VBA, you name it!
I highly recommend that you check this guide out before asking me or anyone else in the comments section to solve your specific problem. I can guarantee 9 times out of 10, one of my strategies will get you the answer(s) you are needing faster than it will take me to get back to you with a possible solution. I try my best to help everyone out, but sometimes I don't have time to fit everyone's questions in (there never seem to be quite enough hours in the day!).
I wish you the best of luck and I hope this tutorial gets you heading in the right direction!
Chris :)
