Retrieve Data from Oracle using VBA - some codes I started off with...
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
