EXCEL VBA与SQL server的交互（一）-Toy模板网

这篇具有很好参考价值的文章主要介绍了EXCEL VBA与SQL server的交互（一）。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

引言

开一个系列讲一下VBA与SQL server之间的交互

本片是第一讲，主要内容为在EXCEL端怎么连接SQL数据库，以及简单的数据抽取及保存

首先大家要知道，在EXCEL中是可以不用写VBA代码就能连接各类数据库的:

vba操作sqlserver数据库,数据库,microsoft

可以"数据"标签栏中设置连接数据库

这个本文不涉及

本文只讲用VBA连SQL数据

正文

首先我们要连接SQL server需要引用两个库，也就是：

Microsoft ActiveX Data Objects 6.1 Library

Microsoft ActiveX Data Objects Recordset 6.0 Library

有些低版本的EXCEL可能只有低版本的库，也没关系，大家根据自己的情况选择

vba操作sqlserver数据库,数据库,microsoft

然后就是正式的数据库连接了，先创建一个连接对象：

Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
'也可以合并起来写：Dim con As New ADODB.Connection

接着连接数据，这里我们需要四个参数，也就是数据库地址、具体数据库的名称、用户名、以及密码

conn.ConnectionString = "Provider=SQLOLEDB;Server=127.0.0.1;Database=Test;Uid=sa;Pwd=111111"
conn.Open
'也可以合并起来写：conn.Open "Provider=SQLOLEDB;Server=127.0.0.1;Database=Test;Uid=sa;Pwd=111111"

这里我连接的是我自己本地的数据库，因此地址就是127.0.0.1，具体连接的是Test这个数据库，使用了管理员账号sa，密码为111111

连接好后可以测试一下：

MsgBox ("连接成功！数据库版本：" & conn.Version)

成功后我们来取一下数据库Test中text表里的前100行数据

我们需要几个参数：

Dim sql_text As String
Dim counter As Integer
sql_text = "SELECT top 100 * FROM [Test].[dbo].[test]"

Dim col_name() As String
Dim col_count As Integer

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

其中:

sql_text 为数据库查询语句
counter 为循环参数
col_name 为列名数组
col_count 为列的数量
rs 为查询结果

然后我们来执行数据库查询获取数据：

rs.Open sql_text, conn

再来获取列名及列数量：

col_count = rs.Fields.Count

ReDim Preserve col_name(0 To col_count - 1)
For counter = 0 To col_count - 1
    
    col_name(counter) = rs.Fields(counter).Name

Next counter

为啥要把列名先存进数组中？嗯。。。没啥特别的原因，可能是习惯使用数组

尔后将列名和内容存入表中：

With ThisWorkbook.Worksheets("Sheet1")
    
    .Range("A1").Resize(1, col_count) = col_name
    .Range("A2").CopyFromRecordset rs
    
End With

最后关闭链接，释放rs及conn

Set rs = Nothing
conn.Close
Set conn = Nothing

完整的代码如下：

Sub test()

Dim conn As ADODB.Connection
Set conn = New ADODB.Connection

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

conn.ConnectionString = "Provider=SQLOLEDB;Server=127.0.0.1;Database=Test;Uid=sa;Pwd=111111"
conn.Open

Dim sql_text As String
sql_text = "SELECT top 100 * FROM [Test].[dbo].[test]"
Dim counter As Integer
counter = 0

Dim col_name() As String
Dim col_count As Integer

With rs

    .Open sql_text, conn
    
    col_count = .Fields.Count
    ReDim Preserve col_name(0 To col_count - 1)
    
    For counter = 0 To col_count - 1
    
        col_name(counter) = .Fields(counter).Name
        
    Next counter
    
    With ThisWorkbook.Worksheets("Sheet1")
    
        .Range("A1").Resize(1, col_count) = col_name
        .Range("A2").CopyFromRecordset rs
    
    End With

End With

Set rs = Nothing
conn.Close
Set conn = Nothing

End Sub