PDA

View Full Version : Using a Range name as part of a function



TedMosby
02-23-2009, 08:26 AM
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
02-23-2009, 08:56 AM
Range("range_name") = rsDW.GetRows

TedMosby
02-24-2009, 01:09 AM
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
02-24-2009, 01:26 AM
Why did you do that? You have two = in that statement, doesn't that suggest a problem to you?

TedMosby
02-24-2009, 03:54 AM
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
02-24-2009, 07:40 AM
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
02-24-2009, 08:42 AM
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
02-24-2009, 09:03 AM
separate statement, not separate query