Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

VBA to Pass Parameters to SQL Stored Procedure

Hi, we are converting our sql queries (done thru Excel) into Stored Procedures. I have figured out how to call a stored procedure, but I do not know how to pass parameters. I have my parameters that were used in the original queries as comments at the top. Then the code is calling a stored procedure. Can someone help me with the parameter part?
'parameters used in queries:
'
'offnum = Range("OfficeValue")  
'  This is a number and in the SQL query would look like: '24' or '667' - it is an integer
'
'*******
'If frmInput.obPartner.Value = True Then
'    Pname = frmInput.ComboNames.Value    This is a name like 'Smith' - is varchar, 20
'    Bname = ""
'Else
'    Bname = frmInput.ComboNames.Value    This is a name like 'Smith' - is varchar, 20
'    Pname = ""
'End If
'*******

Sub CallStoredProcedure()

Dim oConn As New ADODB.Connection
Dim oRs As ADODB.Recordset
Dim cmd As New ADODB.Command
Dim stProcName As String
   

'Open a connection.
Set oConn = New ADODB.Connection
oConn.Open strConnect
Set oRs = New ADODB.Recordset

oConn.CommandTimeout = 1200


stProcName = "STORED PROCEDURE NAME HERE"
cmd.CommandType = adCmdStoredProc
cmd.ActiveConnection = oConn
cmd.CommandText = stProcName


Set oRs = New ADODB.Recordset
oRs.Open cmd.Execute

'where to put data
Sheet4.Range("A2").CopyFromRecordset oRs

     
' Close the connection.
oRs.Close
oConn.Close

End Sub


Post your answer or comment

comments powered by Disqus
Is it possible to invoke a SQL stored procedure from vba excel? If so, can you also pass in parameters? I've looked on the web and haven't found anything in regard to this topic. If you've got an example that would be great too!

Thanks,
-Marilyn

Is it possible to call a SQL stored procedure with parameters that come from
a worksheet cell using MS Query?

Hello to all. It's been a while since I posted, however the resources I have found here have been very useful indeed. My thanks to all - not only those who answer the questions, but to those who ask them as well. Without the questions, I couldn't see such specific answers!

To my issue.

I have been working with WinXP (as a VMware guest running on Vista Ultimate host), Excel 2003 and SQL 2005 Express to develop a process reporting system for an engineering environment.

Users will read/write data in the database using a series of forms (no surprises yet). I have used SQL Stored Procedures to manage the INSERT part of the forms, and this has been successful.

My problem so far has been using SQL Stored Procedures to retrieve data from the database. I have been searching the forum and other various resources on the Internet and have not yet come across a workable solution to data retrieval. Code examples I have seen either don't work for me, or I don't understand how to implement them correctly (more the latter, I would suggest). Some comments I have seen also question why use a Recordset to achieve what I am trying to achieve here, so I'm not certain that I am building something that will be operationally stable.

I have been able to code something that works (more by chance than any other method). Will the solution I have be robust in a production environment?

Is there a 'better' method to return data from SQL stored procedures than using the recordset method shown below?

The example below assumes the Stored Procedure requires myVariable, which comes from myControl on myForm.

Thanks in advance,

