Using a Range name as part of a function

[TedMosby]

I have this section of code which is great as it picks up what I want from a ComboBox and uses it as part of my Execute Statement, but how can I create another which will look at a particular range and pull the result back?

the range is one particular cell or would I be best using example B5?



Set rsDW = New ADODB.Recordset
stProcName = "EXEC dbo.sp_PARA_PERFMANAGEREPORTBUILDLOAD '01/04/2008 00:00:00', '" & Worksheets("Proc").OLEObjects("cboGetDate").Object.Value & "' "
rsDW.CursorLocation = adUseClient
Debug.Print stProcName
rsDW.Open stProcName, cnnDW, adOpenDynamic, adLockOptimistic, adCmdText
Application.ScreenUpdating = False

[Bob Phillips]

Range("range_name") = rsDW.GetRows

[TedMosby]

I have used the code above and put this



stProcName = "EXEC dbo.sp_PARA_CREATEPERFMANAGESTORAGE '" & Worksheets("Proc").Range("ReportProdDate") = rsDW.GetRows & "' "


I get an Run Time Error 3704
Operation is not allowed when the object is closed

Where have I gone wrong?

[Bob Phillips]

Why did you do that? You have two = in that statement, doesn't that suggest a problem to you?

[TedMosby]

My code now looks like this.

I dont understand where you say two = in statement?

[code]

Set rsDW = New ADODB.Recordset
stProcName = "EXEC dbo.sp_PARA_CREATEPERFMANAGESTORAGE '" & Worksheets("Proc").Range("ReportProdDate") = rsDW.GetRows & "' "
rsDW.CursorLocation = adUseClient
Debug.Print stProcName
rsDW.Open stProcName, cnnDW, adOpenDynamic, adLockOptimistic, adCmdText
Application.ScreenUpdating = False

[Bob Phillips]

stProcName = "EXEC dbo.sp_PARA_CREATEPERFMANAGESTORAGE '" & Worksheets("Proc").Range("ReportProdDate") = rsDW.GetRows & "' "

two equal signs in there. The rng = rsDW.GetRows should be a separate statement after you have run the query.

[TedMosby]

If it's seperate from the query how does the EXEC Statement know what to use as its variable when running the command?

[Bob Phillips]

separate statement, not separate query