Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel input form

Status
Not open for further replies.

amal1973

Technical User
Jul 31, 2001
131
US
Dear Developers …
I have created a Stored Procedure in SQL 2000 that take the Department number as a parameter and sends the populated record set to Excel.
I would really appreciate any help in designing a form in excel that will prompt the user to enter the Department number

This is the Stored Procedure

CREATE PROCEDURE RC_Costs_1
@RC nvarchar (255)

AS
SELECT RC, [Desc], COUNT([Desc]) AS [Count Desc], Cost, COUNT([Desc]) * Cost AS total, Service, BillingPeriod
FROM tblTempCostTable

where (RC = @RC)
GROUP BY RC, [Desc], Cost, Service, BillingPeriod

GO

This is the Excel VBA Code

Public Sub ExecuteSP()
Dim objConn As ADODB.Connection
Dim rsData As ADODB.Recordset
Dim szConnect As String

'Create the connection String
szConnect = "Provider=SQLOLEDB;Data Source =LAPTOP\SQL2000;" & _
"Initial Catalog=DP_Charges;Integrated Security=SSPI"

Set objConn = New ADODB.Connection
Set rsData = New ADODB.Recordset


objConn.Open szConnect
objConn.sp_RC_Costs_1 "0002286", rsData
'Make sure we got records back
If Not rsData.EOF Then
Sheet1.Range("A1").CopyFromRecordset rsData
rsData.Close

Sheet1.UsedRange.EntireColumn.AutoFit
Else
MsgBox "error: No records returned.", vbCritical
End If
'Clean up our Objects
If CBool(objConn.State And adStateOpen) Then objConn.Close
Set objConn = Nothing
If CBool(rsData.State And adStateOpen) Then rsData.Close
Set rsData = Nothing


End Sub

Thank you
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top