Matthew


	VB:
	
 myStoredProcedure() 
     'This sub-routine defines an ADOBD command to return variables to VBA from SQL Stored Procedures.
     'The ADODB command executes a Stored Procedure on the SQL Server (cmd.CommandText = [Stored Procedure Name])
     'Input requirements from the Stored procedure are declared as variants at the start of the sub-routine.
     
    Dim cnt As ADODB.Connection 
    Dim rst As ADODB.Recordset 
    Dim cmd As ADODB.Command 
    Dim stCon As String 'SQL Connection string
    Dim stProcName As String 'Stored Procedure name
     
     'Declare variables for Stored Procedure
    Dim myVariable As Variant 
    Dim myReturn As String 
     
     'Set ADODB requirements
    Set cnt = New ADODB.Connection 
    Set rst = New ADODB.Recordset 
    Set cmd = New ADODB.Command 
     
     'Define database connection string
    stCon = "Provider=SQLOLEDB.1;" 
    stCon = stCon + "Integrated Security=SSPI;" 
    stCon = stCon + "Persist Security Info=True;" 
    stCon = stCon + "Data Source=myServersqlexpress;" 
    stCon = stCon + "Use Procedure for Prepare=1;" 
    stCon = stCon + "Auto Translate=True;" 
    stCon = stCon + "Packet Size=4096;" 
    stCon = stCon + "Use Encryption for Data=False;" 
    stCon = stCon + "Tag with column collation when possible=False;" 
    stCon = stCon + "Initial Catalog=myCatalog" 
     
     'Open database connection
    cnt.ConnectionString = stCon 
    cnt.Open 
     
     ' Defines the stored procedure commands
    stProcName = "myStoredProcedure_Name" 'Define name of Stored Procedure to execute.
    cmd.CommandType = adCmdStoredProc 'Define the ADODB command
    cmd.ActiveConnection = cnt 'Set the command connection string
    cmd.CommandText = stProcName 'Define Stored Procedure to run
     
     
     'set parameters to be executed
    myVariable = myForm.myControl.value 
    Set prm = cmd.CreateParameter("@myVariable", adVarChar, adParamInput, 50, myVariable) 
     'Append parameters
    With cmd 
        .Parameters.Append prm 
    End With 
     
     'Execute stored procedure and return to a recordset
    rst.Open cmd.Execute 
     
    myReturn = rst.Fields("myVariable").value 
     
    Call Sub-Routine_That_Uses_The_Returned_Data 
     
     'Close database connection and clean up
    If CBool(rst.State And adStateOpen) = True Then rst.Close 
    Set rst = Nothing 
     
    If CBool(cnt.State And adStateOpen) = True Then cnt.Close 
    Set cnt = Nothing 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I am trying to right a simple front-end in VBA for Excel to return results
from SQL stored procedures after the user selects the input variable values
from a multi-select list box.

I did something similar out of Access last year; but all the ADODB. syntax
doesn't work in Excel VBA. (I've figured out the differences between Access
and Excel with the list box properties but not with the connection, query,
parameters... )

Provided below is my Access VBA code; how do I need to modify to run out of
Excel?

I am running Access 2002 and Excel 2002.

Private Sub lst_user_DblClick(Cancel As Integer)

'Get user-selected value for stored procedure parameter

For j = 0 To lst_user.ListCount - 1
If lst_user.Selected(j) = True Then
var1 = lst_user.ItemData(j)
End If
Next j

Dim cnn1 As ADODB.Connection
Dim runspcmd As ADODB.Command
Dim runempty As ADODB.Command
Dim runsp_param As ADODB.Parameter
Dim intvar As Integer
Dim strCnn As String

Set cnn1 = New ADODB.Connection
cnn1.ConnectionString = "Provider=SQLOLEDB;driver={SQL
Server};Server=(local);Initial Catalog=excelsqltest;
userid=myname;Password=;Trusted_Connection=Yes"
cnn1.Open strCnn

' Open command object with one parameter.
Set runspcmd = New ADODB.Command
runspcmd.CommandText = "sp_testproc"
runspcmd.CommandType = adCmdStoredProc

' Get parameter value and append parameter.
intvar = var1
Set runsp_param = runspcmd.CreateParameter("recno", _
adInteger, adParamInput)
runspcmd.Parameters.Append runsp_param
runsp_param.Value = intvar

' execute the command.
Set runspcmd.ActiveConnection = cnn1
runspcmd.Execute

cnn1.Close

DoCmd.Close acForm, Form.Name
DoCmd.OpenReport "outputtable", acViewPreview

end sub

I am trying to right a simple front-end in VBA for Excel to return results
from SQL stored procedures after the user selects the input variable values
from a multi-select list box.

I did something similar out of Access last year; but all the ADODB. syntax
doesn't work in Excel VBA. (I've figured out the differences between Access
and Excel with the list box properties but not with the connection, query,
parameters... )

