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

Else

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}; ” & _

“CONNECTSTRING=(DESCRIPTION=” & _

“(ADDRESS=(COMMUNITY=ORAserverLocation)(PROTOCOL=TCP)” & _

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

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

.Open

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

 

rs.Close

Set rs = Nothing

conn.Close

Set conn = Nothing

1

End If

End Sub

 

Hope you enjoy