Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tips on selecting SQL data into a worksheet?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tips on selecting SQL data into a worksheet?
Hi Jim,
"Jim Bancroft" <bobbgambles_at_nospam.msn.com wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tips on selecting SQL data into a worksheet?
Hi Jim,
"Jim Bancroft" <bobbgambles_at_nospam.msn.com wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Tips on selecting SQL data into a worksheet?
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). -- HTH Bob Phillips "Jim Bancroft" <bobbgambles_at_nospam.msn.com wrote in message ... Hey, thanks Jake. I'll make good use of this. 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. 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. 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? Thanks again (and thanks, Bob, for offering your help too) -Jim 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Locating and selecting Data from Worksheet | Excel Discussion (Misc queries) | |||
Selecting a row in a worksheet | Excel Worksheet Functions | |||
Selecting a specific worksheet | New Users to Excel | |||
selecting a worksheet | Excel Worksheet Functions | |||
Selecting a Worksheet Range | Excel Worksheet Functions |