Provided below is my Access VBA code; how do I need to modify to run out of
Excel?

I am running Access 2002 and Excel 2002.

Private Sub lst_user_DblClick(Cancel As Integer)

'Get user-selected value for stored procedure parameter

For j = 0 To lst_user.ListCount - 1
If lst_user.Selected(j) = True Then
var1 = lst_user.ItemData(j)
End If
Next j

Dim cnn1 As ADODB.Connection
Dim runspcmd As ADODB.Command
Dim runempty As ADODB.Command
Dim runsp_param As ADODB.Parameter
Dim intvar As Integer
Dim strCnn As String

Set cnn1 = New ADODB.Connection
cnn1.ConnectionString = "Provider=SQLOLEDB;driver={SQL
Server};Server=(local);Initial Catalog=excelsqltest;
userid=myname;Password=;Trusted_Connection=Yes"
cnn1.Open strCnn

' Open command object with one parameter.
Set runspcmd = New ADODB.Command
runspcmd.CommandText = "sp_testproc"
runspcmd.CommandType = adCmdStoredProc

' Get parameter value and append parameter.
intvar = var1
Set runsp_param = runspcmd.CreateParameter("recno", _
adInteger, adParamInput)
runspcmd.Parameters.Append runsp_param
runsp_param.Value = intvar

' execute the command.
Set runspcmd.ActiveConnection = cnn1
runspcmd.Execute

cnn1.Close

DoCmd.Close acForm, Form.Name
DoCmd.OpenReport "outputtable", acViewPreview

end sub

Please help I need to import SQL Stored Procedures into Excel, can this be
done, if so how?

Hi,

I am trying to work out how I get VBA to action more than one procedure from an event.

Here's a couple of examples of things I am trying to do...

When you click an option button on a user form, it opens the next user form and close that userform.

Private Sub LEAVERBUTTON_Click()
    CatFormL.Show
    Unload HomeForm
End Sub
In a text box on a userform when you click in the box it opens my frmCalendar macro and the the value I select is the put in that text box.

Private Sub TextBox1_Enter()
    frmCalendar.Show
    TextBox1.Text = frmCalendar.Value
End Sub
I guess I need some sort of loop? not sure how this is done though

Hi,

I have a shape in my worksheet and I have defined onAction property to it.

Now i want to pass parameter to the macro which is associated with the OnAction property,

string param = "40"
  Set p = ActiveSheet.Pictures.Insert(PictureFileName)
p.OnAction = ActiveWorkbook.Name & "!ImageRemoveClick"
The macro name is ImageRemoveClick and now i need to pass parameters- "param" to it

the macro definition will be
Function ImageRemoveClick(Param As String)
' code for macro
End function
Any help??

Hi,

i want to pass parameter to the function which is called from menu "OpenFile"

I am calling abc.xla!GetAllFiles...

where abc.xla is addin and GetAllFiles is a public function.

Now I need to pass parameters to the function GetAllFiles.

How do I do that?

Dim cMenu1 As CommandBarControl
Dim cbMainMenuBar As CommandBar
Dim iHelpMenu As Integer
Dim cbcCutomMenu As CommandBarControl

'(1)Delete any existing one. We must use On Error Resume next _ in case it does not exist.
On Error Resume Next
Application.CommandBars("Worksheet Menu Bar").Controls(MenuName).Delete
On Error GoTo 0
'(2)Set a CommandBar variable to Worksheet menu bar
 Set cbMainMenuBar = Application.CommandBars("Worksheet Menu Bar")
'(3)Return the Index number of the Help menu. We can then use  this to place a custom menu before.
 iHelpMenu = cbMainMenuBar.Controls("Help").index
 '(4)Add a Control to the "Worksheet Menu Bar" before Help.
 'Set a CommandBarControl variable to it
 Set cbcCutomMenu = cbMainMenuBar.Controls.Add(Type:=msoControlPopup, Before:=iHelpMenu, temporary:=True)
 '(5)Give the control a caption
 cbcCutomMenu.Caption = MenuName
 
