这次维护的项目需要根据Excel文件中条件,抽取数据库的信息,导出到CSV文件。
然后就给出了两种方案:一、直接连接数据库,写入本地文件。;二、WebService取得最终的信息,写入本地文件。
首先看直接连接数据库。
默认VBA环境是不支持连接数据库的,需要手动添加引用,如下图:
选择【Microsoft ActiveX Data Objects 2.8 Library】,打勾,按OK按钮返回。
如果图中表示的不一样,尽量选择高版本的。
VBA中采用ADODB的方式,定义三个变量
Dim conn As New ADODB.Connection
Dim cmd As New ADODB.command
Dim rs As New ADODB.Recordset
本次使用的SqlServer,连接字符串如下
Provider=SQLOLEDB;Server=数据库地址;User ID=用户;Password=密码;database=数据库名;Connection Reset=FALSE
设置连接字符串,并打开连接
conn.ConnectionString = strConn
conn.Open
设置Command的连接和sql语句
Set cmd.ActiveConnection = conn
cmd.CommandText = strSQL
执行command,结果存放在rs里
Set rs = cmd.Execute
查询结果的表头信息在rs.Fields里
可以遍历一下,取出来放在Sheet1里,从单元格A1开始
For col = 0 To rs.Fields.Count - 1
Worksheets("Sheet1").Range("A1").Offset(0, col).Value = rs.Fields(col).Name
Next
然后把结果放在第二行,从单元格A2开始
Worksheets("Sheet1").Range("A1").Offset(1, 0).CopyFromRecordset rs
到目前为止只是为了验证执行结果,下面开始写入CSV文件。
定义文件对象,文件对象的使用方法可以查看资料
Dim fs, f
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.OpenTextFile("c:\sample.csv", 8, True)
定义临时变量,存放CSV的一行信息
Dim csvRowText As String
csvRowText = ""
首先把表头信息写入文件
For col = 0 To rs.Fields.Count - 1
If col = 0 Then
csvRowText = rs.Fields(col).Name
Else
csvRowText = csvRowText + "," + rs.Fields(col).Name
End If
Next
f.writeline csvRowText
游标移到第一行
rs.MoveFirst
遍历结果集,防止全是数字时,在excel打开时,显示成数字,所以都加上双引号.
While (rs.EOF = False)
csvRowText = ""
For col = 0 To rs.Fields.Count - 1
If col = 0 Then
csvRowText = """" + rs(col).Value + """"
Else
csvRowText = csvRowText + ",""" + rs(col).Value + """"
End If
Next
f.writeline csvRowText
rs.MoveNext
Wend
f.Close
关闭连接,释放资源
Set rs = Nothing
conn.Close
Set conn = Nothing
以上就是VBA连接数据库时的查询操作,当然在高级语言里ADODB能做update,delete,insert,VBA里都能做到。
根据需要灵活即可。