Using Excel to Pull Data from SQL Server

Using Excel to Pull Data from SQL Server
Using Excel to Pull Data from SQL Server
Michael Schmalz

Before I start, I want to emphasize that this example uses Excel for the functionality as opposed to Enterprise Manager or any of the SQL Server User Interface functionality. In the first article that I wrote about getting data from SQL Server to Excel, you were shown how to push data into Excel using DTS. This is useful in many cases when you are dealing with large datasets. But, if you want access to specific pieces of data in real time, then DTS is not the method to use. In cases like this, two very viable options are to use Microsoft Access or Microsoft Excel. You could use Microsoft Word if you needed to mail letters to customers meeting some criteria, but similar concepts that you would use in Excel apply to Word as well. Your decision about whether to use Access or Excel will depend mostly on what you want to do with the data after you get it. For example, if you just need to page through data and don’t need calculations and don’t need to do anything else with the data, then Access is a good choice. If you need the data in a report format with or without calculations and don’t need to do anything else with the data, Access Reports or Excel could work. In that case just pick the one you are most comfortable using. But, if you want access to the data and need to do calculations and also need to be able to reference the data for other purposes, then Excel can be an excellent choice. It is also worth noting that many companies do not put Microsoft Access on everyone’s desktop. So, in those cases Excel is going to be the go to application.

Pulling Data with a Query

There is a query tool that comes with Excel that can be used to get data from a variety of sources. However, it is not easy to modify your query on the fly and you don’t have a whole lot of control over where the data goes. It simply takes a result set and places it in your worksheet where you tell it. If you want to change the number of parameters and their values on the fly and do different things with each record, then this is not the tool for you.

One of the other options you have is to use Visual Basic for Applications (VBA) and ActiveX Data Objects (ADO) to manage the data. That is the topic of this article. After you are done reading this article, you will understand how to: build a connection string to SQL Server, connect to SQL Server, execute a query, and navigate through the resulting recordset.

Step 1 – Get Connection Information on Your Server

Your first order of business is to determine how you want to connect to your server. If you are currently using your server with MS Access, then it is likely that you already have a DSN set up with your server information in it. You can easily use that DSN through VBA. If you don’t have a DSN set up, then you will need to get the connection information to your server. The information that you need is the server name or IP address, the database name, your username, and your password. I wrote this article using this information instead of a DSN. If you are using a DSN or use Windows Authentication instead of a password, then your connection string will change slightly. There are many examples about how to change your connection string for these different methods in the ADO documentation.

Step 2 – Get Excel Ready

The next thing that you must do is start-up Excel and get into a new workbook. Then, hit Atl-F11 to bring up the Visual Basic Editor. In the default view, you will see the project explorer on the left hand side of your screen with a treeview list of items. If it is not already expanded, you should click the plus sign on the listing that says “VBAProject (filename.xls)” and then right-click on the listing that says “Microsoft Excel Objects”. You will see a pop-up list with several options. You need to select Insert – Module. This will get you a blank module. As a default, ActiveX Data Objects is not available for use from VBA. In order to make ADO available for use, you need to go to the menu bar and select Tools – References. From the box that comes up, you need to scroll down the list and check the box for “Microsoft ActiveX Data Objects 2.x Library”. You should generally select the highest version number. The only caveat to this is when it will be used by multiple users. In cases like this, you should select the version that is on everyone’s computer – or better yet install the latest version on everyone’s computer. Now, you are ready to write your code.

Step 3 – Write Your Code

Your next decision is whether you want to set up a generic procedure to connect to SQL Server or if you want to simply have it as part of your module. For the purposes of this article, the connection to SQL Server will be part of the module, although the information for the connection will come from the Excel Workbook.

On the Project Explorer, you will see a treeview for Modules and a module named “Module1”. Double-click on “Module1” to bring up the blank module. Next, go to the menu bar and click on Insert – Procedure. You will want the radio button for Sub to be selected and the radio button for Public to be selected. For the name of the procedure, let’s call it GetMyData.

With the procedure ready, it is time to go back to the Excel Workbook. In Sheet1, set up your worksheet like the figure below.

You could also create a field for password, but for the purposes of this article I have the password coming from an inputbox. I would not suggest storing a password in your worksheet, but it can be done. The ADO objects that you will need are Connection, Recordset, and Field. Also, when you type in your Fields and Criteria, you must use the ' character to start your entry so that Excel does not think you are entering a formula. For the rest of the article, refer to the code below:

