current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

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

share|improve this question

1 Answer 1

up vote 1 down vote accepted

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
share|improve this answer
    
So instead of Sheets(2).Range("A1").CopyFromRecordset oRsOracle i should have a for loop with dat(i).CopyFromRecordset oRsOracle ? –  Thatdude1 Oct 4 '13 at 19:04
    
See my last edit, try GetRows method (untested) –  Portland Runner Oct 4 '13 at 19:07
    
Seem to be getting out range error's seems like its not adding it into data –  Thatdude1 Oct 4 '13 at 19:22
1  
GetRows returns a 2-D array, so it's dat(field, row) –  Tim Williams Oct 4 '13 at 19:24
    
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 ? –  Thatdude1 Oct 4 '13 at 19:31

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.