Excel VBA 连接Oracle数据库

VBA宏下载：Oracle2Excel 导出字典

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 = "MYORCL"
    db_user = "scott"
    db_pass = "tiger"

    '创建对象
    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 emp")

    '打印表头
    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