'(6)Working with our new Control, add a sub control and  give it a Caption and tell it which macro to run (OnAction).
With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
        .Caption = OpenFolder
       .OnAction = "abc.xla!GetAllFolders"
       
     
End With
 '(6a)Add another sub control give it a Caption and tell it which macro to run (OnAction)
With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
                .Caption = OpenFile
                .Tag = "FromMenu"
                .OnAction = "abc.xla!GetAllFiles"
                
                              
End With
Thanks
Ranjani

Hello All

I want to pass an Excel range data to a SQL Server stored procedure. I
am trying to pass the data in a Excel range in form of some VB array
to a stored procedure.

Can you kindly provide a simple sample code in VBA and in T-SQL stored
proc how I can do this.

Thanks
Belinda

I need to get data from stored procedures in a sql database. I can't
see the sprocs in the list in "Select database source" window.

How can Iconnect to this information? If not with the user GUI, maybe
with vba?

Lets say the server name = Srv1, database name = DBTest1, user =
User1, pws = empty, sprocs = sptemp_report

Thankful to any help you can offer.
Kind regards
TSkogstrom

Hello
Can a SQL stored procedure be called (it takes five parameters) from Excel?
Can a "For Next loop" be used to supply those variable parameters? If you
use parameters do you call them the same way that you would in VB?
Thanks

Hi everyone,

I have been struggling for over a week now, trying to make my DLL call a
function written in VBA (version 6) when parameters are involved.

I am able to call my DLL functions from VBA without any problems. The DLL
can even call a VBA procedure (sub or function) without parameters
successfully (using the callback technique) but as soon as parameters are
passed with the call, an exception is raised in the VBA host application which
makes it crash.

I have done my homework by reading the available help files on the
subject and searching on the web without success.

Here the project I have built to demonstrate my problem:

My TestDLL.dll has been developed with Microsoft Visual C++ .NET

(1) Here is the content of DllTest.cpp:

#include "stdafx.h"

#define EXTERNC extern "C"
#define DLLAPI __declspec(dllexport)
#define WINAPI __stdcall

typedef long (CALLBACK *PCB1) (char cVal);

EXTERNC DLLAPI DWORD WINAPI DllCBInit (PCB1 pCallback);
EXTERNC DLLAPI void WINAPI DllCBTest1(char cVal);

static PCB1 theCallback = NULL;

// Defines the entry point for the DLL application.
BOOL APIENTRY DllMain(HANDLE hModule,
DWORD ul_reason_for_call,
LPVOID lpReserved)
{
return TRUE;
}

// Exported functions of the DLL
EXTERNC DLLAPI DWORD WINAPI DllCBInit(PCB1 pCallback)
{
theCallback = pCallback;
return (DWORD)theCallback;
}

EXTERNC DLLAPI void WINAPI DllCBTest1(char cVal)
{
(*theCallback)(cVal);
}

(2) Due to C++ name mangling, I have defines the function names in the
DLLTest.def file as follows:

LIBRARY DllTest

EXPORTS
DllCBInit
DllCBTest1

(3) I compiled the project DllTest with the Calling Convention __stdcall (/Gz)
as suggested everywere in the documentation. Then I copied the DllTest.dll
file
in c:windowssystem32

(4) In a VBA script (written under Excel 2003 for convenience purposes) I have
created a Main module with the following code:

' Force explicit variable definition
Option Explicit

Public theByte As Byte

Public Declare Function DllCBInit Lib "DllTest" (ByVal pCallback As
Long) As Long

Public Declare Sub DllCBTest1 Lib "DllTest" (ByVal cVal As Byte)

Public Sub Callback1(cVal As Byte)
On Error Resume Next 'to prevent error being propagated back to the caller
theByte = cVal
End Sub

Public Sub TestDLLCallback()
Dim lStatus As Long
'Initialize the callback
lStatus = DllCBInit(AddressOf Callback1)
Debug.Print lStatus
'Test the Dll callback functionality
Call DllCBTest1(128)
Debug.Print theByte
End Sub

