1. Test

Just testing.

Code:
```Sub Oops()
Application.Calculation = xlCalculationAutomatic
End Sub```

_____ Workbook: 20SepSample3.xlsm ( Using Excel 2007 32 bit )
Row\Col
A
B
C
D
E
F
G
H
I
1
ICR
NotInH24
1MTH
3MTH
6MTH
1YR
3YR
5YR
2
0.61 1.52 7.14 0.92 2.88 3.47 4.03
21.11
3
0.73 -0.01 5.02 -2.43 -0.48 2.97 5.36
15.27
4
0.8 0.99 7.63 8.76 11.18 6.56 5.95
43.24
5
0.22 0.38 0.67 3.41 3.93 5.42 4.63
26.52
Worksheet: FundSelection

https://imgur.com/SnM2jm2
<blockquote class="imgur-embed-pub" lang="en" data-id="SnM2jm2"><a href="https://imgur.com/SnM2jm2">View post on imgur.com</a></blockquote><script async src="//s.imgur.com/min/embed.js" charset="utf-8"></script>
https://i.imgur.com/SnM2jm2.jpg

The text that you have entered is too long (27539 characters). Please shorten it to 25000 characters long.

I have never used that copyfromrecordset thing, Pete. I really don’t know too much about anything ADO, ADODB
If I understand a quick look at the documentation, ( https://docs.microsoft.com/en-us/off...yfromrecordset ) , that copyfromrecordset thing seems to let you paste out all your “data” in one go to a spreadsheet.
I assume the “data” is something that crudely to the uneducated mind like mine is something that very approximately could be considered as
Record = approximately rows
Field = approximately columns
Recordset = approximately a set of rows = approx like 2D array of data = approx like spreadsheet range Area of data

So I will take a guess that what that copyfromrecordset thing does is
_a) somehow convert that data into something it recognises as a 2D VBA array, say, arrFromRecordset ()
_b) it works out the range size for you, so that you only have to pass it top left of where you want it.

I guess then it effectively does something like this: ( This is all pseudo like )

Ws.Range( Ws.Cells(1, 1) , Ws.Cells(rs.Fields.Count, rs.Fields.Count) ) = arrFromRecordset ()

( in this example, I assume you gave the copyfromrecordset thing a top left of A1. So like you gave
Ws.Range("A1").CopyFromRecordset rs __ )

In other words
Ws.Range("A1").CopyFromRecordset rs
is approximately like
Ws.Range( Ws.Cells(1, 1) , Ws.Cells(rs.Record/Rows.Count, rs.Fields.Count) ) = arrFromRecordset ()
where arrFromRecordset () is something we might recognise as a simple VBA 2D array which the copyfromrecordset thing made for us from our ADO “data”

So if I have that all approximately correct, then that is probably a very efficient way of getting all your data into a worksheet range, ( assuming Microsoft did a good development job on it… )

I note again that the OP said he didn’t want to use range. I wonder if he meant he did not want to use the copyfromrecordset thing since it is the Range.CopyFromRecordset method
I expect only a sub set of the data was wanted, possibly just some “column” data

What would be useful would be to able to get direct access to that arrFromRecordset ()
I expect we can’t. That is internally made and used internally only
We can do that indirectly: We could paste it all out using the copyfromrecordset thing.
Then take that data into an array, arrIn()
Then Clear that data
Then paste the subset of rows and columns you want in one go using the Index code line like
____Index( arrIn(), Rws(), Columns() )

I suppose the obvious comment to that is “why not paste it all out and then delete what you don’t want
In a lot of cases you probably might as well do that.
But deleting rows and columns in a spreadsheet can sometimes take a while, even with coding, as interactions with a spreadsheet are often very slow.
On the other hand the alternative I suggest can be very efficient in comparison.
Just to clarify what that alternative to deleting stuff would be: It would be this:
_ take all info in, from the spreadsheet , in one go,
_ then using coding to get your wanted arrOut() , via
_arrout()=Index( arrIn(), Rws(), Columns() )
_ Then pasting out that array in one go
( You can “cut out the middle man”, like pseudo
MyWantedDataRange= Index( arrIn(), Rws(), Columns() ) __ )

At the end of the day it could be either
_ all splitting hairs
_ or not
Just depends I suppose how much data you are playing with and how often you do it.

I am just mentioning a few possibilities, discussing alternative, that’s all.
I am pretty ignorant about data analysis, data bases and stuff.
I had a massive amount of data to get at and organise for an important personal project a few years ago.
I got some great help in forums with using advanced data bases , including some ADO, SQL stuff, that I couldn’t begin to understand
A few years later I re did a lot of it using more simple things including some of the Index( arrIn(), Rws(), Columns() ) ideas and a few other unconventional things , often using VBA in unusual ways. .
In some cases it worked a lot better, and most people told me it wouldn’t

Alan

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•