How can I use Excel VBA to transfer information from an Oracle table to an Excel worksheet?

edit

Here is a sample VBA code that retrieves data from an Oracle database and populates it in an Excel worksheet:

First, ensure that you have the Oracle ODBC driver installed on your computer to create a connection between Excel and Oracle. Then, in the Excel workbook, go to Developer > Visual Basic to open the Visual Basic Editor.

1. Create a reference to the necessary objects, such as ADODB.Connection, ADODB.Recordset, and Excel.Range, by going to Tools > References and selecting the appropriate libraries.

2. Set up the connection to the Oracle database using the connection string information. The following code example uses Windows authentication to connect. You may need to adjust the connection string based on your database and login details.

Dim con As New ADODB.Connection
con.ConnectionString = "Driver={Oracle in instantclient_12_2};DBQ=myservername;Uid=myUsername; Pwd=myPassword;"
con.Open

1. Declare a SQL statement and execute it using the connection object. The results are stored in a recordset.

Dim sql As String
sql = "SELECT * FROM tablename"
Dim rs As New ADODB.Recordset
rs.Open sql, con

1. Copy the data from the recordset to a worksheet range. In this example, the data starts at cell A1.

Dim rng As Excel.Range
Set rng = Worksheets("Sheet1").Range("A1")

' Copy the field names
Dim i As Integer
For i = 0 To rs.Fields.Count - 1
    rng.Offset(0, i).Value = rs.Fields(i).Name
Next

' Copy the data
rng.Offset(1, 0).CopyFromRecordset rs

1. Close the recordset and connection objects.

rs.Close
con.Close
Set rs = Nothing
Set con = Nothing

Put all of this code together into a Sub or Function, and run it to transfer the Oracle data into an Excel worksheet.