(5) Here when I step through the TestDLLCallback() procedure, the first debug
print displayed 59111236 (0x0385f744) as the address of the VBA callback
procedure Callback1.

(6) Then if I step through the Call DllCBTest1 the cursor goes to the
Callback1() procedure as expected. The next step causes the application to
crash.

(7) I used the Visual Studio.NET IDE to attach to the EXCEL application and
set a breakpoint in the DLL at the DllCBTest1 function entry point. I stepped
through the assembly code to finally realize that the application crashes
when trying to access the content of an invalid memory location:

651FAF5E 8B 00 mov eax,dword ptr [eax] ---> where eax = 0xCCCCCC80

The content of eax seems to be the actual value of the passed parameter (128)
stored as one byte in the register eax and pushed onto stack. For some unknown
reason, this value comes back from the stack to haunt us. The assembly code
for DllCBTest1 is as follows:

EXTERNC DLLAPI void WINAPI DllCBTest1(char cVal)
{
05741FF0 55 push ebp
05741FF1 8B EC mov ebp,esp
05741FF3 81 EC C0 00 00 00 sub esp,0C0h
05741FF9 53 push ebx
05741FFA 56 push esi
05741FFB 57 push edi
05741FFC 8D BD 40 FF FF FF lea edi,[ebp-0C0h]
05742002 B9 30 00 00 00 mov ecx,30h
05742007 B8 CC CC CC CC mov eax,0CCCCCCCCh
0574200C F3 AB rep stos dword ptr [edi]
(*theCallback)(cVal);
0574200E 8B F4 mov esi,esp
05742010 8A 45 08 mov al,byte ptr [cVal]
05742013 50 push eax ---> 0xCCCCCC80
05742014 FF 15 40 6B 76 05 call dword ptr [theCallback (5766B40h)]
0574201A 3B F4 cmp esi,esp
0574201C E8 61 F5 FF FF call @ILT+1405(__RTC_CheckEsp) (5741582h)
}
05742021 5F pop edi
05742022 5E pop esi
05742023 5B pop ebx
05742024 81 C4 C0 00 00 00 add esp,0C0h
0574202A 3B EC cmp ebp,esp
0574202C E8 51 F5 FF FF call @ILT+1405(__RTC_CheckEsp) (5741582h)
05742031 8B E5 mov esp,ebp
05742033 5D pop ebp
05742034 C2 04 00 ret 4

We can see that the parameter cVal is saved as a byte in al (byte portion of
eax).

From what I have read on the subject, there seems to be a calling convention
clash. Since I only have control in my DllTest library, I even tried to
recompile it with either __cdecl and __fastcall. I obtain same results.

The only explanation must be something silly I just can seem to figure out.

I hope that someone would be able to see what my problem is and provide
me with the solution.

I have to appologize for the lenghty description but I assumed that
the more you have to work with the easier it will be to find a solution.

I am running under WinXP Pro 2002 with service pack 1.

Thanks in advance,
Michel

Hi Everyone

I have a stored procedure, that returns a value if passed two parameters.

The procedure is called spSkiCalculateDeprnForMonth, and needs me to pass a stock number (@pisStockNo, Varchar(20)) and a date (@pidDate DateTime).

It should then output a numeric value (@pocDeprn Money).

How do I write this in the 'query_text' part of the SQL.REQUEST function?

Many thanks

Gareth

All,

I have a stored Procedure"

	VB:
	
USE FabTime717 
GO 
 
 
/* Every Set of chart results Is identified by a unique TempTag Integer, which Is returned To you In the @TempTag variable.
*/ 
Declare @TempTag int 
/* The QueryString contains all information necessary For FabTime To generate the chart, including the chart name, factory
ID, And filters. */ 
Declare @QueryString nvarchar(4000) 
/* RetValue Is used To hold a return value from prcGetData that Is non-zero only If the procedure fails. */ 
Declare @RetValue int 
 
