Calling a stored procedure with excel vba

1

I've been working with SQL for a little while and I am starting to explore stored procedures and how to run them from excel vba. I hope this is the forum for this question. I have this script to run a stored procedure called "sp_SelectTForecast" (SP) that retrieves data from a table called tForecast on my local database called "ITMDB"

I think that I have connected to the correct database, and at first the error said could not find the SP, but now the script runs all the way through but the data does not show up on the excel sheet. what am I doing wrong?

Sub GettForecast()
Dim conn As ADODB.Connection
Dim cmd As ADODB.Command

Set conn = New ADODB.Connection
conn.ConnectionString = "Provider=SQLOLEDB;Data Source=" & "localhost\sqlexpress" & ";Initial Catalog=" & "ITMDB" & ";User ID=" & "#####" & ";Password=" & "########" & ";Trusted_Connection=yes"
conn.Open

Set cmd = New ADODB.Command
cmd.ActiveConnection = conn
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "sp_SelectTForecast"

cmd.Execute
conn.Close

Set conn = Nothing
Set cmd = Nothing
End Sub

3

I'm not sure what you mean by defining ADODB.Connection

Try do add the schema name in the name of the stored procedure.

cmd.CommandText = "dbo.sp_SelectTForecast"

dbo , or what schema it's that sp

1 Like
4

Check to see if ADODB reference is enabled:

On the VBA Editor, click on menu "Tools" -> "References..."

Here you should verify the "Microsoft ActiveX Data Objects x.x Library" if it's enable or not. It should be enabled

https://msdn.microsoft.com/en-us/library/office/jj249338.aspx

1 Like
5

I had my ActiveX turned on but what I did not do is create a variable to hold the data which I could then copy the data from that into the excel sheet. Got the answer from a colleague.

Sub GettForecast()
Dim conn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.RecordSet

Set conn = New ADODB.Connection
conn.ConnectionString = "DRIVER=SQL Server;SERVER=localhost\sqlexpress;UID=username;Trusted_Connection=Yes;APP=Microsoft Office 2013;WSID=local database ID;DATABASE=ITMDB"
conn.Open

Set cmd = New ADODB.Command
'cmd.ActiveConnection = conn
'cmd.CommandType = adCmdStoredProc
'cmd.CommandText = "dbo.sp_SelectTForecast"

'save data to variable
Set rs = conn.Execute("dbo.sp_SelectTForecast")

ThisWorkbook.Worksheets("Sheet1").Range("A2:A7201").CopyFromRecordset rs

'cmd.Execute
rs.Close
conn.Close

Set conn = Nothing
Set rs = Nothing
End Sub

6

Now the next step is to create a stored procedure that does the same thing as the below query, which deletes rows from a table and inserts the updated data. Right now I just open SQL Management Studio and run this query, I would like to do it all in excel where I collect the data to insert into the table in the database.

//////////////////////////Query//////////////////////////////////////////////
delete
from tForecast
where Vintage='2Q18'
go

INSERT INTO dbo.tForecast
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Text;Database=C:\Users\nreilly\Documents\GlobalDesignTeam\QCCharts', 'select * from ITMDBLoad.csv')
///////////////////////////////////////////////////////////////////////////////

Should I start another post for this or can I continue post?

7

Not sure I understand your question. Yes , you could open a new post.

Anyhow, you can do something like this:

...
cmd.CommandType = adCmdText
cmd.CommandText = "Insert here your query text that runs from SSMS"
...

8

Thanks everyone and sorry I neglected this one, but it was a temporary remedy and now the IT team has automated the entire data collection and retrieval.