Execute a SQL SP from an Excel Macro

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Execute a SQL SP from an Excel Macro

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-01-08 : 08:51:56

Robert writes "I have a dilema.. I want to update Cells in an Excel Spreadsheet from SQL on a recurring basis (as the values change in my SQL database). The problem is I don't like using ODBC or any connection type that requires something to be set up on the PC.

I am looking for a way to programatically set up a link between SQL and Excel.

So far I have used iSQL in a Batch File. In Excel I run Shell('MyBatchFile'). This calls a SQL sproc which BCP's the data out to a text file that Excel can import.

Problem is I want this to occur on an ongoing basis (say every 10 minutes).

Since the Excel WAIT and Kernel32 SLEEP functions, when used in Excel, cause the PC to hang, I wanted to call a SQL Stored Procedure in which I would used the SQL WAITFOR DELAY. This does not hang the PC (if run from iSQL) but I can't get Excel help on setting up a SQL Procedure call programatically from an Excel Macro.

Any ideas?"

nr
SQLTeam MVY

12543 Posts

Posted - 2003-01-08 : 09:34:43

excel macros run vbscript?

In dts you can do it like this so may work in excel too

dim objCN
dim objCMD
dim objRS
strConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;"
strConnectionString = strConnectionString & "Initial Catalog=" & DTSGlobalVariables("DatabaseName").Value
strConnectionString = strConnectionString & ";Packet Size=4096;
strConnectionString = strConnectionString & "DataSource=" & DTSGlobalVariables("ServerName").Value
set objCN = CreateObject("ADODB.Connection")
set objCMD = CreateObject("ADODB.Command")
objCN.open strConnectionString
objCMD.ActiveConnection = objCN
objCMD.CommandText = "spMySp"
objCMD.CommandType = 4 'adCmdStoredProc
objCMD.Parameters.Append objCMD.CreateParameter("OutParmInt",3,2)
objCMD.Parameters.Append objCMD.CreateParameter("InParmStr1",200,1,10,DTSGlobalVariables("StrData1"))
objCMD.Parameters.Append objCMD.CreateParameter("InParmStr2",200,1,255,DTSGlobalVariables("StrData2"))
Set objRS = objCMD.Execute
set objRS = nothing

DTSGlobalVariables("SPOutParm").Value = objCMD.Parameters("OutParmInt")
set objCMD = nothing
set objCN = nothing

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources