I have a project where I have to send data from an Excel spreadsheet to an Oracle database.
Here is the code I am using and I need some help. It stops at the second string statement. I am trying to use an Excel range name to Insert or Update into an Oracle database. Any help would be appreciated.
Const hostName = "host"
Const portNo = "port"
Const srvSID = "SID"
Const usrID = "username"
Const usrPwd = "password"
Sub ADOInsert()
Dim cnAdo As ADODB.Connection
Dim strConnString As String
Dim strSQL As String
Dim ws1 As Excel.Worksheet
'set the worksheet:
Set ws1 = ThisWorkbook.Worksheets("IMPDATA")
Dim Imprange As Range
Set Imprange = ws1.Range("a2:d6")
strDriver = "Driver={Microsoft ODBC for Oracle};"
strParams = "CONNECTSTRING=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=" + hostName + ")(PORT=" + portNo + "))(CONNECT_DATA=(SID=" + srvSID + ")));"
strUser = "UID=" + usrID + ";PWD=" + usrPwd + ";"
strConnString = strDriver + strParams + strUser
Set cnAdo = New ADODB.Connection
cnAdo.Open strConnString
'/ this is hardcoded for number of rows and columns.
For lngRow = 1 To 10
strSQL = "Insert Into TESTDATA1 Select "
strSQL = strSQL & "'" & Range(Imprange, 1).Value & "', " '/ REQUEST_NUMBER
strSQL = strSQL & "'" & Range(Imprange, 2).Value & "', " '/ CID
strSQL = strSQL & "'" & Range(Imprange, 3).Value & "', " '/ SERVER_TYPE
strSQL = strSQL & "'" & Range(Imprange, 4).Value & "' " '/ COST
cnAdo.Execute strSQL
Next
cnAdo.Close
Set cnAdo = Nothing
End Sub