Results 1 to 2 of 2

Thread: Retrieve Data from Oracle using VBA

  1. #1

    Question Retrieve Data from Oracle using VBA



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi There,
    I would like to connect to an Oracle database and retrieve data from several tables. The data retrieved from each table specified will populate separate worksheet.
    Any Help to get me started will be greatly appreciated.

  2. #2

    Retrieve Data from Oracle using VBA - some codes I started off with...

    Quote Originally Posted by foxxsh View Post
    Hi There,
    I would like to connect to an Oracle database and retrieve data from several tables. The data retrieved from each table specified will populate separate worksheet.
    Any Help to get me started will be greatly appreciated.

    Some codes I started off with but get error in the last line: mtxData = Application.Transpose(rs.GetRows)

    Sub Load_data()
    Dim cn As ADODB.Connection
    Set cn = New ADODB.Connection
    Set rs = New ADODB.Recordset

    cn.Open ( _
    "User ID=USERID" & _
    ";Password=PASSDOWD" & _
    ";Data Source=DATASOURCE" & _
    ";Provider=OraOLEDB.Oracle")

    Dim i As Long
    For i = 1 To 20
    Load_data_into_sheet Sheets("Sheet" & i), "FUNDS" & i, cn
    Next

    cn.Close
    End Sub
    Private Sub Load_data_into_sheet(ws As Worksheet, FUNDS As String, cn As ADODB.Connection)
    ws.Select
    Dim rs As ADODB.Recordset
    Dim col As Integer
    Dim row As Integer
    Dim Query As String
    Dim mtxData As Variant


    Set rs = New ADODB.Recordset
    rs.Open "select * from tbl_funds where fund_id_a in ('fund_id1','fund_id12') " & vbCrLf & _
    "order by pay_dt desc", cn

    With Sheet1
    col = 0
    'First Row: names of columns
    Do While col < rs.Fields.Count
    .Cells(1, col + 1) = rs.Fields(col).Name
    col = col + 1
    Loop

    mtxData = Application.Transpose(rs.GetRows)
    .Range("A2").Resize(UBound(mtxData, 1) - LBound(mtxData, 1) + 1, UBound(mtxData, 2) - LBound(mtxData, 2) + 1) = mtxData

    End With
    rs.Close
    End Sub

Posting Permissions

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