June 21, 2024


Welcome to the core

Connecting to an Oracle Database

This code will connect to an oracle database using a user inputted username and password, run an SQL query contained within a cell and populate the desired sheet with the results.

As normal the parts in bold will need to be changed for your own needs.


Dim UserP As String

UserN = Sheets(“Sheet3“).Range(“E1“).Value

‘where the username is stored

UserP = Sheets(“Sheet3“).Range(“E2“).Value

‘where the user’s password is stored


If Sheets(“Sheet3“).Range(“F1“).Value = “ERROR” Then

MsgBox (“UserName Missing“)

GoTo 1


If Sheets(“Sheet3“).Range(“F2“).Value = “ERROR” Then

MsgBox (“Password Missing“)

GoTo 1

End If

‘check if the Username and password have been provided


Set conn = CreateObject(“ADODB.Connection”)

With conn

.ConnectionString = “Driver={Microsoft ODBC for Oracle}; ” & _



“(HOST=HostName)(PORT=PortNumber))” & _

“(CONNECT_DATA=(SID=SIDNAME))); uid=” & UserN & “; pwd=” & UserP & “;”


End With

Set TargetRange = Sheets(“Sheet2“).Range(“A14“)

‘set range where the data is to be populated

Set rs = CreateObject(“ADODB.Recordset”)

strSQL = Sheets(“Sheet3“).Range(“A41“)

‘location of the SQL Query as it’s in a cell

rs.Open strSQL, conn, 3, 3


TargetRange.Offset(1, 0).CopyFromRecordset rs



Set rs = Nothing


Set conn = Nothing


End If

End Sub


Hope you enjoy

Copyright © All rights reserved. | Newsphere by AF themes.