This is part of a the Excel VBA Export SQL Data Series, in which im building out a VBA application to load in SQL Server data, Links are below to other parts in the series:

Part 1 – Basics
Part 2 – Clean up

In this part of the series we are going to work on the user input form, taking it from a simple input query to using a VBA Form with multiple inputs.

Start with opening up the Excel workbook we have been using, hit the developer ribbon and select Visual Basic

VBAForms00

Create a VBA Form

In Visual Basic right click on Modules -> Insert -> User Form

VBAForms01

From here, use the Toolbox (It should pop up by default) and set up the below layout Using Labels, Textboxs and Buttons. (Im just dropping them on and re-sizing so wont show this step by step).

VBAForms02

Now we do some clean up of the properties, Updating each object on the forms Name and update the Label Text.

First the form, Update the Name to SQLForm and Caption to “SQL Server Input Form” Properties panel should look like:

VBAForms03

Update Label1, change the Name to InstanceLbl and Caption to SQL Instance

VBAForms04

Also update all the other lables like below:

Label1 – InstanceLbl – SQL Instance
Label2 – DatabaseLbl – Database
Label3 – LoginLbl – Login
Label4 – PasswordLbl – Password
Label5 – QueryLbl – User Query

It should end up looking like:

VBAForms05

Now do the same for the TextBox’s, with a few tweaks for the password and the User Query, I put default text to make testing it works easier and gives you a better idea how it works. Working from top to bottom:

