How to Using Excel to Read and Write an External Database

Excel can do virtually anything; whether it should be made to do everything is another matter. While the spreadsheet is very powerful in manipulating data, it isn’t too great at storing normalised data. Harnessing Excel to a relational database like SQL Server enhances the application’s power.

To start with, you will need MS Access or the more stable and free – SQL Server Express. It is assumed that the reader has the Excel Developer ribbon displayed, and is familiar with the VBA Editor and Structured Query Language (SQL). This article uses SQL Server connection strings. For MS-Access, refer to Google.

While Excel has its own built-in routines for getting information from SQL Server into (say) a pivot table, our example will give more flexibility in data selection.

Connection String

I will be using a private database; insert your own driver information in place of mine in the ConnectDatabase sub routine. We then use connDB as a communications channel to our database – in my case to return results from a stored procedure. You might use more standard SQL statements  like “Select * from …”

Order of Business

First, we will load combo-box choices from SQL Server when the workbook opens, using an Auto_open macro, and dumping it in sheet “ComboData”. Whether the Server is in the cloud or local, there will be no noticeable delay in starting Excel – as long as the database is accessible from the workstation.

Next, we will extract filtered data from the database and drop it into Excel, columns F to K.

The Interface

Mine has drop-down boxes to filter information from the database. The Role combo box triggers a search to populate the table at right.

Rename “Sheet1” as “Main”.  Add at least one combobox.

The Code

Public connDB As New ADODB.Connection
Public rstNew As New ADODB.Recordset
Public rs As New ADODB.Recordset
Public strSQL As String
Public nID As Integer

Sub auto_Open()
    Call PopulateComboData     'kicks off the first process  on Open
End Sub

Sub PopulateComboData()
    Sheets("ComboData").Range("A3:C100").ClearContents
    Call ConnectDatabase      'use the ConnectDatabase routine
    strSQL = "Select DeptID, Department, Phase from tblDept Order by Department"
    Set rs = connDB.Execute(strSQL)
    ActiveSheet.Range("A3").CopyFromRecordset rs      'copies the recordset in bulk
End Sub

Sub ReadData()
    intRole = Sheets("main").Range("D7")
    Sheets("Main").Range("F4:L100").ClearContents
    Call ConnectDatabase
    strSQL = "EXEC DBTest " & intRole    'calls a stored proc with parameter
    Set rs = connDB.Execute(strSQL)
    ActiveSheet.Range("F4").CopyFromRecordset rs      'copies the recordset in bulk
End Sub

Sub ConnectDatabase()
    On Error GoTo ErrConnect
    If connDB.State = 1 Then connDB.Close     'closes connection if already open
    strServer = "197.200.28.164" 
    strDBase = "Qcrew_sql"
    strUser = "joesoap_sql"
    strPWD = "frU6ra!@"
    If strPWD > "" Then 
        strConnectionstring = "DRIVER={SQL Server};Server=" & strServer & _
        ";Database=" & strDBase & ";Uid=" & strUser & ";Pwd=" & strPWD & _
        ";Connection Timeout=30;"
    Else        'Use windows authentication
        strConnectionstring = "DRIVER={SQL Server};SERVER=" & strServer & _
        ";Trusted_Connection=yes;DATABASE=" & strDBase
    End If
    connDB.Open strConnectionstring
Exit Sub
ErrConnect:
    MsgBox Err.Description
End Sub

Format the combo box control to read sheets “ComboData”. Then right-click the combo box to assign the ReadData sub procedure to it.  When an item is selected in the combobox, write its key to sheet “Main”, cell D7. The VBA code will use this key as a filter (see intRole, above).

References to dll library

Use Tools>References in the code window to reference the Microsoft Active X Data Objects library. This will enable Excel to use the ADODB objects declared in the code.

The ReadData sub routine above uses a relational data structure, shown at below, which is difficult to achieve in Excel alone.

Further data changes could trigger a write-back to the database, with the appropriate SQL Update statement followed by connDB.execute(strSQL).

Finally, protect your code from being viewed or changed:  Tools>Properties>Protection.

Handle Excel problems:

From time to time, particularly when it holds complex programs, Excel might crash and fail to re-cover the properly. In the event of a damaged xlsx file, having an effective recovery tool handy will solve most problems.

Author Introduction:

Felix Hooker is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair rar error and sql recovery software products. For more information visit www.datanumen.com