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.
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.
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
Dim sql As String
sql = "SELECT * FROM tablename"
Dim rs As New ADODB.Recordset
rs.Open sql, con
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
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.
Please start posting anonymously - your entry will be published after you log in or create a new account. This space is reserved only for answers. If you would like to engage in a discussion, please instead post a comment under the question or an answer that you would like to discuss
Asked: 2022-06-27 11:00:00 +0000
Seen: 11 times
Last updated: Sep 23 '21
How can an Ansible playbook be used to run an Oracle script?
How can dbt be used on Oracle to perform an approximate relation match?
Can Visual Basic be used to code the Grep command?
How can I use VBA to create a border around cells that have text or a value in them?
How do ListBox and Item Values work in Visual Basic 2010?
Is it possible to utilize both Office Script and VBA macros within a single workbook?
How to integrate a value from an oracle database into an MVC Bootstrap project using .NET 5 and C#?
What is the method for retrieving information from an Oracle database using PHP?