Name – InstanceTxt, Text – localhost
Name – DatabaseTxt, Text – master
Name – LoginTxt, Text – stephen (Put in a login that works for your database here!)
Name – PasswordTxt, Text – Pa$$w0rd (Put in a login that works for your database here!), PasswordChar – * (This will mean any text you enter in the PasswordTxt textbox will show u as a *.
Name – QueryTxt, Text – select * from sys.databases, MultiLine – True

VBAForms06

Finally the buttons,

Name – CancelBtn, Caption – Cancel
Name – ConfirmBtn, Caption – Confirm

VBAForms07

Basic Controls

Now the form is created we need to get basic functionality, On the Input Worksheet we need to update the button to now point to the Form instead of the old macro. We then need to make the Form Close or load the query from the bottoms we added.

First lets create a new Macro to use on the button we added. Copy the below text into the VB Module.


Sub OpenInputWizard()

' OpenInputWizard Macro
    Load SQLForm
    SQLForm.Show

End Sub

Now we need to assign our Button on the Input Sheet, to this new Macro instead of the old one.

Simple right click on the button and select “Assign Macro…”

VBAForms08

Now pick the new OpenInputWizard Macro.

VBAForms09

Now we should get our Form to open when we click out button and can close it via the red top box in the corner, all other buttons do nothing.

Now lets set the Cancel button to simply close the form. In VB, double click on the SQLForm under the Forms. to bring up the form window, Now double click on the Cancel Button This brings up a Macro for the Click processes on a button

Update the code to

Private Sub CancelBtn_Click()
    Unload Me
End Sub

Next we need to load all our old code into the Confirm button, before we do a little clean up.

Double Click on the Confirm button to create a function on Click and paste in the old code like below:

Private Sub ConfirmBtn_Click()

'ClearFormatting Macro'
    Sheets("Data").Select
    Cells.Select
    Selection.Clear
    Range("A1").Select

'Input query'
    Dim dbQuery As String
    dbQuery = InputBox("Enter Query")
     
'Declare variables'
    Dim objMyConn As ADODB.Connection
    Dim objMyCmd As ADODB.Command
    Dim objMyRecordset As ADODB.Recordset
    Dim iCols As Integer
    Dim tbl As ListObject
 
    Set objMyConn = New ADODB.Connection
    Set objMyCmd = New ADODB.Command
    Set objMyRecordset = New ADODB.Recordset
 
'Open Connection'
    objMyConn.ConnectionString = "Provider=SQLOLEDB;Data Source=.;Initial Catalog=AdventureWorks2012;User ID=stephen;Password=Pa$$w0rd;"
    objMyConn.Open
 
'Set and Excecute SQL Command'
    Set objMyCmd.ActiveConnection = objMyConn
    objMyCmd.CommandText = dbQuery
    objMyCmd.CommandType = adCmdText
 
'Open Recordset'
    Set objMyRecordset.Source = objMyCmd
    objMyRecordset.Open
    For iCols = 0 To objMyRecordset.Fields.Count - 1
    Worksheets("Data").Cells(1, iCols + 1).Value = objMyRecordset.Fields(iCols).Name
    Next
 
'Copy Data to Excel'
    Worksheets("Data").Range("A2").CopyFromRecordset objMyRecordset
 
'Format Table'
    Sheets("Data").Select
    ActiveSheet.ListObjects.Add(xlSrcRange, ActiveSheet.UsedRange, , xlYes).Name = "Table1"
    ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleLight8"

End Sub

Create Dynamic Stings in VBA

Now we need to just replace the hard coded connection string with the Text values from our TextBoxs.

First we will need to create variables for all the TextBoxs, then attach the Values from them that we have entered like below:

    'Create Variables for the Form'
        Dim dbInstance As String
        Dim dbDatabase As String
        Dim dbLogin As String
        Dim dbPassword As String
        Dim dbQuery As String
    
    'Attach inputs to the variables
        dbInstance = InstanceTxt.Value
        dbDatabase = DatabaseTxt.Value
        dbLogin = LoginTxt.Value
        dbPassword = PasswordTxt.Value
        dbQuery = QueryTxt.Value

Then all we need to do is update the connection string to use the variables.

    'Open Connection'
        Dim myConnectionString As String
        myConnectionString = "Provider=SQLOLEDB;Data Source=" + dbInstance + ";Initial Catalog=" + dbDatabase + ";User ID=" + dbLogin + ";Password=" + dbPassword + ";"
    
        objMyConn.ConnectionString = myConnectionString
        objMyConn.Open

Final code would like:

Private Sub ConfirmBtn_Click()

 'ClearFormatting Macro'
        Sheets("Data").Select
        Cells.Select
        Selection.Clear
        Range("A1").Select

    'Create Variables for the Form'
        Dim dbInstance As String
        Dim dbDatabase As String
        Dim dbLogin As String
        Dim dbPassword As String
        Dim dbQuery As String
    
    'Attach inputs to the variables
        dbInstance = InstanceTxt.Value
        dbDatabase = DatabaseTxt.Value
        dbLogin = LoginTxt.Value
        dbPassword = PasswordTxt.Value
        dbQuery = QueryTxt.Value

    'Declare variables'
        Dim objMyConn As ADODB.Connection
        Dim objMyCmd As ADODB.Command
        Dim objMyRecordset As ADODB.Recordset
        Dim iCols As Integer
        Dim tbl As ListObject
        
        Set objMyConn = New ADODB.Connection
        Set objMyCmd = New ADODB.Command
        Set objMyRecordset = New ADODB.Recordset

    'Open Connection'
        Dim myConnectionString As String
        myConnectionString = "Provider=SQLOLEDB;Data Source=" + dbInstance + ";Initial Catalog=" + dbDatabase + ";User ID=" + dbLogin + ";Password=" + dbPassword + ";"
    
        objMyConn.ConnectionString = myConnectionString
        objMyConn.Open

    'Set and Excecute SQL Command'
        Set objMyCmd.ActiveConnection = objMyConn
        objMyCmd.CommandText = dbQuery
        objMyCmd.CommandType = adCmdText

    'Open Recordset'
        Set objMyRecordset.Source = objMyCmd
        objMyRecordset.Open

        For iCols = 0 To objMyRecordset.Fields.Count - 1
        Worksheets("Data").Cells(1, iCols + 1).Value = objMyRecordset.Fields(iCols).Name
    Next

    'Copy Data to Excel'
        Worksheets("Data").Range("A2").CopyFromRecordset objMyRecordset

    'Format Table'
        Sheets("Data").Select
        ActiveSheet.ListObjects.Add(xlSrcRange, ActiveSheet.UsedRange, , xlYes).Name = "Table1"
        ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleLight8"

    'Close Form
        Unload Me


End Sub

And there we have it, I have download from my OneDrive, that you can use to play with which is here: File Download

Advertisements