• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2613
  • Last Modified:

Execute SQL Procedures using Excel VBA

Hello,

I have no problem constructing sql queries using VBA in Excel. But for some odd reason I can´t seem to find the solution for executing a SQL Procedure using Excel VBA.

Thank You in advance
0
Claudia_V
Asked:
Claudia_V
1 Solution
 
RiverGuyCommented:
Claudia, it depends on how you want to do it.  The simplest way is probaby by passing a string for an SQL statement, such as:

"EXEC your_stored_procedure @Paramter1 = 1"

This would execute a stored procedure with 1 parameter.  You would just pass it to your connection, such as:

yourConnection.Execute(your_sql_String)


You could also make an ADODB Command object:

Dim cmd1 as New ADODB.Command
cmd1.Type = adCmdStoredProc
cmd1.ActiveConnection = your_Connection
cmd1.CommandText = your_stored_procedures_name
cmd1.Parameters("your_parameter_name") = 1
cmd1.execute


0
 
Claudia_VAuthor Commented:
Hello,

Thank you for replying although my problem persists because it looks like the method for defining connections using the Add(Connection:=...) function (which is the one I use for select Queries in VBA) is a little diferente with the Execute function. What am I doing wrong??? I´ve tried using OpenDatabase and OpenConnection with no luck.

Thanks for the help
0
 
RiverGuyCommented:
Are you using ADO?  Here is a total sample that one could work with:


'Create ADO Connection
Dim Con1 as New ADODB.Connection
Con1.Connectionstring = "Connection_String"
Con1.Open

'Create a Recordset
Dim Rst1 as new ADODB.Recordset

'Create a Command for your Stored Proc
Dim cmd1 as New ADODB.Command
cmd1.Type = adCmdStoredProc
cmd1.ActiveConnection = your_Connection
cmd1.CommandText = your_stored_procedures_name
cmd1.Parameters("your_parameter_name") = 1

'Display your Results
Set Rst1 = cmd1.Execute
Rst1.MoveFirst
Range("A1").Value = Rst1![Field1]
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Claudia_VAuthor Commented:
Thanks once again.I didn´t try creating an ADO Connection because there I seem to missing an Important Library, because he doesn´t recognise commands such as ".Type". I´ve tried going too the references and adding a few libraries, but I must not be choosing the correct one. Do you know wich one it is??
0
 
Claudia_VAuthor Commented:
Just one more peace of information. I already have 2 libraries which I think are important: "Microsoft DAO 3.6 Object Library" and "Microsoft ActiveX Data Object 2.6 Library". Am I missing something???
0
 
RiverGuyCommented:
Your using DAO, thats why.  The 2.6 ActiveX Data Object (ADO) would be the one you want.  You might have to uncheck the DAO.  DAO is more or less for desktop databases, and you're using SQL Server.  

If I was you, I would make a copy of your excel workbook, so you still have your old one that you had some things working with the DAO.  On the new copy, unreference the DAO, and make sure you have reference the newest version of the ActiveX Data Object Library.  You might even have 2.7.  If thats the case, uncheck the 2.6 and check the 2.7.  Then try the code for the stored procedures.
0
 
Claudia_VAuthor Commented:
Thanks once again. I´m sorry to tell you that your sugestion still didn´t resolve my problem (Weird??). In the meanwhile I´ve fortunatly been able to solve my problem without using ADO Commands.

Here goes my solution:
 
With ActiveSheet.QueryTables.Add(Connection:="ODBC;DSN=XXX;DATABASE=XXX;Trusted_Connection=Yes", Destination:=Range("a1"))
        .CommandText = "EXECUTE proc" & " " & "SELECT * FROM tableXPTO"
        .Refresh BackgroundQuery:=False
  End With

Probably it´s basic stuff for you, but my error was not realizing that by using this method I had to have output data for my destination cells(and this method doesn´t work without the destination command!). In my .Commandtext I would just Execute the procedure and not have a command for output. This method, although Im sure is not the best, sutes me just fine because I need to import to excel the table that results from my procedure run in SQL.

Thanks anyway for the help. In the meanwhile if you have any other sugestions (because I wouldn´t mind start bulding ADO Commands) I would be gratefull.

0
 
CleanupPingCommented:
Claudia_V:
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
0
 
monosodiumgCommented:
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

I will leave the following recommendation for this question in the Cleanup topic area:
    PAQ with points refunded

Any objections should be posted here in the next 4 days. After that time, the question will be closed.

monosodiumg
EE Cleanup Volunteer
0
 
Computer101Commented:
PAQed, with points refunded (20)

Computer101
E-E Admin
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now