executing T-sql stmts in Excel VBA

[venkiatmarut]

Hi,

:banghead:
Can we execute a group of SQL statements in Excel without creating a Stored procedure on database.

I have a group of sql statments



IF EXISTS(SELECT name FROM tempdb.dbo.sysobjects WHERE name = 'rpt') DROP TABLE tempdb.guest.rpt
GO

CREATE TABLE tempdb.guest.rpt
( port_num INT NULL,
port_name VARCHAR(25) NULL,
pos_num INT NULL,
risk_mkt VARCHAR(25) NULL,
cmdty_full_name VARCHAR(40) NULL,
balance_period CHAR(8) NULL,
open_close_ind CHAR(1) NULL,
inv_cur_actual_qty FLOAT NULL,
inv_cost_uom_code CHAR(4) NULL,
pl_amt FLOAT NULL
)

IF EXISTS(SELECT name FROM tempdb.dbo.sysobjects WHERE name = 'tmp') DROP TABLE tempdb.guest.tmp
GO

CREATE TABLE tempdb.guest.tmp
( pos_num INT NOT NULL,
pl_amt FLOAT NULL
)


DECLARE @trading_period VARCHAR(20)
DECLARE @pl_asof_date DATETIME

SELECT @trading_period = '201003'
SELECT @pl_asof_date = '20100319'


INSERT INTO tempdb.guest.rpt
(port_num,
port_name,
pos_num,
risk_mkt,
cmdty_full_name,
balance_period,
open_close_ind,
inv_cur_actual_qty,
inv_cost_uom_code
)
SELECT
DISTINCT
INV.port_num,
PRT.port_full_name,
INV.pos_num,
TIM.risk_mkt_code ,
CMD.cmdty_full_name,
INV.balance_period,
INV.open_close_ind,
SUM(INV.inv_curr_actual_qty),
INV.inv_cost_uom_code
FROM tbl1 AS INV
LEFT JOIN tbl2 AS PRT ON INV.port_num=PRT.port_num
LEFT JOIN tbl3 AS CMD ON INV.cmdty_code=CMD.cmdty_code
LEFT JOIN tbl4 AS TIM ON INV.trade_num=TIM.trade_num
AND INV.order_num = TIM.order_num
AND INV.sale_item_num = TIM.item_num
WHERE
1 = 1
AND balance_period = @trading_period
AND INV.port_num IN (SELECT DISTINCT port_num FROM portfolio_group
WHERE parent_port_num IN (00004,00002,54645,4556,
))
AND INV.trade_num <> 56
GROUP BY
INV.pos_num,
--INV.trade_num,
--inv_num,
INV.port_num,
PRT.port_full_name,
--INV.del_loc_code,
CMD.cmdty_full_name,
balance_period,
open_close_ind,
--inv_curr_actual_qty,
--inv_avg_cost,
INV.inv_cost_uom_code,
TIM.risk_mkt_code

INSERT INTO tempdb.guest.tmp
SELECT TMP.pos_num, SUM(PLH.pl_amt)
FROM
tempdb.guest.rpt TMP,
pl_history PLH
WHERE 1=1
AND PLH.pos_num = TMP.pos_num
AND PLH.pl_asof_date = @pl_asof_date
AND PLH.pl_owner_sub_code IS NULL
GROUP BY TMP.pos_num

UPDATE tempdb.guest.rpt
SET tempdb.guest.rpt.pl_amt = tempdb.guest.tmp.pl_amt
FROM tempdb.guest.tmp
WHERE tempdb.guest.rpt.pos_num = tempdb.guest.tmp.pos_num

SELECT * FROM tempdb.guest.rpt ORDER by port_num



Kindly help me on this.

Thanks.

[venkiatmarut]

Please give me some suggestions on this problem?

[Aussiebear]

Would this not be better off in the SQL forum?

[Blade Hunter]

You will need to make some minor mods but here is my T-SQL parser that I use.
Dim sqlText As String
Dim SQLColumns As Integer
Dim NoRefresh As Boolean

Sub RunString()
sqlText = "SELECT * FROM Table WHERE Column1 = Column2"
SQLColumns = 10
Call Main
End Sub

Sub Main()
'Database calling routine
Dim Conn As New ADODB.Connection
Dim RS As New ADODB.Recordset
Dim Cmd As New ADODB.Command
Dim Row As Long
Dim Findex As Long
Dim Data As Worksheet
Dim X As Long
Dim Server As String
Application.Calculation = xlCalculationManual '<--- Turns the calculation off, this serves as a speed boost when there are formulas haning off the data
Set Data = Sheets(ActiveSheet.Name)
Data.Select
Range(IIf(ActiveSheet.Name = "Main", "A", "B") & ":W").ClearContents '<--- Change this to the columns you are returning data to (Clears them out of previous data first)
Server = "ServerName"
DatabaseUserName = "UserName"
DatabasePassword = "Password"
Conn.Open "PROVIDER=SQLOLEDB;DATA SOURCE=" & Server & ";USER ID=" & DatabaseUserName & ";PASSWORD=" & DatabasePassword
Cmd.ActiveConnection = Conn
Cmd.CommandType = adCmdText
Cmd.CommandText = sqlText
Set RS = Cmd.Execute
For X = 0 To SQLColumns - 1
Data.Cells(1, X + IIf(ActiveSheet.Name = "Main", 1, 2)) = RS.Fields(X).Name
Next
Do While Not RS.EOF
Row = Row + 1
For Findex = 0 To RS.Fields.Count - 1
On Error GoTo ErrorCode
Data.Cells(Row + 1, Findex + IIf(ActiveSheet.Name = "Main", 1, 2)) = RS.Fields(Findex).Value
Next Findex
RS.MoveNext
Loop
Application.Calculate
Application.Calculation = xlCalculationAutomatic
Exit Sub
ErrorCode:
Data.Cells(Row + 1, Findex + 1) = "#N/A"
Resume Next
End Sub