How to extract data from Oracle Database into Excel Spreadsheet via VBA code

[rkc2009]

Hey everyone,

I just got assigned this new project where I have to extract data from an Oracle database and place it into Excel via VBA code. As of right now, I am unsure of the column headings within the Oracle database. If I knew that, I knew there would be a way to query those headings and possibly extract data in that fashion. What would be a great help is some general code I could use for interacting with an Oracle database so that I can extract the data that I would like. My final objective is to create a raw table of all this data and then to produce charts from this data graphing rejection rates on a weekly basis. Basically, data from the Oracle database is extracted each week, and the raw table will be updated by placing the appropriate data under the appropriate week. After this update, the newest data is added to the charts generated so that the user can see the rejection rates of the data over time. If this description is too vague, let me know. Hope someone has some useful information that could help me with this. Thanks to all for your time.

[Oorang]

You don't even really need code, you can just use a database query. In Excel 2003, Data>Import External Data>New Database Query

[rkc2009]

Thanks. I just found that out today as well from doing more research on database interactions in Excel. So the next question I had about this was that I have data that comes in every week. I will get weekly data from the Oracle database, and once I import it into Excel, I need to place the imported data in the right spot. This means that if I get data for week 3, I need to place it in the week 3 row so that it follows data from week 1 and 2. Then, each time this occurs, the charts should be automatically updated so that the week 3 data is added to the data already present in the chart for week 1 and week 2. If there is any advice on this concept, it would be greatly appreciated. I look forward to everyone's replies.

[Oorang]

This will allow you to specify where the data lands. If you need help with the connection string, this resource might be useful.
[vba]Option Explicit

Public Sub Example()
Const strSQL_c As String = _
"SELECT * From Customers " & _
"WHERE Country = 'UK' " & _
"ORDER BY CompanyName;"
Dim strConnection As String
Dim strDBPath As String
strConnection = _
"ODBC;DSN=MS Access Database;DBQ=" & _
"C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb;"
QueryDB strConnection, Sheet1.Cells(1, 1), strSQL_c
End Sub

Public Sub QueryDB(ByVal connectionString As String, ByVal target As _
Excel.Range, ByVal SQL As String)
Dim qt As Excel.QueryTable
Dim ws As Excel.Worksheet
Set ws = target.Parent
Set qt = ws.QueryTables.Add(connectionString, target, SQL)
qt.Refresh BackgroundQuery:=False
End Sub
[/vba]

[rkc2009]

Hey Aaron,

Thanks for the VBA code. That helps me get a good visualization of how to move the data. Just to clarify, this code is for when I want to paste newly acquired data into a specific area of the excel sheet even when there is, for example, two rows of data before it? I know this code was just an example, but this part of the code I was confused about:

Public Sub Example()
Const strSQL_c As String = _
"SELECT * From Customers " & _
"WHERE Country = 'UK' " & _
"ORDER BY CompanyName;"
Dim strConnection As String
Dim strDBPath As String
strConnection = _
"ODBC;DSN=MS Access Database;DBQ=" & _
"C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb;"
QueryDB strConnection, Sheet1.Cells(1, 1), strSQL_c
End Sub

If say my headings were Week, % Rejections, and Average, how would they fit in this code. And also, is the strConnection statement the place where I will put the code to connect to the specific Oracle database I am trying to access? I know my questions may be a little unclear, so let me know if I need to clarify them. Again, thanks so much for your help, I'm not much of a programmer yet they keep making me program.

[Oorang]

1.) Two ways you can go, change the SQL so the headers are provided. Example: Say the field name was "Foo" but you wanted the header of "Bar". You could do :

SELECT MyTable.Foo as Bar, MyTable.MyField2, MyTable.MyField3 FROM MyTable

1B.) You could also just hardcode the headers in the worksheet and just not pull in the field names:

Option Explicit
 
Public Sub Example()
    Const strSQL_c As String = _
    "SELECT * From Customers " & _
    "WHERE Country = 'UK' " & _
    "ORDER BY CompanyName;"
    Dim strConnection As String
    Dim strDBPath As String
    strConnection = _
    "ODBC;DSN=MS Access Database;DBQ=" & _
    "C:\Program Files\Microsoft Office\OFFICE11\SAMPLES\Northwind.mdb;"
    QueryDB strConnection, Sheet1.Cells(2, 1), strSQL_c
End Sub
 
Public Sub QueryDB(ByVal connectionString As String, ByVal target As _
    Excel.Range, ByVal SQL As String)
    Dim qt As Excel.QueryTable
    Dim ws As Excel.Worksheet
    Set ws = target.Parent
    Set qt = ws.QueryTables.Add(connectionString, target, SQL)
    qt.FieldNames = False
    qt.Refresh BackgroundQuery:=False
End Sub

2.) Yes you will need to update strConnection to be a valid connection string for your Oracle database.