用Excel VBA导入导出Oracle数据

最新推荐文章于 2021-11-17 08:00:29 发布

原创最新推荐文章于 2021-11-17 08:00:29 发布 · 823 阅读

1 ·

本内容遵循CC 4.0 BY-SA版权协议

收录于

数据库

本文介绍了如何在Excel VBA中与Oracle数据库进行数据导入和导出操作。首先，详细说明了安装Oracle Instant Client (Basic + ODBC) 的过程，并配置环境变量和数据源。在VBA代码示例中，展示了如何建立连接并处理中文字符显示问题。

用Excel VBA导入导出Oracle数据

导入数据
下载数据
- 下载的中文字符变问号?问题
- VBA代码示例

导入数据

安装Oracle Instant Client(Basic + ODBC)

下载链接: Oracle Instant Client Downloads
我本机是64位，但我选择了32位的版本（因为开始用64位时，用VBA老是报找不到Drivers，不清楚原因，可能因为我的Excel是32位，VBA总是查询32位的ODBC数据源），选择下载Instant Client的Basic Package版本以及对应的ODBC Package，我这里下载的版本号是19.6.0.0.0。

安装方法：把下载的两个zip文件都解压到同一目录上，然后运行“odbc_install.exe”（需管理员身份运行）

配置环境变量

配置环境变量：ORACLE_HOME = Oracle Instant Client的目录

配置数据源

配置ODBC数据源：新增一个DSN，选择Oracle in instantclinent_19_6，DSN名字自定义，可以填上数据库连接主机、用户名、密码（但这些都可以不填，VBA代码可以设置运行时再弹出提示框再填写），这个DSN的名字在VBA连接中需要用到，我这里取名为Oracle。

VBA代码示例

下面定义了一个Connection类，把Connection/Recordset打包一起，后续可以扩展。

' 创建一个Connection连接类，封装一些简单功能
' 保存为类模块：OracleConnection

Private con As New ADODB.Connection
Private cmd As New ADODB.command
Private rs As New ADODB.Recordset

Private Sub Class_Initialize()
    
End Sub

Public Sub setConnetion(DSN As String)         '采用弹出提示框，需要输入主机、用户名、密码连接
    con.Open "Data Source=" & DSN & ";Prompt=2"
End Sub

Public Function isConnected() As Boolean
    isConnected = con.State
End Function

Public Function C() As ADODB.Connection     '返回当前使用的连接
    Set C = con
End Function

Private Sub Class_Terminate()           '对象使用完后关闭连接
    If rs.State = adStateOpen Then
       rs.Close
    End If
    If con.State = adStateOpen Then
       con.Close
    End If
    Set cmd = Nothing
End Sub

Public Sub RunSql(tsql As String)       '执行SQL语句
    If rs.State = adStateOpen Then
       rs.Close
    End If
    If con.State = adStateClosed Then
        setConnetion hostName
    End If
    Set cmd = Nothing
    cmd.ActiveConnection = con
    cmd.CommandText = tsql
    Set rs = cmd.Execute
End Sub

Public Function r() As ADODB.Recordset      '执行完SQL语句后,可以使用该函数返回结果集(如有)
    Set r = rs
End Function

以下是测试连接执行代码

    Dim con As OracleConnection
    Set con = New OracleConnection
    con.setConnetion "Oracle"	'参数为DSN名字
    con.RunSql "insert into table_name (field1, field2) values(x1,y1),(x2,y2) "

下载数据

下载的中文字符变问号?问题

需要配置环境变量：NLS_LANG = AMERICAN_AMERICA.ZHS16GBK

VBA代码示例

以下是测试连接执行代码

    Dim con As OracleConnection
    Set con = New OracleConnection
    con.setConnetion "Oracle"	'参数为DSN名字
    con.RunSql "select * from table_name where rownum <= 100"
    ActiveSheet.Cells(1.1).CopyFromRecordset con.r	'将结果集下载到当前Active的Excel中

标签

#数据库 #oracle #vba