|
Get Data from
Oracle to Excel using an Macro
Hi Guys, I was back after a long time with something useful to DBA’s. After
enough play with SAS, I started playing with Visual Basic Application (VBA) and
Excel concentrating on getting Oracle data into Excel through an Macro. I know a
very little bit of VB through which I managed to get this. Especially, I found
it quite easy to connect to an Oracle Database with ADODB, execute a select
statement and import the result into an Excel worksheet.
Now we are getting the data into excel which is just same what you get if you
fire “select * from dba_tables”. In my code, there is no need to pass any of the
parameters just click on play macro and enjoy the play. J I would like to assist
you through a step by step guide to complete this task which will take less than
5minutes of your precious time.
1. Open a new workbook.
2. Press Alt+F11 to run the Visual Basic Editor.
3. On the Insert menu, click Module.
4. Paste the code below into the module sheet
5. Set the username, password and TNS name as per your database.
6. Click on Tools > References and select Microsoft ActiveX Data Objects 2.x
Library. (x being a number)
7. Close the editor and return to Microsoft Excel.
8. Select Sheet1.
9. On the Tools menu, point to Macro, and then click Macros.
10. In the Macro dialog box, click AnalyzeDBATables, and then click Run.
Sub AnalyzeDBATables()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim col As Integer
Dim row As Integer
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
cn.Open ( _
"User ID=test" & _
";Password=password" & _
";Data Source=Development" & _
";Provider=MSDAORA.1")
rs.Open "select * from dba_tables", cn
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
' Now actual data as fetched from select statement
row = 1
Do While Not rs.EOF
row = row + 1
col = 0
Do While col < rs.Fields.Count
Cells(row, col + 1) = rs.Fields(col).Value
col = col + 1
Loop
rs.MoveNext
Loop
End Sub
|
This macro assumes that my username is test, my password password and the net
service name Development, that is, that I'd have to connect with SQL*Plus like
so: sqlplus test/password@Development.
|
