View Stored Proc Command Text in a Cell

[Bench]

Hi,

I'm working on a Macro that is working intermittently to change the Command Text in a Stored Proc connection property.

e.g. exec dbo.usp_ShepherdBonusDataBreakdown 'Sonya Taylor',7,2013

Changes to [SurveyorName] based upon 'Variables' Range "E4"

With Workbooks("Surveyor Statement V8.xlsm").Connections("115Points").OLEDBConnection
.CommandText = "exec dbo.usp_ShepherdBonusDataBreakdown '" & Worksheets("Variables").Range("E4").Value & "'," & Worksheets("Variables").Range("E3").Value & "," & Worksheets("Variables").Range("E2").Value
End With

As stated sometimes it works other times it doesn't.

What would be really useful would be to see the Command Text in a cell as there are several proc Text's changing based upon that value.

Is there a way to do this to save me going through DATA>Connections>Properties>Definition every time to check the macro has worked.

Any ideas?

Thanks

 

[Andrew Poulsom]

You can use this to print it to the Immediate window:

Debug.Print .CommandText

or to put it in A1 on the ActiveSheet:

Range("A1").Value = .CommandText