Execute SP with 1 param from Excel

[MSBrady]

Excel 2003
SQL Server 2005

I have read through many posts but can't seem to find the answer to my problem. I am very green with VBA. I have a spreadsheet that uses external data to populate PivotTables. From these PT's I have an Analysis sheet that references rows in the PT's. I am trying to allow the users to execute a SP from the Analysis sheet that updates the data in the tables that the PV's point to. Below is some code I found to help me do this but I keep getting a RTE "80040e14 Automation Error":

Private Sub CommandButton1_Click()
'dim the objects we need to communicate
 Dim CON1 As New ADODB.Connection
 Dim Cmd1 As New ADODB.Command
 Dim rst As New ADODB.Recordset

'Set the current Worksheet to Analysis
Worksheets("Analysis").Activate
'set the current cell to A1
Range("A1").Activate
'turn off the screen so excel is faster
Application.ScreenUpdating = False
'make a connection string
With CON1
    .ConnectionString = "Provider=sqloledb;Server=SQLSERVER;Database=DENAPP;"
'Open the connection
    .Properties("Prompt") = adPromptAlways
    .Open
End With
With Cmd1
    .ActiveConnection = CON1
    .CommandType = adCmdStoredProc
    .CommandText = "Exec xWIPRecon"
'Add parameters
    .Parameters.Refresh
    .Parameters(0) = Sheet1.Range("E2").Value
    '.Parameters.Append .CreateParameter("FiscalNo", adChar, adParamInput, 6)
    Set rst = .Execute
End With
With CON1
    .Close
End With
Set CON1 = Nothing
End Sub

FiscalNo is the name of the param in the SP. The SP is:

LTER PROC [dbo].[xWIPRecon] --200610
@FiscalNo char(6)
as
DELETE FROM xwrk_WIPRecon

begin transaction
-- (1) Extract project transaction beginning balances by project from PJTRAN
INSERT xwrk_WIPRecon
select ledger = 'BEG', src = 'PJTRAN', client = rtrim(p.pm_id01), p.customer, product = rtrim(p.pm_id02), t.project, t.acct, 
sum(t.amount) as 'amount', t.fiscalno
from pjtran t join pjproj p 
	on t.project=p.project
where (t.acct like 'WIP%' or t.acct like 'APS TRANSFER%') 
and (t.alloc_flag = 'A'or t.bill_batch_id = 'Y' or (t.batch_type = 'BI' and (data1 like 'WIP%' or data1 like 'APS TRANSFER%')))
and t.fiscalno < @FiscalNo
group by p.pm_id01, p.customer, p.pm_id02, t.project, t.acct, t.fiscalno
commit

TIA for any help.

 

[SkipVought]

Hi,

"... I keep getting a RTE "80040e14 Automation Error":..."

On WHAT statement?

Skip,

_[red][/red]

 

[MSBrady]

Hello Skip,

Thanks for the quick reply. It'll make it past authentication and it appears to bomb on the .Execute statement.

thanks

 

[SkipVought]

You never set the Cmd1 object. Consequently .Execute, referring to the Cmd1 object has NOTHING.

Skip,

_[red][/red]

 

[PHV]

You never set the Cmd1 object
Skip, are you saying that the following doesn't instantiate the object ?

Dim Cmd1 As New ADODB.Command

 

[MSBrady]

I'm not sure what you mean by set the object. Doesn't this code set it?

With Cmd1
    .ActiveConnection = CON1
    .CommandType = adCmdStoredProc
    .CommandText = "Exec xWIPRecon"
'Add parameters
    .Parameters.Refresh
    .Parameters(0) = Sheet1.Range("E2").Value
    '.Parameters.Append .CreateParameter("FiscalNo", adChar, adParamInput, 6)
    Set rst = .Execute
End With

 

[SkipVought]

oops, missed that statement! Sorry

Skip,

_[red][/red]

 

[MSBrady]

Any ideas on this guys?