Results 1 to 1 of 1

Thread: Test

  1. #1

    Thread Starter
    New Member Doc.AElstein's Avatar
    Join Date
    Sep 2020
    Location
    Hof, Bavaria, Germany
    Posts
    5

    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
    Name:  arr1D_b()OptionBase0.jpg
Views: 5
Size:  53.5 KB



    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
    Attached Files Attached Files
    Last edited by Doc.AElstein; Oct 26th, 2020 at 04:49 AM.

Posting Permissions

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



Click Here to Expand Forum to Full Width