Public Sub GetMyData()
Dim adoconn As ADODB.Connection
Dim adors As ADODB.Recordset
Dim adofld As ADODB.Field

Dim server As String
Dim dbname As String
Dim usernm As String
Dim tblname As String
Dim pword As String
Dim sqlstr As String
Dim whrclse As String
Dim x As Integer
Dim y As Integer

Dim xlws As Excel.Worksheet
Dim xlrng As Excel.Range
Set xlws = Sheets("Sheet1")

pword = InputBox("Please enter your password.", "Password Prompt")

server = xlws.Range("B3").Value
dbname = xlws.Range("B4").Value
usernm = xlws.Range("B5").Value
tblname = xlws.Range("B6").Value

Set adoconn = New ADODB.Connection
Set adors = New ADODB.Recordset

adoconn.ConnectionString = "Provider=sqloledb;Datasource=" & server & _
";Database=" & dbname & ";uid=" & usernm & _
";pwd=" & pword

adoconn.Open

sqlstr = "Select " & tblname & ".* from " & tblname

x = 12
y = 0

While xlws.Cells(x, 1).Value <> ""
y = y + 1
whrclse = whrclse & " AND " & xlws.Cells(x, 1).Value & _
" " & xlws.Cells(x, 2).Value
x = x + 1
Wend

If y <> 0 Then
whrclse = " WHERE 1 = 1 " & whrclse
sqlstr = sqlstr & whrclse
End If

adors.Open sqlstr, adoconn, adOpenStatic
Debug.Print sqlstr
Set xlws = Sheets("Sheet2")
xlws.Activate

x = 1

For Each fld In adors.Fields
xlws.Cells(1, x).Value = fld.Name
x = x + 1
Next fld

Set xlrng = xlws.Range("A2")
xlrng.CopyFromRecordset adors
xlws.Columns.AutoFit


adors.Close
adoconn.Close
Set fld = Nothing
Set adors = Nothing
Set adoconn = Nothing
Set xlrng = Nothing
Set xlws = Nothing

End Sub

In this code, there are a lot of things going on. The two concepts that I want to cover are the ones that are not necessarily covered in the documentation that comes with ADO. The first is the building of the where clause. What I am trying to accomplish with the Excel Worksheet is the ability for a user to enter as many fields and criteria as they wish. The code is going to start at the first row where fields and criteria are entered and then continue until a blank row is encountered. In order to not have to check for the first row to determine if an “AND” is needed, what I have done is created a first part of the where clause that will always be true “1 = 1”. You could use other expressions that evaluate to true or capture the first row separately and then place “AND”s in subsequent rows. But, this way is very easy. The second item that I would like to call your attention to is the line “For Each fld in adors.fields”. What I am doing there is getting the field name for each field in the recordset so that I can put column headers on.

You may also notice the Excel objects that are being used – the Worksheet Object and the Range Object. As you type this code, you will notice that there are many other properties and methods that you can access. In this case, we are using the CopyFromRecordset method of the Range Object. This method takes the entire recordset and starts the copy and paste in the range specified. The second method being used is the AutoFit method. This is setting up the Worksheet to be more readable.

Final Step

The final step is to place a command button on the Worksheet. You do this by opening up the Visual Basic Toolbar from the View > Toolbars menu. Then, you open up the Control Toolbox from the toolbar. You will see a command button on this toolbox and you simple click on it and then click and drag on your worksheet to draw the command button that you want. When you have done this, you can right-click on it to change properties (from here you can change color, caption, etc.). Then, right-click on it again and go to view code. Your one line of code for the OnClick event is:

Private Sub CommandButton1_Click()
GetMyData
End Sub

You can then fill out the sheet with your information and click the button. The code will run and place the recordset on Sheet2. You could obviously set this up with other sheet names, etc. Some suggested uses for the resulting worksheet can be a report on another sheet that refers to Sheet2, setting criteria for transactions that need reviewed, or any other time that you need to get a recordset into Excel for analysis.

Conclusion

While as an administrator it is unlikely that you will need this type of setup for your work, a method like this makes it possible for you to give recordset viewing capabilities to people on their desktop as long as Excel and MDAC are installed. My guess is that you would not use this code verbatim, but might be able to tweak the code and put it to good use. A final note that you can do is place all of the server information and user names and passwords into the Excel code and then protect the code with a password. You can set up a generic read only login that you only give access to specific views on the server. This would enable several people to use the workbook and they would never know the name of the server, the login, etc. But, how you set it up is up to you.