Extracting Values from Database into Array - Excel-VBA

Question

I'm connected to an oracle database that basically outputs data into a sheet using an SQL statement I give it:

This is the code that copies it into a spreadsheet:

 Set oRsOracle = New ADODB.Recordset
    With oRsOracle
        .ActiveConnection = oConOracle
        .Open "SELECT CalcGrp_Name FROM Calc_Group"
        Sheets(2).Range("A1").CopyFromRecordset oRsOracle
        .Close
    End With
    Set oRsOracle = Nothing

    oConOracle.Close
    Set oConOracle = Nothing

Basically instead of the line Sheets(2).Range("A1").CopyFromRecordset oRsOracle is there a way to store these values into an Array or any data struct VB has to offer, I basically want to use these values and randomly populate them into another a data-test generation file

Automate This · Accepted Answer · 2013-10-04 19:07:12Z

2

You can use arrays or Variants for example:

Dim dat as Variant
dat = Array(record1, record2, record3)

In a loop you can set individual records:

For i = 1 to 10
  dat(i) = 'your record
next

In your code try this:

Dim dat as Variant

With oRsOracle
    .ActiveConnection = oConOracle
    .Open "SELECT CalcGrp_Name FROM Calc_Group"
    dat = oRsOracle.GetRows
    .Close
End With

To print results:

Dim i As Long

For i = LBound(dat) to UBound(dat)
  Debug.Print dat(i)
Next

edited Oct 4, 2013 at 19:07

answered Oct 4, 2013 at 18:59

Automate This

31.5k11 gold badges63 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Thatdude1 Over a year ago

So instead of Sheets(2).Range("A1").CopyFromRecordset oRsOracle i should have a for loop with dat(i).CopyFromRecordset oRsOracle ?

Thatdude1 Over a year ago

Seem to be getting out range error's seems like its not adding it into data

Tim Williams Over a year ago

GetRows returns a 2-D array, so it's dat(field, row)

Thatdude1 Over a year ago

So if I wanted to put in a cell would this work ? Cells(1, "H").Value = dat(1, 1) considering there is only one column ?

Automate This Over a year ago

Flip it around: dat(1,1) = Cells(1, 10).value

|

almost · Accepted Answer · 2021-07-12 13:14:53Z

0

Try something liki this

Dim Qdata as variant
Qdata= recordset.GetRows /// recordset is Adodb recordset
Qdata= Application.Transpose(Qdata)

End you have nice array

answered Jul 12, 2021 at 13:14

almost

1

Collectives™ on Stack Overflow

Extracting Values from Database into Array - Excel-VBA

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related