'装好oracle客户端,不需引用直接粘贴下列格式运行
Sub connect()
Dim strconnt As String
Set connt = New ADODB.Connection
Dim rs As Object
Set rs = New ADODB.Recordset
Dim sevip, Db, user, pwd As String
'设服务器地址、所连数据,及登录用户密码
sevip = "IP地址"
Db = "数据库名"
user = "用户名"
pwd = "密码"
strconnt = "DRIVER={Microsoft ODBC for Oracle};Password=apps;User ID=apps;Data Source=demo" '
connt.ConnectionString = strconnt
connt.Open
End Sub
'=========================
Sub linkOracle()
Dim strConn As String '连接字符串
Dim dbConn As Object '连接对象
Dim resSet As Object '查询结果集
Dim db_sid, db_user, db_pass As String 'sid,用户名,密码
'设置自己的链接数据
db_sid = "数据库名"
db_user = "用户名"
db_pass = "密码"
'创建对象
Set dbConn = CreateObject("ADODB.Connection")
Set resSet = CreateObject("ADODB.Recordset")
'拼接链接字符串 下面两个选一个
strConn = "Provider=OraOLEDB.Oracle.1; user id=" & db_user & "; password=" & db_pass & "; data source = " & db_sid & "; Persist Security Info=True"
strConn = "Provider=MSDAORA.1; user id=" & db_user & "; password=" & db_pass & "; data source = " & db_sid & "; Persist Security Info=True"
'-----打开数据库----
dbConn.Open strConn
'执行查询
Set resSet = dbConn.Execute("select * from 表名")
'打印表头
For j = 0 To resSet.Fields.COUNT - 1
Cells(1, j + 1) = resSet.Fields(j).Name
Next
'粘贴结果
Range("A2").CopyFromRecordset resSet
'-----关闭连接----
dbConn.Close '关闭数据库
End Sub