/* Setup @QueryString - In this example, a simple Call To the WIPLotList chart, without any filters. 
Specify a unique UserID so that you can identify this Call In tblLogRunTime. */ 
Set @QueryString = 'FactoryID=1&Chart=WIPLotList&UserID=-10'
 
/* Call prcGetData To generate the chart results. */ 
EXEC @RetValue = prcGetData @TempTag = @TempTag Output, @QuerySTring = @QueryString 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This procedure "prcGetData" returns an integer which will correspond to a block of data in a particular table. I need it to know what the ID of the block of data is.

I've tried to the following:


	VB:
	
 TestStoredProcedure() 
     
    Dim CServer As String 
    Dim CDatabase As String 
    Dim CLogon As String 
    Dim CPass As String 
    Dim StartDate As Date 
    Dim EndDate As Date 
    Dim TStartDate As String 
    Dim TEndDate As String 
     
    CServer = "" ' Your server name here
    CDatabase = "" ' Your  database name here
    CLogon = "" ' your logon here
    CPass = "" ' your  password here
    Call CnToSQL(CServer, CDatabase, CLogon, CPass) 
     
     
     
    Dim Cmd1 As New ADODB.Command 
    Dim rs As New ADODB.Recordset 
    Dim intTemp As Integer 
     
    Set Cmd1 = New ADODB.Command 
     
    Cmd1.ActiveConnection = cn 
    Cmd1.CommandText = "prcGetData" 
    Cmd1.CommandType = adCmdStoredProc 
     
    Cmd1.Parameters.Refresh 
    Cmd1.Parameters(0).Value = 10 
    Cmd1.Parameters(1).Value = 20 
    Cmd1.Parameters(2).Value = Worksheets("Sheet2").Range("D5") 
     
     
    Set rs = Cmd1.Execute() 
     
    rs.Open Cmd1 
    Worksheets("Procedure Export").Range("A1").CopyFromRecordset rs 
     
     
     
     
     
     
     
    Call DumpSP("prcGetData", "", "", Worksheets("Procedure Export").Range("A1")) 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I don't get any errors but the RS is empty. I know that I'm returning a value (int) but how do you set up the query.


	VB:
	
 
If @RetValue  0 BEGIN 
/* Error! @RetValue specifies LogID from tblLog record containing error. */ 
SELECT Time,Message FROM tblLog WHERE LogID = @RetValue 
End Else BEGIN 
/* Pull results. */ 
SELECT * FROM tblTempWIPLotList 
WHERE TempTag = @TempTag 
End 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Does anyone know if you can use a stored procedure name in the Array passed to the pivottablewizard? It works in 2000 but I think this is not working in 97. The error message is non-informative so i am guessing. Code attached. The bolded text is where the error message occurs. Thanks in advance.

Sub CreatePivotAssignments()

Dim PivotTableVar As PivotTable
For Each PivotTableVar In Worksheets("Productivity_Reports").PivotTables
PivotTableVar.TableRange2.Clear
Next

Dim QArray As Variant
Dim Range1 As Range
Dim ConString As String
Dim PivotName As String

Set Range1 = Worksheets("Productivity_Reports").Range("A6")
ConString = "Driver={SQL Server};" & _
"Server=some_server;" & _
"Database=some_database;" & _
"UID=;" & _
"PWD=;"

QArray = Array(ConString, "scSPGetProjectAssignments")
PivotName = "Pivot1"
Worksheets("Productivity_Reports").Activate
Worksheets("Productivity_Reports").PivotTableWizard xlExternal, QArray, Range1, PivotName
Application.ScreenUpdating = False
Set Pivot1 = Worksheets("Productivity_Reports").PivotTables(PivotName)
Worksheets("Productivity_Reports").Activate

With Pivot1

With .PivotFields("Hours")
.Orientation = xlDataField
.Caption = "Sum of Hours"
.Function = xlSum
End With

With .PivotFields("Month")
.Orientation = xlColumnField
.NumberFormat = "mmm-yy"
.Caption = "Month"
End With

.PivotFields("Employee").Orientation = xlRowField
.PivotFields("Project").Orientation = xlRowField
.PivotFields("Manager").Orientation = xlPageField

