|
楼主 |
发表于 2018-3-21 10:25
|
显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用 · 内置多项VBA编程加强工具 ★ 免费下载 ★ ★ 使用手册★
- 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 = "oss"
- db_user = "npo"
- db_pass = "npo1234"
- '创建对象
- 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; user id=" & db_user & "; password=" & db_pass & "; data source = " & db_sid & "; Persist Security Info=True"
- '-----打开数据库----
- dbConn.Open strConn
- '执行查询
- Set resSet = dbConn.Execute("select * from c_lte_lncel")
- '打印表头
- 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
复制代码 |
|