98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Paste an array containing the result returned by a stored proced
Paste an array containing the result returned by a stored proced
Resolved · High Priority · Version 2010
Sat 13 Oct 2012, 13:22Delegate Alain said...
Alain has attended:
Excel VBA Intro Intermediate course
Excel VBA Advanced course
Access Introduction course
Access Intermediate course
Access Advanced course
Access VBA course
Introduction to Management course
Paste an array containing the result returned by a stored proced
Hi,
I run a stored procedure in a Microsoft SQL server database and I would like to paste the data in a spread sheet. Creating the ADO connection is not an issue. Usually I use the function "transpose" to put the data in an Excel spreadsheet. The code looks like that:
ThisWorkbook.Sheets("Data").Range("A1:C150") = _
WorksheetFunction.Transpose(RecordArray)
knowing that RecordArray is the variable used to store the result of the stored procedure.
Sometimes this method does not work. I have done some reading on the internet and came to the conclusion that the reason why "transpose" does not work is probably the type of data that I try to manipulate.
My understanding is that the best way to dump the data in a spread sheet would be to use a loop. However I did not manage to find any good and understandable example. Would you mind showing how to do so?
If there is any other (easier or een less straightfoward) method to do this could you please let me know about it?
I thank you in advance for your help
Alain
Mon 15 Oct 2012, 13:51Trainer Gary said...
RE: Paste an array containing the result returned by a stored pr
Hi Alain
Thanks for getting in touch.
You can loop through the array using a FOR NEXT loop, substituting the current array position for the current count of the loop.
Assuming something like this, RangeArray(3, 150) the code could be:
For i = 1 to 150
For j = 1 to 3
activecell.offset(i,j) = RangeArray(i,j)
Next j
Next i
To loop across rows and columns, you should have a loop within a loop (i then j).
Have a look at the example I've suggested above and see how you goes. If you need further explanation please let me know.
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Mon 22 Oct 2012: Automatically marked as resolved.
Training information:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Calculate age or serviceThe DATEDIF() function in Excel calculates the number of days, months, or years between two dates. So, this function makes it easy to calculate a person's age. To try this tip: |
