I want to connect my PHP applications to Microsoft Excel and allow Excel to pull and push data into MySQL tables. The first step on the journey is to create an Excel login form.
Creating the Excel Login Form
From the Visual Basic window, insert a new form and name it LoginForm. Add two fields and name them “UserID” and “Password”. Finally add a “Connect” button to the form.
When you are finished, you should have a form that looks something like the above picture. See the Excel Easy post “Userform” if you need help. http://www.excel-easy.com/vba/userform.html
Saving the User ID to the Registry
Having to type the user id over and over is a waste of time. Saving the user id into your local registry and setting the focus to the password field is simple way to to provide automation for the user. We can do this by add form initialization code into the visual basic for the application. By right clicking on the form from the lefthand side “Project” tree you can view the code for the form.
Add the following to save the user id to the registry.
Private Sub UserForm_Initialize()
' Get any saved user id from the registry.
Me.UserID.Value = GetSetting("StormesConnect", "UserSettings", "UserID")
' If we have a user id then skip the user id field and move on to the password.
If (Len(Me.UserID.Value) > 1) Then
Private Sub ConnectButton_Click()
' Save the user id into the registry.
Call SaveSetting("StormesConnect", "UserSettings", "UserID", Me.UserID)
Adding the login icon the the Quick Access Toolbar
Now we need a way to trigger the login form. By putting a icon in the Quick Access Toolbar we can give the user quick access to the login form.
First we will need a entry point for our form. From the Visual Basic window select “Insert” then Module. This should insert a new module and open a code window.
Assuming you named your form “LoginForm” add the following code to you module.
' Entry Point from Quick Access Bar Icon.
This is the subroutine that will be called when we click the quick connect icon.
Next, right click on the title bar on the save icon and select “Customize Quick Access Toolbar…”.
From the Options menu, change the Commands to “Macros”, change the “Customize Quick Access Toolbar” to “For (You file name)”, select “Connect_Click” from the lefthand side and click the “Add > >” Button. Finally click “OK” to save your options.
You should now see your icon on the Quick Access Toolbar.
By clicking your new icon you should see your login form.
Clicking the Connect button will save your User ID into the registry, closing the login form and reopening will show your the last User ID entered with the Password field selected.
In Part 2 we will connect to a PHP application using VBA-Web library https://github.com/VBA-tools/VBA-Web.