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