End With

Set Range1 = Nothing
Set Pivot1 = Nothing

ActiveSheet.PivotTables("Pivot1").Format xlTable10
Test
DisablePivotToolbar
Application.ScreenUpdating = True
End Sub

Hi,

This is a bit of a weird one but I have created a stored procedure that runs fine when executed manually from SQL. However when I run it from the Userform I have created in Excel it doesn't run properly, however the weird thing is it doesn't error either.

What the stored procedure does is take a loads of data and creates a table in a specific database, however once its been run in Excel I go to SQL and there is no table sitting there. Its like SQL isn't linked up to VBA correctly but with no error its almost impossible to know where its going wrong?

Any advice would be much appreciated

Cheers

Macro1

How do I source external data from a SQL Server stored Procedure with
parameters..

A stored procedure *without* parameters is not problem --> (get external
data - new db query - enter "exec sp_MySpName" into MS-Query)

However, with adding parameters, when the user selects "Refresh Data!" I
would like the user to be prompted to enter the values as per the stored
procedure parameters..

e.g. exec sp_MySpName @StartDate = [prompt user], @EndDate = [prompt user]

Is this possible?

Hi all, want to ask for a little help.. i am creating a program using asp classic,, and i want to passing parameter (stored procedure) between excel and the asp..
is it possible? so that the e10idbpdisabrowsing.useretch the query or parameter filter by user in the web.. and then run the query to the sql and return the information in pivot..... plzz helpp... thx

Rgds
jsy

When I run a stored procedure out of data>import...>new database query by
pasting in {call procname} in the sql code box, the results returned include
the field names from the select * procedure .

When I assign the results of the stored procedure to a recordset out of VBA
I encounter several issues:
The code I use to assign the results to the recordset is as follows:

Set runspcmd = New ADODB.Command
runspcmd.CommandText = procname
runspcmd.CommandType = adCmdStoredProc
' execute the command.
Set runspcmd.ActiveConnection = cnnct
Set recset = runspcmd.Execute

1) The field/column names from the procedure results are not included in
recset. Anyway I can get the names as well?

2) recordcount, and certain of the Move commands don;t work --(presumably
because of the implicit cursor type?).
Is there any way I can easily count how may records are in the recordset?

3) I know how to place the recordset on a worksheet using copyfromrecordset.
Can copyfromrecordset be used to assign the recordset to an array? (I am
trying to populate a multi-column list box with the data in recset) Any other
suggestions on how to easily do this?

Thanks for any help anyone can provide.

Hello

I have several stored procedures on my SQL Server which produce different data outputs that we use regularly.

Rather than run the stored procedures and then copy and paste the data into Excel, is it possible to simply run the stored procedure via Excel so that the data appears in Excel automatically?

Thanks for your help

Jon

I am trying to return data to excel (actually trying to create a pivot table)
from a record set created by a stored procedure in SQL database. When I
create the data source, it will not allow me to see a stored procedure, only
a view or table. Any way around this, WITHOUT using code (macro)?
--
Thx Jonathon

[Excel 2000]
Is there a combobox-style Inputbox hidden away in Excel anywhere? Or is it
possible to pass parameters to a form as it loads?

I have a macro, which in the middle of the code requires input from the
user.
Presents a form with a combobox on it.
The combobox is filled with a list - lets call it MyList.
Based on the value of a variable in the macro (call it MyValue), a
specific entry in the list is already selected.
The user then either accepts the offered value or selects a different
value from the list.
The macro then stores the result in a variable (MyResult) and continues
the code.

I don't want to use a standard inputbox because many of the entries are
long strings, and it will be irritating to type them in.
But using a form is complicated by the need to pass the default list
entry to the form.

I hope I've explained my problem clearly - can anyone suggest a solution?

Darren

how can i code or call a stored procedure with parameters in vba?
are the codes (with parameter and without parameters) different?
and do i need to create first the stored procedure before i can call it in vba?

thanks


No luck finding an answer? You could always try Google.