VBA操作数据库

相关背景

对于数据分析同学，一般SQL，EXCEL是必备技能，但对于VBA和Python可能有的同学不会；在处理本地数据上(诸如excel、txt|csv文本）；

excel简易sql查询插件，可转：https://blog.csdn.net/me_to_007/article/details/130417173

相关配置

转载自蓝色幻想

'一、Connection对象
  '1 建立和数据库的连接
    '.Open
   ' Dim con As New Connection
   ' Set con = CreateObject("ADODB.Connection")    
   ' con.Open "provider=microsoft.jet.oledb.4.0;extended properties=excel 8.0;data source=" & ThisWorkbook.Path & "/Database/exceldata.xls"
   
	 ' 数据连接
	 ' con.Open "Provider=Microsoft.ace.Oledb.12.0;" _
	    & "Extended Properties=Excel 12.0;" _
	    & "Data Source=" & ThisWorkbook.FullName
    
      'Conn.Open:打开数据库的连接
      'provider=microsoft.jet.oledb.4.0 数据库引擎版本，该引擎主要用于excel2003；Provider=Microsoft.ace.Oledb.12.0;用于excel2007及以上版本
      'extended properties=excel 8.0 连接的是Excel8.0版本(excel2000以后的版本),Excel不是标准的数据库格式,所以要设置扩展属性
      'data source=" & ThisWorkbook.Path & "/数据库.xls" 数据库路径
      
   '************以下是连接其他数据库或文件的字符串表达式*********************************
        '1 Mysql数据库
            'strDriver = "Provider=SQLOLEDB;DataSource=" & Path & ";Initial Catolog=" & strDataName
        '2 TXT文件
            'strDriver = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='text;IMEX=1;HDR=NO;FMT=Delimited;';Data Source=" & Path
        '3 MSSQL数据库
            'strDriver = "Provider=MSDASQL;Driver={SQL Server};Server=" & Path & ";Database=" & strDataName
        '4 Oracle数据库
             'strDriver= "Provider=madaora;Data Source=MyOracleDB; User Id=UserID; Password=Password"

  '2 执行sql语句
    '.Execute SQL
    '增加新表格：.Execute "Create 表格名 字段和属性"
    '增加新记录：.Execute "Insert into 表名 (字段1, 字段2,... 字段n) VALUES(值1,值2,... 值n)"
    '删除记录：  .Execute "Delete from 表名 where 条件
    '修改旧记录：.Execute "Update 表名称 SET 列1 = 新值,列2=新值 WHERE 列名称 = 某值
    '筛选记录:   .Execute "Select 字段 from 表 where 条件
           
'二、Recordset对象
  '作用 打开记录集操作记录
    '1 打开游标(记录集)
       'rst.Open sql或command语句等,已打开的conn链接,
       ' Set rst = CreateObject("ADODB.Recordset")
    '2 添加新记录
       'AddNew 单个字段或数组,单个值或数组
       
       '或
       
'       rst.AddNew '添加新的记录
'       rst.Fields("姓名") = "伍天明" 'Fields("字段名")表示某列的记录
'       rst.Fields("年龄") = 28
'       rst.Fields("性别") = "男"
'       rst.Update '添加记录后要更新
    '3 修改记录
       'rst.Update 字段数组, 值或数组
    '4 删除记录
       'rst.delete
    '5 在记录中循环
       'BOF 在记录的最前面
       'EOF 在记录的结尾
       'GetRows(默认值-1,Start, 字段)'Start 0从当前记录开始,1从第一条记录,2从最后一条记录开始
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58

一般操作步骤

if con.State=ADODB.ObjectStateEnum.adStateOpen then "连接成功" 可用来判断数据库链接是否成功

ActiveSheet.Range("A2").CopyFromRecordset rst 可以把返回的数据集写到活动工作表里

rst.Open query_sql, con, 1, 1执行sql查询

rst.recordcount是查询结果记录行数，可以用来判断SQL是否有返回记录行；

写入表头

With ActiveSheet
    cols = rs.Fields.Count
    For i = 0 To cols - 1
        .Cells(1, i + 1).Value = rs.Fields(i).Name  ' 写入表头
    Next
    .Cells(2, 1).CopyFromRecordset rs    ' 数据写入
End With
1
2
3
4
5
6
7

用来判断rst是否成功返回
if rst.State=ADODB.ObjectStateEnum.adStateOpen then msgbox "rst.open 成功返回"

关于数据库引擎与连接串

' provider=microsoft.jet.oledb.4.0 数据库引擎版本，该引擎主要用于excel2003，wps使用该引擎；
' Provider=Microsoft.ace.Oledb.12.0;用于excel2007及以上版本
1
2

关闭链接对象和记录集

Set con = Nothing
Set rs = Nothing

本地查询sql样例：

select t2.group,sum(t1.销售额) as sales 
from [Sheet1$] as t1 
inner join [分组$c4:d7] as t2 
on t1.姓名=t2.姓名 
where date_field<#2023/4/24# 
group by t2.group
1
2
3
4
5
6

如果是读取文本文件，诸如txt，csv，链接串后面的路径只需填写txt文本所在目录；，比如：F:\桌面\；同一个目录下的不同文本文件可以关联查询；

strDriver = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=""text;IMEX=1;HDR=Yes;FMT=Delimited(,)"";Data Source=F:\桌面\" 
1

其中：HDR=Yes表示文件包含表头；HDR=No表示文件不包含表头

文本分隔符不生效的问题

解决方法：在文本文件目录下配置schema.ini文件（同一目录）；

参考来源：https://www.cnblogs.com/zyizyizyi/archive/2011/10/10/2497832.html

这里可以使用文本框控件让用户填写分隔符，然后使用代码在文本目录下生成该配置文件，再创建链接；

如未配置，如果是访问txt文本，delimited指定其他分隔符，不生效的，还是读成了英文逗号；

Schema.ini配置格式大概格式如下：把注释和不必要的属性删了，第一行未文件名，用中括号括起来；

[Contacts.txt] ///需要导入的文本文件名
ColNameHeader=True ///是否有数据头
Format=FixedLength ///字段固定长度
MaxScanRows=0 ///最多导入行
CharacterSet=OEM ///字符集
Col1="First Name" Char Width 10 ///第一列格式
Col2="Last Name" Char Width 9 ///第二列格式
Col3="HireDate" Date Width 8 ///第三列格式
1
2
3
4
5
6
7
8

Schema.ini用于提供文本数据中的记录规格信息。每个Schema.ini的条目用于指明表的5个特征之一：

文本文件名
文件名有方括号括起来，例如如果要对Sample.txt使用Schema，那么它的对应的Schema条目应该是[Sample.txt]

文件格式指令如下：
Format=Value，Value可以取下面的值之一：
TabDelimited 用Tab分隔
CSVDelimited 用逗号分隔
FixedLength 固定长度
Delimited(C) 指定字符，其中C可以为除了双引号(")外的任何字符，也可以为空

字段名、字段宽度和类型
格式为：Coln=字段名 数据类型 [width 宽度]
字段名可以是任意字符，如果字段名包含空格，请使用双引号括起来。

数据类型可以为：Bit,Byte,Short(Integer),Long,Currency,Single,Double(Float),DateTime(Date DateFormat),Text(Char),Memo(LongChar)
其中DateFormat是日期的格式字符串例如：Date YYYY-MM-DD

字符集
格式：CharacterSet=ANSI | OEM
格式只有两种：ANSI和OEM

竖杠分隔测试：

Sub sql_query()
Dim con, rs As Object
Dim query_sql, str As String
Dim i, cols As Long

' 创建对象
Set con = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")


str = "Provider=Microsoft.ace.Oledb.12.0;Extended Properties=""text;HDR=Yes;FMT=Delimited(|)"";Data Source=C:\Users\yinrong\Desktop\"

    
' 数据连接
con.Open str

query_sql = "select * from [test.txt] where age>20"
' 执行sql语句
rs.Open query_sql, con, 1, 1

' 数据写入
Worksheets.Add    ' 新建工作表
With ActiveSheet
    cols = rs.Fields.Count
    For i = 0 To cols - 1
        .Cells(1, i + 1).Value = rs.Fields(i).Name  ' 写入表头
    Next
    .Cells(2, 1).CopyFromRecordset rs    ' 数据写入
End With

rs.Close
con.Close
Set con = Nothing

End Sub
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

schema.ini文件配置如下：不必要的属性配置删除（我这里抛出了异常）

[test.txt]
Format=Delimited(|)
CharacterSet=OEM
1
2
3

配置文件与文本在同一目录下

如果是配置多个文件的schema，直接在该文件标识即可；比如test.txt文件是以|分隔的；test1.txt以-分隔；配置文件如下：

[test.txt]
Format=Delimited(|)
CharacterSet=OEM

[test1.txt]
Format=Delimited(-)
CharacterSet=OEM
1
2
3
4
5
6
7

写sql时，txt文件名即表名，sql样例如下（文本文件名为：test.txt）：
select * from [test.txt] where age>20

窗体控件相关

公司闲暇时间做了一版，大概样式（文件没法外发），执行结果分页，使用列表控件展示结果：

在插件自定义功能区设置按钮，点击按钮加载该窗体；

UserForm1.show(0)是非模态显示窗体，在展示窗体后可以同时操作工作簿；

UserForm1.show(1)显示窗体后，无法操作工作簿；

分页控件通过设置value属性，展示对应对应页面；UserForm1.MultiPage1.Value = 0表示显示第一个page页面；

其他就是一些控件属性设置，事件动作，异常逻辑等，整体上比较简单；

SQL相关函数

聚合函数基本同其他数据库一致，其他函数有些区别，有些SQL函数可能不适用；