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