Run Oracle query using Excel VBA

See what this does for you.

In order to run it, you have to have the Oracle ODBC driver installed on your PC (it's installed if you have Oracle installed), and you have to go to Tools -> References in the VB editor and add the reference to the driver.

Const HOST$ = "ServerName"  ' Server hosting the Oracle db
Const DBNAME$ = "DatabaseName"
Const ORACLE_USER_NAME$ = "UserName"
Const ORACLE_PASSWORD$ = "Password"
 
 
Sub HarvestData()
    ' Makes the SQL call to collect the data.
    Dim strConOracle As String
    Dim oConOracle, oRsOracle
    Dim strSQL As String
    Dim lngCount As Long
    Dim strCount As String
    
    strConOracle = "Driver={Microsoft ODBC for Oracle}; CONNECTSTRING=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)"
    strConOracle = strConOracle & "(HOST=" & HOST & ")(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=" & DBNAME
    strConOracle = strConOracle & "))); uid=" & ORACLE_USER_NAME & " ;pwd=" & ORACLE_PASSWORD & ";"
    Set oConOracle = CreateObject("ADODB.Connection")
    Set oRsOracle = CreateObject("ADODB.RecordSet")
    oConOracle.Open strConOracle
    
 
    strSQL = "SELECT * FROM SCHEMA.TABLE"
    
    Set oRsOracle = oConOracle.Execute(strSQL)
    
    lngCount = 1
        
        Do While Not oRs.EOF
            DoEvents
            lngCount = lngCount + 1
            strCount = Trim(Str(lngCount))
            
            If Not IsNull(oRs.fields(0).Value) Then
                strTemp = oRs.fields(0).Value
            Else
                strTemp = ""
            End If
            Range("A" & strCount).Select
            ActiveCell.FormulaR1C1 = strTemp
            
            If Not IsNull(oRs.fields(1).Value) Then
                strTemp = oRs.fields(1).Value
            Else
                strTemp = ""
            End If
            If strTemp = "" Then strTemp = Now
            Range("B" & strCount).FormulaR1C1 = strTemp
            
            If Not IsNull(oRs.fields(2).Value) Then
                strTemp = oRs.fields(2).Value
            Else
                strTemp = ""
            End If
            Range("C" & strCount).FormulaR1C1 = strTemp
            
            If Not IsNull(oRs.fields(3).Value) Then
                strTemp = oRs.fields(3).Value
            Else
                strTemp = ""
            End If
            Range("D" & strCount).FormulaR1C1 = strTemp
            
            If oRs.fields(4).Value = "1" Then
                strTemp = "Defects"
            Else
                If oRs.fields(5).Value = SITE_AREA_CODE Then
                    strTemp = "Site"
                Else
                    strTemp = "Area"
                End If
            End If
            Range("E" & strCount).FormulaR1C1 = strTemp
            
            If lngCount = 65000 Then
                ' We've filled the current sheet. Make a new sheet, put labels on it,
                ' and begin filling that one, instead.
                Set shtNewSheet = Sheets.Add
                Sheets(Sheets.Count - 1).Move After:=Sheets(Sheets.Count)
                Sheets(Sheets.Count).Select
                lngCount = 1
            End If
            
            oRs.MoveNext
    Loop
End Sub

Select allOpen in new window

Columns A-D are filled in with the first four values returned from Oracle. Column E, in this case, fills in with a value determined by the combined contents of the fifth and sixth columns (indexes 4 and 5 in a zero-based array). Of course, your SQL can be much more elaborate than "SELECT * FROM ..." so you can specify exactly what will be where in your returned recordset.

This code also breaks the returned data into groups of 65,000 rows, because Excel has a limit of 65535 (or is it 65536?) rows on a single sheet. So if you're returning more data than 65,000 rows, the macro will continue to add new sheets for you as it goes.

I see, now, that I missed a snippet, though. After the Loop and before the End Sub, you should insert these three lines:

    oConOracle.Close
    Set oRsOracle = Nothing
    Set oConOracle = Nothing

Select allOpen in new window

I have Oracle installed...I do not see that driver under references though.  Would I need to use the browse option to look in the actual Oracle folders?  If so, there are a dozen of those *.dll files...specifically what do I need to add as a reference.

Thanks!

ok option #1 is working, I'll take a look at what Leon suggested in a moment.  Although it may have to wait until the morning.

Thanks!

I love this place.

I do my best to figure out how to do things (often using EE as a primary resource) and cobble together things that work, and I'm happy to share what I know because so many here have helped me. And even when I come in and provide a working solution, someone else can still come in and improve on it, which means now my own things will be faster and better the next time I need to do this.

Thanks, leonstryker!

Why do you need a VBA to pull data from Excel ?

You can create a ODBC connection.

Go to excel. Menu->Data->Import External Data

Select ODBC from file type and the correct source.

Write the query.

The data will be pulled into the active sheet.

You can refresh the data every time by right click on any one of the cells containing data and refresh.

This will become your local copy of the data file.

You can write macros to create report or change format.

(You will have GUI interface in MS-Query to edit the query fields. This will save time in writing exact code)

>Why do you need a VBA to pull data from Excel ?

There are lots of reasons to do it this way as opposed to using MS-Query among them are:

1. Its more stable (no persistant connection and separation of code from data.)
2. Its versitale (can switch databases quickly, use multiple datasources, as well as use complex store procedures to query data)
3. You can dump the results into separate files to be used as reports
4. etc.

Leon

Good question, I used to do this via Import External Data, and it pulled from existing MS access queries.  Worked fine, until I put it on a server, and had someone else update the file.  

For someone else to run it they needed to have their Oracle tnsnames.ora and personal.ora Txt files set-up properly, and an ODBC connection set-up under Data Sources.

When my IT guys set up the website to run Oracle queries on, it bypasses the need to have all that set-up on the users PC.  As long as they have Oracle installed they could use the web site.  Or rather, VBA could use the website from their computer and import the web queries into Excel.  

As the website is going to disapear soon, I needed another way, hence, running the entire query in VBA.  It'll still require the user has Oracle, but I don't need to get on each users computer and change the *.ora files and such.

Leon, I'm sorry for taking so long to try your suggestion.  I'm out of the office today, and running this stuff off VPN is terribly slow.  I'll try to look on Thursday, if not, I'm gone until Monday.  

I won't abandon the post, and promise to update as soon as I get a chance to test further.

Thanks for your help!

Much faster with the CopyFromRecordSet method.  Thank you!

I don't understand Late vs Early binding.  What's the difference?

I've included the VBA that's working, if you'd care to show how to mod it to be early binding, and explain the benifit?

Thanks everyone!

Sub HarvestData()
'On Error Resume Next
    ' Makes the SQL call to collect the data.
    Dim strConOracle As String
    Dim oConOracle, oRsOracle
    Dim strSQL As String
    Dim lngCount As Long
    Dim strCount As String
Dim x As String
x = Sheets("Sheet4").Range("T1")
    
    
    strConOracle = "Driver={Microsoft ODBC for Oracle}; CONNECTSTRING=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)"
    strConOracle = strConOracle & "(HOST=" & HOST & ")(PORT=57699))(CONNECT_DATA=(SERVICE_NAME=" & DBNAME
    strConOracle = strConOracle & "))); uid=" & ORACLE_USER_NAME & " ;pwd=" & ORACLE_PASSWORD & ";"
    Set oConOracle = CreateObject("ADODB.Connection")
    Set oRsOracle = CreateObject("ADODB.RecordSet")
    oConOracle.Open strConOracle
    
 
    strSQL = x
    
    Set oRsOracle = oConOracle.Execute(strSQL)
    
    ActiveSheet.Cells.CopyFromRecordset oRsOracle
    
    oConOracle.Close
    Set oRsOracle = Nothing
    Set oConOracle = Nothing
End Sub

Select allOpen in new window

The code I provided (that you have, above) uses this:
    Dim oConOracle, oRsOracle
    Set oConOracle = CreateObject("ADODB.Connection")
    Set oRsOracle = CreateObject("ADODB.RecordSet")
    oConOracle.Open strConOracle

That's "late binding," because oConOracle and oRsOracle are declared as variants and VB doesn't know what to do with them until later, when they actually have an object assigned.

leonstryker suggested this code instead:
    Dim oConOracle As ADODB.Connection
    Dim oRsOracle As ADODB.Recordset
    Set oConOracle = New ADODB.Connection
    Set oRsOracle = New ADODB.Recordset

From the outset, VB knows exactly what oConOracle and oRsOracle are and how to treat them. The nature of their structure is bound to the variable names immediately on declaration. This results in less internal overhead later any time it actually has to reference the objects or do anything with them.

From a practical standpoint, with this relatively small amount of code the differences in performance or resource utilization would be negligible. But in a larger project, all the little bits do add up. In situations where it is an option, early binding is considered good practice. leonstryker's suggestion is a good one.

-- b.r.t.

In additon, using Early Binding will help with syntax since the Intellisense will work (will show you the methods and properties).

The down side of Early Binding is that it is less portable and relies that all client machines use the same version of the DLL which you are referencing, while Late Binding generally does not care as long as the methods you are using are present.

Thanks for the grade,
Leon