Tips on selecting SQL data into a worksheet?

[Jim Bancroft]

I have a SQL Server stored procedure I need to run from within an excel
spreadsheet, if that's possible.

The stored procedure takes about three minutes to run, and the end result is
10 columns worth of data. Ideally, I'd like for those 10 columns to be my
spreadsheet's 10 columns.

Are there code snippets out there I can look at to learn:

1. how to connect to a SQL Server database via excel (hopefully using a
DSN-less connection-- I have a connection string), and
2. how to call a SQL Stored procedure from Excel, and snag the resulting
data.

TIA,

-Jim

 

[Jake Marx]

Hi Jim,

I have a SQL Server stored procedure I need to run from within an excel
spreadsheet, if that's possible.

The stored procedure takes about three minutes to run, and the end result is
10 columns worth of data. Ideally, I'd like for those 10 columns to be my
spreadsheet's 10 columns.

Are there code snippets out there I can look at to learn:

1. how to connect to a SQL Server database via excel (hopefully using a
DSN-less connection-- I have a connection string), and
2. how to call a SQL Stored procedure from Excel, and snag the resulting
data.

I've pasted some code below that shows you how to execute a SP from Excel.
It's not that hard, it just involves a few steps. If you don't have any
parameters in your SP, you can take out the 2 lines related to that. And
you'll want to add error handling that cleans up when you hit an error.
Most errors will occur when connecting (if the connection is down or the
string is incorrect) or when executing the SP via the Command object (this
will fail if anything you've done up to that point is incorrect or missing).

BTW, 3 minutes for a SP to execute seems like a long time - do you have a
_ton_ of data, or is the query very complex? Just wondering if you can't
optimize the query a bit.

Regards,

Jake Marx
MS MVP - Excel



---------sample code----------
Sub PutRecordsetInRange()
'/ you must set a reference to the Microsoft ActiveX Data Objects 2.x
Library
'/ (where x is the highest # you or your users will have)
Dim cn As ADODB.Connection
Dim cd As ADODB.Command
Dim rsData As ADODB.Recordset

Set cn = New ADODB.Connection
With cn
.CursorLocation = adUseClient
.ConnectionString = "<your cxn string>"
.Mode = adModeRead
.Open
End With

Set cd = New ADODB.Command
With cd
Set .ActiveConnection = cn
.CommandType = adCmdStoredProc
.CommandText = "dbo.<your sp name>_sp"
.CommandTimeout = 180 '/ set this higher if you need to

.Parameters.Append .CreateParameter("@starttime", _
adDBTimeStamp, adParamInput, , CLng(Now()) - 2)
.Parameters.Append .CreateParameter("@endtime", adDBTimeStamp, _
adParamInput, , Now())

Set rsData = .Execute
End With

With rsData
If .State = adStateOpen Then
If Not (.BOF And .EOF) Then
'/ got records - put them in range
Range("A1").CopyFromRecordset rsData
End If
.Close
Else
'/ error
End If
End With

Set rsData = Nothing
Set cd = Nothing
cn.Close
Set cn = Nothing
End Sub

 

[Jake Marx]

Hi Jim,

Hey, thanks Jake. I'll make good use of this.

No problem - hope it helps a bit!

To answer your question: yes, the stored procedure does take three minutes.
It drops and creates tables and runs a series of select statements, slapping
data into them. You're quite right that the queries involved could be
optimized a bit, but we're on a deadline and I was just handed this SQL
file....you know how it goes sometimes. Optimizations perpetually just over
the horizon, "we'll get to that tomorrow," etc.

Hey, isn't that always the case? I've got some apps that I've been meaning
to update for years.

What I'll do is run a couple of ADO commands from excel. One to generate
the tables I need, then another to create and retrieve my recordset. Seems
easiest if I segment things this way.

I typically put everything into one SP if possible. Even if that SP is
simply a wrapper for other SPs segmented by task. The less calls you make
to SQL, the better.

Ah, one more question, if I could. Is your code below designed to be run
from a VB program? I'm a little embarrassed to ask, but can you create and
run VB-esque subroutines and functions from within Excel?

Yes, you can do most VB-type things in Excel. Excel comes with VBA (Visual
Basic for Applications), which you can get to via Alt+F11 in Excel. Just
add a standard module and add/edit the code I provided. You can also create
UserForms (a different beast than VB forms). Once you're in the VBE (Visual
Basic Environment), make sure you add a reference to ADO via Tools |
References or the code won't compile.

To run your code, you can either select Tools | Macros | > Macros or you
could create a menu item or button that calls your subroutine.

Regards,

Jake Marx
MS MVP - Excel

 

[Bob Phillips]

Jim,

It's all VB apart from the one line
Range("A1").CopyFromRecordset rsData
which uses part of the Excel Object Model (Range), and the CopyRecordset
method. Those apart, you could copy and paste it directly into a VB program.
You could even put it into a DLL and call that from Excel (over the top here
perhaps, but possible).