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
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